RE: SQL exception and output

John,

Insert 1000 records into a file with an auto increment field starting at 2000. Then run a DELETE FROM generatedfile WHERE EXISTS( Select * from controlfile where generatednumber=controlfilenumber). If your controlfilenumber or generatednumber need to be text strings for the comparison you can convert them in the SQL statement.

Paul

Principal Programmer Analyst
IT Supply Chain/Replenishment

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of jmmckee
Sent: Tuesday, January 25, 2011 2:04 PM
To: Midrange Systems Technical Discussion
Subject: SQL exception and output

I >think< the need for this has passed, so I am just wanting to know if there is a more efficient way to do this.

I needed to create a list of numbers. The range was from 2000 - 2999. Each number generated had to be checked against another file to ensure it had not already been used.

What I did: Ran a query over an account file, to get the initial numbers. Converted the number to a string, extracted four characters and added six blanks. Grouped the result and wrote to a file. Then, a second query joined this file to the control file where the join field was also 10 characters. This was a query where data was not in primary file. Wrote the results to a file.

Questions:

1) What would an SQL statement look like to accomplish the same thing?

2) How would I get the results into a file? I have read this somewhere, before, but don't know how to search effectively for it in the archives.

Again, the need may be no more. I just want to do this in a more direct manner in the future.


John McKee