select statement in Transformer Stage

[sarathi]

Can we pass StageVariable into Select statement in Transformer Stage..

Here is what i am looking to pass to LOC VARIABLE

LOC = SELECT searchcode from LOCATIONS
WHERE searchcode like STAGEVARIABLE

Any help appreciated

Sarathi

[anbu]

Is LOC target field?

[anbu]

Is LOC target field?

[sarathi]

Yes LOC is target field

[anbu]

Then yes possible

Code: Select all

LOC = 'SELECT searchcode from LOCATIONS WHERE searchcode like ' : STAGEVARIABLE

[chulett]

I'm curious how you are planning on executing your 'select statement in a Transformer stage'.

[sarathi]

LOC = 'SELECT searchcode from LOCATIONS WHERE searchcode like ' : STAGEVARIABLE

This is not working. It's taking as string and passing...

Craig- Do u have solution for this. I appreciate your help


Sarathi

[ray.wurlod]

Craig would not know what solutions U does or doesn't have.

The second person personal pronoun in English is spelled "you", not "u".

How are you using a SELECT statement in a Transformer stage? The Transformer stage does not connect to external data sources.

[sarathi]

Hi

Sorry about mis-pronouncation.
Can it be done by Sub-routines like DSRoutines.
I am not particular that it should be done in Transformer stage.
Any type of solution for this issue will be appreciated.

Sarathi

[chulett]

That's the problem, we don't know what the "it" is you are trying to do, what "issue" it is you are trying to solve. About I all see if you are trying to build a string of dynamic SQL, which by itself is a pretty trivial undertaking but after that... ??

How about you explain what problem you are trying to solve? Forget DataStage, forget the whole 'select statement in a transformer' thing, just let us know where this all started and where you're trying to go.

[kduke]

Use a parameter. Your whole SQL can be #jpMySqlStatement#.

[sarathi]

Let me explain to you in brief....

Need to CALL SELECT ...LIKE VARIABLE IN Transformer/Applicable Stage.I declared VARIABLE as STAGE VARIABLE.

Value of VARIABLE = If LOCATION LIKE 'Technology Center' Then 'BACKUP ' Else 'TTCE' else If LOCATION LIKE .........

I am using '%Technology Center%' to check for LIKE condition in IF statement.

Let me know if you need additional information.

Sarathi

[chulett]

Ummm... yes. And I'd prefer you explained completely rather than briefly, briefly is just extending the pain.

You keep using the word "select" - is there a database involved in this derivation somewhere? Are you needing to pull information from a table using SQL or is this just a simple "if-then-else" field derivation? Doing the like and the % wildcards is fine for something like Oracle but has no place in a transformer derivation... unless (as I asked before) you are trying to dynamically build some SQL and then somehow execute that in a database elsewhere for some still unknown purpose.

In other words, the picture remains unclear as to what it is that you need to accomplish. Please re-read the last paragraph in my previous post and let's try this again.

[sarathi]

I needing to pull information from a table using SQL in which uses field variable from "if-then-else" derivation. Using the % wildcards in If-then-else.

SELECT A From B where A LIKE 'VARIABLE'

VARIABLE= If LOCATION LIKE 'Technology Center' Then 'BACKUP ' Else 'TTCE' else If LOCATION LIKE .........

I was trying to do in Transformer stage but could not succeed.

Hope this Helps

Sarathi

[ray.wurlod]

A Transformer stage can not access a data source.