SQL query character limit

[irshanuddin]

Hello gurus,
We have a sql from a view that loads a table that we would like to bring in to our Datastage environment to have more control over it. The SQL itself is over 97,000 characters. It appears the character limits for User Defined SQL, Before SQL, After SQL and parameter string are all 64,000. This leaves us with the option of calling the script from a file, which we are trying to avoid due to some mishaps in the past where files got erased from the server.
Are we left with any other options?
We would like to keep the query intact and not have to break it up since it has loops in it and just rewriting to break it up would be a big job.

Thanks!

[chulett]

Not really... does it really need to be user defined? Curious also what you mean by "loops" in the SQL.

[irshanuddin]

So read from file, eh?
What I really meant was that it takes results from one query and outer joins to another and so on and so forth for 10 iterations to create groupings.
Still grappling with it, my first week at a new place.

Thanks!

[chulett]

Can you put a view over all that, source from the view instead?

[irshanuddin]

That's exactly what we are trying to replace, a view.

Looks like we will be reading from a file.
Thanks for your input.

[chulett]

Okay... the view would hide all of the looping / iterations / grouping shenanigans from DataStage and just look like a straight select from a single table so seems to me it shouldn't really need to be any kind of 97K characters huge... but okay.

[kduke]

The perfect example of doing too much in SQL. Break your SQL up into chunks. You are probably doing ranking. Do that SQL into lookups. Be creative. Learn the ETL tool. Quit relying on your SQL skills to do ETL.

[johnboy3]

Having less than one year with DataStage, and only part-time at that, I suspect this answer has value that I cannot quite Grok.

john3