Archives of the TeradataForum

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

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

Anomy.Anom

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>>