DSXchange

Hello all,
Please take a look to the following question and leave your comments.

The SQL Builder, for Oracle stages, has been of great help for my current project and I want to keep using it. My question is if there is some way to get a variable or parametric name as the schema name?
Perhaps doing something in the Table Definitions?
Here is a picture to explain this better.



Thank you,

Yonny R. Serrano

What happens if you start using generated SQL (long enough to add the job parameter for the schema name) then switch to SQL Builder?

I guess you meant "start using custom SQL Statement ..."
I tried it and the custom SQL gets erased when I switched to SQL Builder

No, I meant generated SQL.

I suspect that this, too, would be erased when you open the builder.

Therefore I have no answer to suggest.

Does your support provider have a solution?

Does your support provider have a solution?

I have a PMR open with IBM, but not answer from them yet.

First off, a caveat: I think the SQL Builder tool sucks and is totally unneeded, except on paper so you have it as part of your feature set. I wouldn't touch it with a ten foot pole. There, with that out of the way...

I don't believe you can parameterize the schema with the SQL Builder. I use the 'Generate SELECT from blah blah' option religiously and always parameterize the schema. Trivial to do there. Perhaps you could consider a change in philosophy?

If you insist on using the SQL Builder tool to build the sql and insist on then being able to change the table's schema, you'll need to do it in steps. Generate the sql, copy it to the clipboard, paste into your editor of choice, make the schema change, switch the stage to Custom SQL and then paste your tweaked version into the stage.

ps. Been playing with this while posting. Looks like you have one other option and that would be to make the entire "schema.tablename" a single job parameter. Then right-click on the table on the 'canvas' in the Builder and select Properties. Even though the tablename is Read Only, you can click on the ellipsis and then 'Insert Job Parameter'. At runtime, you could then change just the schema name in the parameter. Of course you could change the table name too but that would just make it blowed up real good.

So? Any of this help?

Yes, of course

I am just giving some extra time to my "support provider" to post here their answer (in case they find any and it is different from yours).

Same goes for my other thread: OCI Date Conversion Failed.

Hello all, thank you for your answers.

My support provider did not give a useful response. So, we are using chulett's suggestion:

Generate the sql, copy it to the clipboard, paste into your editor of choice, make the schema change, switch the stage to Custom SQL and then paste your tweaked version into the stage.

Best regards,

yserrano