Archives of the TeradataForum
Message Posted: Tue, 01 Apr 2008 @ 20:21:46 GMT
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,
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|