Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 01 Apr 2008 @ 20:21:46 GMT

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

Subj:   Re: Looping SQL Script in Queryman
From:   Michael Larkins


It doesn't make a lot of sense to do this is a loop when you could (as Nike says, "Just do it."). Will this not do the same thing?

     > > insert into closedTable (usedToBeUPI, myNUPI, locationCode,
     > >            maxOfMyDates, minOfMyDates, maxofTransCode, minOfTransCode)
     > > select baseTable.myUPI
     > > , baseTable.goingToBeNUPI
     > > , baseTable.locationCode
     > > , max(myDate)
     > > , min(myDate)
     > > , max(transCode)
     > > , min(transCode)
     > > from baseTable
     > >       where myUPI not in (select usedToBeUPI from closedTable)
     > >           and myUPI(char(8)) in ('080129','080130')
     > >       group by 1,2,3
     > >       having max(transCode) >= 'TRN4'
     > >         and min(transCode) = 'TRN1'

Without the derived table, there is no need for the join (to itself). Just do the aggregates (min,max) directly and it will be faster. I am thinking that your > tmpdate does something like the IN. However, I would have thought that '08013000001' would have been greater than '080129' as well as '080130'. But I am not sure what your looping comparison does.

This is just a guess, but hopefully it gets you on a workable path to what you are trying to accomplish. There is no looping in normal SQL. The only looping is in a stored procedure or BTEQ (normally requires reading data from a file to control different values being incorporated into the SQL).

Hope this helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

  <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: 28 Jun 2020