×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Iterative querying - is this possible??

Iterative querying - is this possible??

Iterative querying - is this possible??

(OP)
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?!

RE: Iterative querying - is this possible??

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 WWW.INFO.NCR.COM

RE: Iterative querying - is this possible??

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close