Krzysztof Krzysztof - 4 days ago 6
SQL Question

"First order by" in Teradata

I have a problem converting SQL statement from Oracle to Teradata. In Oracle statement is that:

SELECT ar.account_no,
MAX (ah.bal_acct) KEEP (DENSE_RANK FIRST ORDER BY ah.created_t desc)
FROM ar
JOIN ah ON ah.obj_id0 = ar.poid_Id0
JOIN acc ON a.poid_id0 = ar.account_obj_Id0
WHERE acc.account_no = '1234'
AND ah.created_t <= 1434753495
GROUP BY ar.account_no


I need to do similar statement in Teradata. I tried something with

QUALIFY ROW_NUMBER() OVER( PARTITION BY max(ah.bal_acct) ORDER BY ah.created_t desc) = 1


But all the time I have error: Selected non-aggregate values must be part of the associated group.

This is what I got:

Select ar.account_no, ah.created_t, ah.bal_acct
FROM VD_REPLICA_BRM.pi_tp_acct_ar_t ar
JOIN VD_REPLICA_BRM.pi_tp_acct_ar_hist_T ah ON ah.obj_id0 = ar.poid_Id0
JOIN VD_REPLICA_BRM.pi_account_t acc ON acc.poid_id0 = ar.account_obj_Id0
WHERE acc.account_no = '00003095660515'
AND ah.created_t <= CAST('2016-10-31' AS DATE FORMAT 'YYYY-MM-DD')
QUALIFY ROW_NUMBER() OVER( PARTITION BY max(ah.bal_acct) ORDER BY ah.created_t desc) = 1
GROUP BY ar.account_no


Where do I do mistake?

Answer

I'm not sure if you can do this with qualify. An equivalent statement is:

SELECT ar.account_no, ah.created_t, ah.bal_acct
FROM (SELECT ar.account_no, ah.created_t, ah.bal_acct,
             ROW_NUMBER() OVER (PARTITION BY ar.account_no ORDER BY ah.created_t DESC) as seqnum
      FROM ar JOIN
           ah
           ON ah.obj_id0 = ar.poid_Id0 JOIN
           acc
           ON a.poid_id0 = ar.account_obj_Id0
      WHERE acc.account_no = '1234' AND ah.created_t <= 1434753495
     ) t
WHERE seqnum = 1;

Duh. You can do this with QUALIFY. The issue is the GROUP BY:

SELECT ar.account_no, ah.created_t, ah.bal_acct
FROM VD_REPLICA_BRM.pi_tp_acct_ar_t ar JOIN
     VD_REPLICA_BRM.pi_tp_acct_ar_hist_T ah
     ON ah.obj_id0 = ar.poid_Id0 JOIN
     VD_REPLICA_BRM.pi_account_t acc
     ON acc.poid_id0 = ar.account_obj_Id0
WHERE acc.account_no = '00003095660515' AND
      ah.created_t <= CAST('2016-10-31' AS DATE FORMAT 'YYYY-MM-DD')
QUALIFY ROW_NUMBER() OVER( PARTITION BY ar_account_no ORDER BY ah.created_t desc) = 1
Comments