select statement in Transformer Stage

[chulett]

Exactly. So... you need to build this SQL string. Check, fine, simple. The question still stands - then what? How are you planning on executing it?

This is a 'select' as in source query, so running it once for every row passing through your job doesn't make much sense unless you're only processing one record or this is for some kind of lookup or you need to pass this to and run another job for every source record. If you are just trying to build source SQL that changes from run to run, you can do as Kim suggested and build it externally and then pass it in as a job parameter. That or just put that SQL in the source SQL stage and pass in your computed 'VARIABLE' field (which again you've computed externally) as the job parameter:

Code: Select all

SELECT A From B where A LIKE '#p_VARIABLE#'

However, since the value seems to be based on the LOCATION field in your source data that may not be practical. So... what a proper solution would be for you is (as one should expect) completely dependant on what the heck it is you are trying to accomplish, something that you either can't or for some reason won't fully articulate for us. Not sure why.

Me, I'm still willing to try and help but without proper and complete requirements from you, that willingness is (unfortunately) rapidly fading...

[sarathi]

I do not know why there is so much confusion on these Topic.
I am trying to build source SQL that changes from run to run. It was suggested to build externally as parameter or Kim suggested.
My first question is what values should i declared in parameters.

Will below will be sequence of steps to resolve it ?

1. Create a parameter in 'Add Environment variables' where value is --
SELECT A From B where A LIKE '#p_VARIABLE#'

2. Create p_VARIABLE in Transformer Stage which has value -
If LOCATION LIKE 'Technology Center' Then 'BACKUP ' Else if LOCATION LIKE Then 'TTCE' else ........

Please EXPLIAN in details sequence of steps as how it can be done using Parameters..

Sarathi

[chulett]

I do not know why there is so much confusion on these Topic.

Simple. Because, in all honesty, you've done a terrible job of helping us help you and explaining clearly what the heck you were/are trying to do. Maybe it's all me and I'm being especially dense or something but I honestly don't think so. [shrug]

[chulett]

What you posted is *still* confusing and shows a lack of understand of how the tool works. Still... let's keep trying...

1. It doesn't need to be an environment variable, just a simple job parameter could work.

2. In my example, "p_VARIABLE" was not a stage variable but yet again a simple job parameter which showing it enclosed in hash/pound signs should have made clear. And meant to be used in a database stage, not a transformer. Not sure why you keep focusing on the dang transformer when we've asked you repeatedly to step back from it.

For me, it would still help tremendously if you could articulate your requirements, what the hell it is that you are attempting to do - and without simply either repeating yourself or parroting my words back to me. Didn't whomever gave you this task give you any such thing? ETL requirements / design specs, something where what you need to do is spelled out in words? Something that might help us understand things like where LOCATION comes from, as one smal smalll example. Sheesh.

Anybody want to jump in and take a shot at this? Am I making a mountain out of a molehill? Be glad for the help if I am...

[chulett]

If this is all too much, how about at least addressing where LOCATION comes from? Is this a singleton value, such that once you somehow know what it is "for this run" you can compute the VARIABLE value and then use it in a select statement in a job to grab and process a bunch of data?

Or will something be delivering a series of LOCATION values and for each one you need to do something similar to the above? Can you clarify the role and source of the LOCATION data and then what eventually happens to the data that the SQL you are needing to generate extracts from your still unknown relational database system?

Or is neither guess even remotely close?

[ray.wurlod]

While it is possible to create SQL statements in Transformer stage it is not possible to execute them there or anywhere else in the same job.

My preferred approach would be to create the SQL statement in one job (or routine) controlled from a sequence, then to pass the SQL statement as a parameter into another job that executes it.