Archives of the TeradataForum

Message Posted: Tue, 01 Apr 2008 @ 18:18:28 GMT

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

<-- Anonymously Posted: Tue, 1 Apr 2008 10:52 -->

Hey everybody -

Thanks for all your help in the past - I'm hoping someone can help me with the syntax (if what I'm trying to do is possible) of how one would do the following?

I know how to do this with cursors (though I'm fuzzy on the syntax) in other databases, but not sure about Teradata.

(I've greatly simplified the logic to cut to the chase)

Hopefully this makes sense -

     create volatile table baseTable (
         myUPI char(12) ,
         goingToBeNUPI decimal(11,0) ,
         locationCode char(2) ,
         myDate date format 'YYYY-MM-DD' ,
         transCode char(4)
     )
     UNIQUE PRIMARY INDEX ( myUPI )
     ON COMMIT PRESERVE ROWS;

     insert into baseTable values('080129000001', 1, 'AA', '2008-01-28', 'TRN1');
     insert into baseTable values('080129000002', 1, 'AA', '2008-01-29', 'TRN4');
     insert into baseTable values('080129000003', 1, 'AA', '2008-01-27', 'TRN4');
     insert into baseTable values('080129000004', 1, 'AA', '2008-01-27', 'TRN4');
     insert into baseTable values('080129000005', 1, 'AA', '2008-01-27', 'TRN4');
     insert into baseTable values('080129000006', 2, 'AA', '2008-01-28', 'TRN4');
     insert into baseTable values('080129000007', 2, 'AA', '2008-01-28', 'TRN4');
     insert into baseTable values('080129000008', 2, 'AA', '2008-01-29', 'TRN4');
     insert into baseTable values('080129000009', 2, 'AA', '2008-01-27', 'TRN4');
     insert into baseTable values('080129000010', 2, 'AA', '2008-01-27', 'TRN4');
     insert into baseTable values('080129000011', 2, 'AA', '2008-01-27', 'TRN4');
     insert into baseTable values('080130000012', 2, 'AA', '2008-01-27', 'TRN1');

     create volatile table closedTable (
         usedToBeUPI char(12) ,
         myNUPI decimal(11,0) ,
         locationCode char(2) ,
         maxOfMyDates date format 'YYYY-MM-DD' ,
         minOfMyDates date format 'YYYY-MM-DD' ,
         maxOfTransCode char(4) ,
         minOfTransCode char(4)
     )
     PRIMARY INDEX ( myNUPI )
     ON COMMIT PRESERVE ROWS;

     set loopOverThisSet = '080129','080130'

     set tmpDate = First(loopOverThisSet)

     do while tmpDate is not null

            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 > #tmpDate#
                  group by 1,2
                  having max(transCode) >= 'TRN4'
                    and min(transCode) = 'TRN1'
            ) tmpBaseAgg
            on baseTable.goingToBeNUPI  = tmpBaseAgg.myNUPI
            and baseTable.locationCode = tmpBaseAgg.locationCode;

            set tmpDate = Next(loopOverThisSet)

     loop

Thanks in advance!

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