Multiple SQL in "Load SQL from file at run time"

Maximus_Jack · Post by **Maximus_Jack** » Tue Feb 26, 2013 10:58 am

Hi
I did some googling and searched this forum as well, but couldn't find it.

I'm trying to execute multiple sql's in oracle stage using the "Load SQL from file at run time"
option, seperating each sql with a ";" , but datastage is taking all the sql as a
single SQL and trying to run and its getting aborted, is there
any way i can use multiple sql in the "Load SQL from file at run time" option.

cheers
MJ

chulett · Post by **chulett** » Tue Feb 26, 2013 12:17 pm

I don't recall if that is an option when using a SQL file (always used it for a single statement) and I don't have any way to check right now. Only thing I can think of right now is to try two semi-colons as a separator: ";;".

Maximus_Jack · Post by **Maximus_Jack** » Tue Feb 26, 2013 12:22 pm

hi craig, thanks for responding, tried that too.. its not working..

chulett · Post by **chulett** » Tue Feb 26, 2013 3:53 pm

Then it's not supported it would seem. Why not just add a UNION ALL between them so they become a single Sql statement?

chandra.shekhar@tcs.com · Wed Feb 27, 2013 12:20 am

Jack,
If you use multiple queries then it will throw an error for invalid character ";".
What sort of sqls you are using, why dont you use Before/After SQL or try union as suggested by Craig.
I had the same problem so I used Before SQL.

chulett · Post by **chulett** » Wed Feb 27, 2013 7:32 am

These must be source SQLs, as in ones that provide data to the job so I'm assuming they are not appropriate for using before/after. If you don't want to go the 'union' route then perhaps the job can be 'looped' via a Sequence job that replaces the sql file each iteration.

Maximus_Jack · Post by **Maximus_Jack** » Wed Feb 27, 2013 9:46 am

Hi thanks for responding..
the queries are dynamically generated... and i want to use those data in the job.

I think by somehow i have to generate the columns of the sql queris as a single concatenated columns and separate them with commas andand as craig said i should then union them all, before passing it to the sequential files

sudha03_vpr · Post by **sudha03_vpr** » Wed Feb 27, 2013 1:01 pm

Craig's idea looks to be simplest. You can also read the sql into the parameter and loop it so each sql reads the data and loads it to the file. This approach may be good if you wanted the sqls to be loaded to the seperate file instead of same file.