Archives of the TeradataForum

Message Posted: Thu, 07 Jul 2005 @ 21:07:58 GMT

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

All,

I've been doing Teradata SQL for some time now, but still haven't found an easy way to accomplish one particular task that I have to perform for a customer of mine. As such, I thought I throw it out to see if anyone else could come up with a solution.

In a set of queries that I have to run on a quarterly basis for a marketing initiative, my customer wants to pull address data for say 500,000 members from a population of several million. The first group to pull is determined by several criteria statements that will return an unknown number of records (before I run), and then I'm asked to 'back-fill' the data with a random sample of the remaining available records until I hit the 500,000 targeted records.

What I've done in the past is to run the query containing the specific criteria, followed immediately by a Count request to determine the number of records pulled, and then perform a .Quit; within my module. I would then have to manually set the amount in the Sample clause of a subsequent query to perform the back-filling, and restart the module at a label just below the .Quit statement to get the remaining records I need.

Does anyone have any suggestions (or somewhere you could point me) as to how this process can be coded and accomplished auto-magically so that I can reduce the amount of manual intervention that=92s required to fulfill this request? Any assistance would be greatly appreciated.

Thanks,

John

Sample query:

     Insert into dbs1.tbl1
     Select col1,col2, col3
       From dbs1.tbl2
     Where col3 = 'X'
     ;
     Select count(*)
       From dbs1.tbl1
     ; - results in 200,000 records
     .Quit.
     .Label restart;
     Insert into dbs1.tbl1
     Select col1, col2, col3
       From dbs1.tbl2
     Where not col1 in
       (select col1 from dbs1.tbl1)
     Sample 300,000  =E7 manually entered value after first part of query runs.
     ;

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