Query with Group by and Qualify receiving error 3504

Analytics
New Member

Query with Group by and Qualify receiving error 3504

I'm a beginner SQL user and trying to figure out why the below query using group by and qualify statements is not working. 

 

SyntaxEditor Code Snippet

SELECT  b.RSN_CD 
    , b.oms_dcsn_hold_ind     , b.trans_held_by_pp_yn_ind     ,COUNT( DISTINCT a.transaction_id || a.item_id)  
     ,SUM (a.ITEM_PRICE  * a.quantity  * CAST(a.LSTG_CuRNCY_EXCHNG_RATE AS DECIMAL(18,2)))      ,SUM(COALESCE(d.NET_LOSS_USD_AMT,0)  - COALESCE(e.RVI_PAID_USD_AMT,0)) 

FROM checkout_trans a

INNER  JOIN INC_FACT b
 ON  b.trans_id = a.transaction_id
 AND a.item_id = b.item_id
 
LEFT JOIN rsltn_cp d ON a.transaction_id=d.tran_id AND  a.item_id= d.item_id 
LEFT JOIN I_Rsltn e ON e.tran_ID=d.tran_id AND e.item_id= d.item_id 

WHERE (cast (a.created_dt AS DATE) BETWEEN  '2016-01-01' AND '2017-03-31')AND a.trans_site_id IN (0,1,3,15,71,101,186)AND a.sale_type NOT IN (-999,-888,12,13,14,15)AND a.CHECKOUT_STATUS IN (1,2)

Group By 1,2,3

QUALIFY ROW_NUMBER() OVER (PARTITION BY a.item_id, a.transaction_id ORDER BY d.RCNT_CPS_OPEN_DT DESC  )=1

 

1 REPLY
Highlighted
Teradata Employee

Re: Query with Group by and Qualify receiving error 3504

I hope someone else can think of an easier way, but you might have to move the Qualify into a derived table, such as:

 

SELECT RSN_CD
    ,oms_dcsn_hold_ind
    ,trans_held_by_pp_yn_ind
    ,COUNT( DISTINCT transaction_id || item_id)
    ,SUM (ITEM_PRICE * quantity * EXCHNG_RATE)
    ,SUM(NET_LOSS_AMT - RVI_PAID_AMT)
FROM (
    SELECT b.RSN_CD
        ,b.oms_dcsn_hold_ind
        ,b.trans_held_by_pp_yn_ind
        ,a.transaction_id
        ,a.item_id
        ,a.ITEM_PRICE
        ,a.quantity
        ,CAST(a.LSTG_CuRNCY_EXCHNG_RATE AS DECIMAL(18 ,2)) EXCHNG_RATE
        ,COALESCE(d.NET_LOSS_USD_AMT ,0) net_loss_amt
        ,COALESCE(e.RVI_PAID_USD_AMT ,0) rvi_paid_amt
    FROM checkout_trans a
    INNER JOIN INC_FACT b
     ON   b.trans_id = a.transaction_id
     AND  a.item_id = b.item_id LEFT
    JOIN rsltn_cp d
     ON   a.transaction_id=d.tran_id
     AND  a.item_id= d.item_id LEFT
    JOIN I_Rsltn e
     ON   e.tran_ID=d.tran_id
     AND  e.item_id= d.item_id
    WHERE (cast (a.created_dt AS DATE) BETWEEN '2016-01-01'
     AND '2017-03-31')AND a.trans_site_id IN (0 ,1 ,3 ,15 ,71 ,101 ,186)
     AND a.sale_type NOT IN (-999 ,-888 ,12 ,13 ,14 ,15)
     AND a.CHECKOUT_STATUS IN (1 ,2)
    QUALIFY ROW_NUMBER() OVER (PARTITION BY a.item_id ,a.transaction_id
                    ORDER BY d.RCNT_CPS_OPEN_DT DESC )=1
) DT
Group By 1 ,2 ,3