I am new to Teradata and I have a requirement to fetch TOP transactions(ROWS) based on a SALES DATETIME provided to the query.
I am able to retreive all the Transactions(ROWS) that occured after the SALES DATETIME,nevertheless my requirement is to fetch TOP 5 ROWS based on DATETIME.I am using below query to achieve it and i am not sure what is driving the problem here.I can't use TOP funtion as i will be combining similar multiple result sets using UNION so thought to restrict either by ROWNUM or RANK.Can anyone please help me to achive this?
p.product_id,d.param_seq_ID,d.parameter_name ,d.parameter_uom,f.parameter_value ,f.sales_datetime ,Store_phase
FROM SFD_MFG_V.SLS_D_PLOT P
INNER JOIN SFD_MFG_V.RDM_F_PARAMETER F ON (P.DIM_SEQ_ID = F.DIM_SEQ_ID) INNER JOIN SFD_MFG_V.SLS_D_PARAM D ON (F.PARAM_SEQ_ID = D.PARAM_SEQ_ID) INNER JOIN SFD_MFG_V.SLS_PLANT_INSTALLATION PI ON (PI.FLEET = P.FLEET
AND PI.PRODUCT_ID = P.PRODUCT_ID AND PI.AIRCRAFT_ID = P.AIRCRAFT_ID AND PI.INSTALL_DATETIME = P.INSTALL_DATETIME A ND PI.PRODUCT_CATEGORY = P.PRODUCT_CATEGORY)
P.PRODUCT_ID = '123456' AND D.PARAMETER_NAME = 'AMERICA'
AND P.STORE_PHASE = 'OPERATIONAL' AND F.SALES_DATETIME>= CAST( '2011-01-01 00:00:01' AS TIMESTAMP(0) )
QUALIFY ROW_NUM() OVER (PARTITION BY
p.product_id ,d.param_seq_ID,d.parameter_name, d.parameter_uom ,f.parameter_value ,f.sales_datetime ,Store_phase
ORDER BY F.SALES_DATETIME ASC)<=5
So, what's your actual problem?
ROW_NUM is no valid keyword, it's ROW_NUMBER.
You have to many columns in your PARTITION, unless you need the TOP 5 per whatever you should remove the PARTITION clause.