QUALIFY <cond_1> AND <cond_2> filtered out valid data

Database
Teradata Employee

QUALIFY <cond_1> AND <cond_2> filtered out valid data

Hi

I have the below resultset from a SQL, to which i add the QUALIFY (given below data)

PRTY_ID PRTY_AGMT_RLE_CD    FULL_NM    SYS_ID    AD_VER_DT    OWNR_INSD_ORDER    PRTY_ID    IND
7286 OWNR ABC 12345 8/8/2011 1 168,098 Y
7286 OWNR ABC 12345 8/8/2011 1 168,098 Y
7286 INSD ABC 12345 8/8/2011 2 168,098 Y
7286 INSD ABC 12345 8/8/2011 2 168,098 Y
1157 OWNR XYZ 67890 12/31/2011 1 49,762 Y
1157 OWNR XYZ 67890 12/31/2011 1 49,762 Y

QUALIFY MIN (OWNR_INSD_ORDER) OVER (PARTITION BY SYS_ID ORDER BY OWNR_INSD_ORDER) = OWNR_INSD_ORDER


This gives me the below, which i expect

PRTY_ID PRTY_AGMT_RLE_CD  FULL_NM    SYS_ID    AD_VER_DT    OWNR_INSD_ORDER    PRTY_ID    CAS_IND
7286 OWNR ABC 12345 8/8/2011 1 168,098 Y
7286 OWNR ABC 12345 8/8/2011 1 168,098 Y
1157 OWNR XYZ 67890 12/31/2011 1 49,762 Y
1157 OWNR XYZ 67890 12/31/2011 1 49,762 Y


However when i add a condtion to the Qualify like below

      QUALIFY MIN (OWNR_INSD_ORDER) OVER (PARTITION BY SYS_ID ORDER BY OWNR_INSD_ORDER) = OWNR_INSD_ORDER

      AND ROW_NUMBER() OVER (PARTITION BY SYS_ID ORDER BY AD_VER_DT DESC ) = 1

I expected to see

PRTY_ID    PRTY_AGMT_RLE_CD    FULL_NM    SYS_ID    AD_VER_DT    OWNR_INSD_ORDER    PRTY_ID    CAS_IND
7286 OWNR ABC 12345 8/8/2011 1 168,098 Y
1157 OWNR XYZ 67890 12/31/2011 1 49,762 Y


but only get 

PRTY_ID    PRTY_AGMT_RLE_CD    FULL_NM    SYS_ID    AD_VER_DT    OWNR_INSD_ORDER    PRTY_ID    CAS_IND
1157 OWNR XYZ 67890 12/31/2011 1 49,762 Y

Though its not in the example above, the AD_VER_DT could be diff and i do need Qualify to pick the latest

thanks

deepak

4 REPLIES
Teradata Employee

Re: QUALIFY <cond_1> AND <cond_2> filtered out valid data

Tried reversing the order of the conditions in QUALIFY and it gave back the expected result, just dont understand it though

- deepak

Junior Contributor

Re: QUALIFY <cond_1> AND <cond_2> filtered out valid data

Hi Deepak,

move both QUALIFY functions to the SELECT and you can see what's wrong :-)

Without a unique sort column ROW_NUMBER is not deterministic.

It seems like you only need a single ROW_NUMBER, 

QUALIFY 
ROW_NUMBER()
OVER (PARTITION BY SYS_ID
ORDER BY OWNR_INSD_ORDER, AD_VER_DT DESC ) = 1
Teradata Employee

Re: QUALIFY <cond_1> AND <cond_2> filtered out valid data


Thanks dieter, yes it does make sense and i did notice that ROW_NUMBER() would sometimes assign 1 to the record that would be filtered out with the 2nd condition in the QUALIFY. 

However with the order of the conditions changed would teradata consistently apply the ROW_NUMBER function after resolving the MIN() OVER () condition?

thanks again

deepak

Teradata Employee

Re: QUALIFY <cond_1> AND <cond_2> filtered out valid data

Tried multiple times and understand that the ROW_NUMBER() cannot gaurantee the assignment have used the  below construct which will work always

SEL ...

( SEL ... QUALIFY MIN ()...) AS X

QUALIFY ROW_NUMBER()....

this thread may be closed - thanks :)