Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: Looping SQL Script in Queryman
From:   Hohman, Bruce E

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 Hohman
Teradata Corporation
Certified Teradata Master V2R5

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019