topic Script - Use results of first SQL in subsequent SQL in QlikView

Script - Use results of first SQL in subsequent SQL

reddwarfcrew — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Script - Use results of first SQL in subsequent SQL

pradosh_thakur — Mon, 16 Oct 2017 20:39:21 GMT

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

Re: Script - Use results of first SQL in subsequent SQL

maxgro — Mon, 16 Oct 2017 21:00:58 GMT

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