Iterative querying - is this possible??

[Evey]

I'm trying to run an update query a multiple number of times.

What I want to do is loop through the SQL x times. I know this is possible in other SQL packages. Does anyone know whether I can do this with Teradata and if so how?

My colleagues think I will just have to repeat the SQL multiple times in my script. Surely there's a better way than that?! [sig][/sig]

 

[TeraMarv]

If you are running your SQL under the BTEQ application on the client (be it MVS,Unix or whatever) you should be able to repeat the SQL script using the '.REPEAT n' command where 'n' is the number of times you wish to repeat (place this before the SQL statement). Alternatively you can end your SQL with the '= n' command which is similar except it will run your script n+1 times as it repeats any SQL immediately above it (i.e. it has run the SQL once before the command is executed).

Check out the Teradata BTEQ Reference Manual at

http://WWW.INFO.NCR.COM

 

[Guest_imported]

This is possible, but the means of execution is quite rudimentary. What it does give though is a means of repeating not just one command, but entire scripts, indefinitely. The method used involves having a script re-execute itself. You need to take care of making the script stop when needed yourself. See below.

At the end of your BTEQ Update script, use the '.RUN FILE=' or '.RUN DDNAME=' command to point to another script to execute (in your case, this would look similar to the first, including the '.RUN FILE=' at the end). In MVS, the JCL would look something like this:

//*
//BTEQ EXEC PGM=BTQMAIN
//SYSIN DD DSN=XXX.YYY.ZZZ(LOGIN),DISP=SHR
// DD DSN=XXX.YYY.ZZZ(FIRSTSQL),DISP=SHR
//DOAGAIN DD DSN=XXX.YYY.ZZZ(REPEATIT),DISP=SHR
//*

FIRSTSQL might look like this:

/* start of FIRSTSQL */

Update SomeTable
Set ColumnX = OtherTable.ColumnY
Where (some conditions)
...
...
/* Run XXX.YYY.ZZZ(REPEATIT) */
.RUN DDNAME=DOAGAIN

/* end of FIRSTSQL */

REPEATIT might look like this:

/* start of REPEATIT */

/* Perform a test here to see if you */
/* need to continue this iteration. */
/* .QUIT if you're all finished, */
/* otherwise... */

Update SomeTable
Set ColumnX = OtherTable.ColumnY
Where (some conditions)
...
...
/* Re-run myself */
.RUN DDNAME=DOAGAIN

/* end of REPEATIT */

I have used this technique successfully in the past. I don't like it, but it works. Hope it helps. Stored Procedures (V2R4) will help get around these problems in the future.