Script - Use results of first SQL in subsequent SQL

[reddwarfcrew]

Hi, my script has about 10 SQL statements.  All are very similar pulling different attributes from an oracle db.

Each SQL state has a where clause along the lines of:

WHERE fieldname IN (SELECT FIELD FROM TABLE WHERE STATUS = 1)

Now the embedded Select statement in each where clause is of course quite ineffective.  Can I run that SQL statement first and then use the results in the subsequent SQL statements in order to speed up the overall load?

Thanks

[pradosh_thakur]

try this may be it will work

1:SELECT FIELD FROM TABLE WHERE STATUS = 1

2: use peek and concat to store all the value of field in a variable and make sure they are separated by comma(,)

3:use variable in plave of SELECT FIELD FROM TABLE WHERE STATUS = 1in the where clause

[maxgro]

there is a limit, 1000 if I remember, for the number of elements in a IN Oracle clause, so

this will always works

WHERE fieldname IN (SELECT FIELD FROM TABLE WHERE STATUS = 1)

this

WHERE fieldname IN (1, 2, .......n)

will work for n <= 1000