Archives of the TeradataForum

Message Posted: Tue, 01 Apr 2008 @ 20:23:55 GMT

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

You don't need a cursor. You can do it with 'set processing' by doing (you were almost there):

     insert into closedTable (usedToBeUPI, myNUPI, locationCode,maxOfMyDates,
     minOfMyDates, maxofTransCode, minOfTransCode)
     select baseTable.myUPI
     , baseTable.goingToBeNUPI
     , baseTable.locationCode
     , tmpBaseAgg.maxOfMyDates
     , tmpBaseAgg.minOfMyDates
     , tmpBaseAgg.maxOfTransCode
     , tmpBaseAgg.minOfTransCode
     from baseTable inner join (
            select goingToBeNUPI as myNUPI
            , locationCode
            , max(transCode) as maxOfTransCode, min(transCode) as minOfTransCode
            , max(myDate) as maxOfMyDates, min(myDate) as minOfMyDates
            from baseTable
            where myUPI not in (select usedToBeUPI from closedTable)
                and myUPI between '080129' and '080130'    --  or you could use an IN
            group by 1,2
            having max(transCode) >= 'TRN4'
              and min(transCode) = 'TRN1'
     ) tmpBaseAgg
     on baseTable.goingToBeNUPI  = tmpBaseAgg.myNUPI
     and baseTable.locationCode = tmpBaseAgg.locationCode;

Bruce

Bruce Hohman
Teradata Corporation
Certified Teradata Master V2R5

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