AND Condition after Qualify

Database
Enthusiast

AND Condition after Qualify

Hi Experts,

I am using two queries which look similar only sequence of AND clause is different

First Query :- 

SEL ENT_CUST_ID, SOR_CUST_ID , ACCT_ID , SOR_ID, COALESCE (CURR_AMT , 0 ) AS CURR_AMT FROM UD466.GHP501_RAF_ACCT_CUST_082014 

QUALIFY ROW_NUMBER() OVER (PARTITION BY ENT_CUST_ID  ORDER  BY SOR_CUST_ID , COALESCE(CURR_AMT,0) DESC) > 1 

AND     EXCL_RSN IS NULL AND ENT_CUST_ID IS NOT NULL;

Second Query:-

SEL ENT_CUST_ID, SOR_CUST_ID , ACCT_ID , SOR_ID, COALESCE (CURR_AMT , 0 ) AS CURR_AMT FROM UD466.GHP501_RAF_ACCT_CUST_082014 

AND EXCL_RSN IS NULL AND ENT_CUST_ID IS NOT NULL ------Sequence Changed 

QUALIFY ROW_NUMBER() OVER (PARTITION BY ENT_CUST_ID  ORDER  BY SOR_CUST_ID , COALESCE(CURR_AMT,0) DESC) > 1 

;

Ideally both queries should not work but first query is working and that too while giving the resultset it ignores AND condition.

And even if I use Where clause in place of AND clause in the second query  RESULTS are completely different.

Can someone please explain me the difference?

Thanks

8 REPLIES
Enthusiast

Re: AND Condition after Qualify

Please help

Senior Apprentice

Re: AND Condition after Qualify

The 2nd query should result in a syntax error, but the 1st is syntactically valid.

Of course moving both conditions into WHERE will return a different result, in WHERE it's applied before, but in QUALIFY after calculating the ROW_NUMBER. This is similar to WHERE vs. HAVING.

Enthusiast

Re: AND Condition after Qualify

Thanks Dieter but in the first query ,why its accepting AND it should accept WHERE?

Senior Apprentice

Re: AND Condition after Qualify

Why not?

It's just an additional condition, just without OLAP function...

Enthusiast

Re: AND Condition after Qualify

Thanks for your response

Below both queries works but gives different results

1.

SEL ENT_CUST_ID, SOR_CUST_ID , ACCT_ID , SOR_ID, COALESCE (CURR_AMT , 0 ) AS CURR_AMT FROM GHP501_RAF_ACCT_CUST_082014

QUALIFY ROW_NUMBER() OVER (PARTITION BY ENT_CUST_ID  ORDER  BY SOR_CUST_ID , COALESCE(CURR_AMT,0) DESC) > 1 

WHERE    EXCL_RSN IS NULL AND ENT_CUST_ID IS NOT NULL

2. SEL ENT_CUST_ID, SOR_CUST_ID , ACCT_ID , SOR_ID, COALESCE (CURR_AMT , 0 ) AS CURR_AMT FROM GHP501_RAF_ACCT_CUST_082014 

QUALIFY ROW_NUMBER() OVER (PARTITION BY ENT_CUST_ID  ORDER  BY SOR_CUST_ID , COALESCE(CURR_AMT,0) DESC) > 1 

AND    EXCL_RSN IS NULL AND ENT_CUST_ID IS NOT NULL

So my question is why second query is working , it should give syntax error?

Enthusiast

Re: AND Condition after Qualify

And even if second one is working it should give the same result as first one

Enthusiast

Re: AND Condition after Qualify

Experts - Please help 

Enthusiast

Re: AND Condition after Qualify

In the first query the where clause gets applied after the spool has all the conditions that satisfy till the qulify.

in the second query during the build of the result set the NOT NULL condition would be applied and then qualify works along with that due to the AND.

Both queries may not result in the same due to the above mentioned placement. it is like the braces placement in a mathematics problem.