Archives of the TeradataForum

Message Posted: Mon, 14 Mar 2011 @ 16:07:52 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Ashutosh Roy wrote:

     > SELECT  distinct  max(irs_name) KEEP (DENSE_RANK LAST
     > ORDER BY YMDTRANS, extraction_date ) OVER (PARTITION BY  IRS_NBR,
     >   BUS_UNIT) AS IRS_NAME,  TRIM(IRS_NBR) AS IRS_NBR, BUS_UNIT AS
     > BUS_UNIT
     > from AMI_STAGE_OWN.IRS_M
     > ORDER BY IRS_NBR,BUS_UNIT,IRS_NAME

Oracle's FIRST/LAST is not implemented in Teradata, you have to rewrite it using nested OLAP functions:

     SELECT  distinct
        max(case when rnk_last=1 then irs_name end)
        over (PARTITION BY  IRS_NBR,BUS_UNIT) as IRS_NAME,
        TRIM(IRS_NBR) AS IRS_NBR,
        BUS_UNIT AS BUS_UNIT
     from
       (select IRS_NBR,BUS_UNIT,IRS_NAME,
          rank()
          over (PARTITION BY  IRS_NBR,BUS_UNIT
                order by YMDTRANS desc, extraction_date desc) as rnk_last
        from AMI_STAGE_OWN.IRS_M
       ) as dt
     ORDER BY IRS_NBR,BUS_UNIT

untested

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>