How to pass iterative values to the sql

[satheesh_color]

Hi All,

We are having issues for the below job flow. We created a dataset which contains the Store_id,Run_date and P_Id. This needs to be passed to the next sql statement in an iterative way.

i.e: we have around 40000 store_id,P_Id.

Eg:
SELECT 1,2,3,4 from Table WHERE Id in (<Store_id>, <P_ID>) AND P_B=1 AND P_T<= (SELECT P_T FROM P_T WHERE Defa= 'T') AND TRUNC(TO_DATE(<job_run_date>, 'mm/dd/yyyy')) BETWEEN P_DATE AND R_DATE AND values=<STOREID> AND P_No=0

Please let me know your thoughts on how to achieve this.


Thanks,
Satheesh

[chulett]

Not very clear on your question or requirement. Are you saying that you need to run your posted sql 40,000 times - once for each combination of ids on file? Can you not just join the two?

[ray.wurlod]

Using that SQL, every row you get back will contain the four numbers 1, 2, 3 and 4. Is that what you require?

[MT]

Hi All,

We are having issues for the below job flow. We created a dataset which contains the Store_id,Run_date and P_Id. This needs to be passed to the next sql statement in an iterative way.

i.e: we have around 40000 store_id,P_Id.

Eg:
SELECT 1,2,3,4 from Table WHERE Id in (<Store_id>, <P_ID>) AND P_B=1 AND P_T<= (SELECT P_T FROM P_T WHERE Defa= 'T') AND TRUNC(TO_DATE(<job_run_date>, 'mm/dd/yyyy')) BETWEEN P_DATE AND R_DATE AND values=<STOREID> AND P_No=0

Please let me know your thoughts on how to achieve this.


Thanks,
Satheesh

Hi Satheesh

well - if I got you right - and you want to pass some values out of a data stream (like a file) as parameters into a SQL statement there are options but not very easy ones....

What you could do (if licensed) you could call a WebService by using the WS transformer passing the data stream into it and receiving the results "out of" it. The WS is quite simply a DB-Stage - TX - RTI Out.
This works in server jobs very well but you need RTI or WISD licensed.
In Parallel this will impose some overhead.

An alternative could be the user variable - but htere is only one per job - so you will need some extra logic..

A third idea might be to pass it to a routine which issues the SQL for you - but this is not very nice in case you need 4 columns back.

So - just some ideas - sometimes it is better to think of a alternative architecture...

kind reagrds
Michael

[jcthornton]

From the way I read it, I think Craig has the best route.

There is a lot of obfuscation going on here, but depending on where all those values come from (are P_DATE and R_DATE parameters or column names?, etc.) it is just a matter of how you break up that statement.

From the database, do your select statement using as many conditions as possible to limit the number of records (and hopefully improve the index used to return data).

Perform a lookup/join with your dataset.

Any remaining conditions can be handled in a filter/transformer after the join.

If data volumes are an issue, a sparse lookup gets to be an option.

At least that's how I'd try to approach it. Go simple first, complicate it only at last resort.