Hi,
I am coding a controlling job, and I need to run an sql statement several times depending on the list of values returned in a previous sql call.
eg. Select Variable1
from TableA
where status = PASS
I should receive a list of values back from the above command, for each row returned I would like to run the same sql:
eg.
For each returned value:
Select Variable2
from TableB
where Variable3 = TableA.Variable1
And I want to keep the two as separate sqls!
Does anyone know have an example of how a prepared sql statement works, and whether it would be appropriate to use it in this example?
Thanks
Wendy Voon
Consultant
Black Diamond
T e c h n o l o g i e s
Level 1, 6 Riverside Quay,
Southbank, Victoria, 3006.
E-mail: wendy.voon@bdt.com.au
Telephone: (03) 9698 - 7600
Facsimile: (03) 9698 - 7666
Web: http://www.bdt.com.au/
-------INTERNET E-MAIL CONFIDENTIALITY/DISCLAIMER--------
Privileged and confidential information may be contained in this e-mail. If you are not the intended recipient of this communication please delete and destroy all copies and kindly notify the sender by return e-mail. Recipients of this e-mail must not use, disclose or forward any information or attachments without express permission from Black Diamond Technologies.
Any views expressed in this communication are those of the individual sender except where the sender specifically states them to be the views of Black Diamond Technologies. Except as required at law, we do not represent warrant and/or guarantee that the integrity of this communication has been maintained or that it is free of errors, viruses, interception or interference.
"Prepared SQL statements"
When you say "run an SQL statement several times depending on the list of values returned in a previous SQL call" do you mean that the SQL is being executed from your controlling jobs job control routine (via BCI functions), or in the job itself? This will affect the answer; if the first SQL statement is being run through BCI its result can be captured, a row at a time if necessary, into variables, and these can then be passed as parameters to other controlled jobs or new SQL statements can be built in code and executed also via BCI functions. On the other hand, if the first SQL statement is executed in controlled job A and the other statement in controlled job B, then your controlling job needs some way to recover the value(s) from job A. Probably the easiest and fastest method is to have job A dump them into a text file or hashed file, which the controlling jobs job control code can read, passing values as parameters to job B. At release 4.x you can only run one instance of job B at a time. At release 5.1 (with the Axcel pack) you can run multiple instances of job B in parallel.
-----Original Message-----
From: Voon, Wendy [mailto:wendy.voon@bdt.com.au]
Sent: Monday, 15 October 2001 14:56
To: datastage-users@oliver.com
Subject: "Prepared SQL statements"
Hi,
I am coding a controlling job, and I need to run an sql statement several times depending on the list of values returned in a previous sql call.
eg. Select Variable1
from TableA
where status = PASS
I should receive a list of values back from the above command, for each row returned I would like to run the same sql:
eg.
For each returned value:
Select Variable2
from TableB
where Variable3 = TableA.Variable1
And I want to keep the two as separate sqls!
Does anyone know have an example of how a prepared sql statement works, and whether it would be appropriate to use it in this example?
Thanks
Wendy Voon
Consultant
Black Diamond
T e c h n o l o g i e s
Level 1, 6 Riverside Quay,
Southbank, Victoria, 3006.
E-mail: wendy.voon@bdt.com.au
Telephone: (03) 9698 - 7600
Facsimile: (03) 9698 - 7666
Web: http://www.bdt.com.au/
-------INTERNET E-MAIL CONFIDENTIALITY/DISCLAIMER--------
Privileged and confidential information may be contained in this e-mail. If you are not the intended recipient of this communication please delete and destroy all copies and kindly notify the sender by return e-mail. Recipients of this e-mail must not use, disclose or forward any information or attachments without express permission from Black Diamond Technologies.
Any views expressed in this communication are those of the individual sender except where the sender specifically states them to be the views of Black Diamond Technologies. Except as required at law, we do not represent warrant and/or guarantee that the integrity of this communication has been maintained or that it is free of errors, viruses, interception or interference.
-----Original Message-----
From: Voon, Wendy [mailto:wendy.voon@bdt.com.au]
Sent: Monday, 15 October 2001 14:56
To: datastage-users@oliver.com
Subject: "Prepared SQL statements"
Hi,
I am coding a controlling job, and I need to run an sql statement several times depending on the list of values returned in a previous sql call.
eg. Select Variable1
from TableA
where status = PASS
I should receive a list of values back from the above command, for each row returned I would like to run the same sql:
eg.
For each returned value:
Select Variable2
from TableB
where Variable3 = TableA.Variable1
And I want to keep the two as separate sqls!
Does anyone know have an example of how a prepared sql statement works, and whether it would be appropriate to use it in this example?
Thanks
Wendy Voon
Consultant
Black Diamond
T e c h n o l o g i e s
Level 1, 6 Riverside Quay,
Southbank, Victoria, 3006.
E-mail: wendy.voon@bdt.com.au
Telephone: (03) 9698 - 7600
Facsimile: (03) 9698 - 7666
Web: http://www.bdt.com.au/
-------INTERNET E-MAIL CONFIDENTIALITY/DISCLAIMER--------
Privileged and confidential information may be contained in this e-mail. If you are not the intended recipient of this communication please delete and destroy all copies and kindly notify the sender by return e-mail. Recipients of this e-mail must not use, disclose or forward any information or attachments without express permission from Black Diamond Technologies.
Any views expressed in this communication are those of the individual sender except where the sender specifically states them to be the views of Black Diamond Technologies. Except as required at law, we do not represent warrant and/or guarantee that the integrity of this communication has been maintained or that it is free of errors, viruses, interception or interference.