IN clause in transformer

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
hitmanthesilentassasin
Participant
Posts: 150
Joined: Tue Mar 13, 2007 1:17 am

IN clause in transformer

Post by hitmanthesilentassasin »

Hi,

Could any one tell me how to define the multiple conditions in transformer similar to the IN clause in a database?

Which other stage apart from transformer will allow me to filter data using job parameters? I very well know that it can't be done using filter stage.

Thanks,
divine
Premium Member
Premium Member
Posts: 149
Joined: Fri Oct 15, 2004 12:13 am
Location: Toronto,divine_auro@yahoo.co.in
Contact:

Post by divine »

Hi
You can do the same using OR condition if you are giving the hardcoded values.
With Regards
Biswajit
dspxlearn
Premium Member
Premium Member
Posts: 291
Joined: Sat Sep 10, 2005 1:26 am

Post by dspxlearn »

I guess a good code for using IN clause as you use in the SQL instead of using multiple OR condition in transformer would be as below:

Example:
If you want to check if your input field has 'A', 'B', 'C', 'D','E' values-

Code:

Code: Select all

If Index('A|B|C|D|E', InputCol,1)
  Then <do something>
  Else <something>
[Note - edited the missing "E" back in for him - easy since it isn't snowing here... :-) Andy]
Thanks and Regards!!
dspxlearn
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

What about "E" ?!!
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
dspxlearn
Premium Member
Premium Member
Posts: 291
Joined: Sat Sep 10, 2005 1:26 am

Post by dspxlearn »

Oops !! I left 'E' out !!
Its snowy outside :D
Thanks and Regards!!
dspxlearn
aravindunni31
Participant
Posts: 27
Joined: Mon Jul 09, 2012 6:51 am
Location: Chennai

Post by aravindunni31 »

Is this still working in DataStage 11.3?
Regards,
Aravind V A
priyadarshikunal
Premium Member
Premium Member
Posts: 1735
Joined: Thu Mar 01, 2007 5:44 am
Location: Troy, MI

Post by priyadarshikunal »

well, I haven't heard If or Index function getting phased out, so I believe it should work in 11.3 as well. You may need to check you syntax if it isn't working for you.
Priyadarshi Kunal

Genius may have its limitations, but stupidity is not thus handicapped. :wink:
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

As noted, the short answer to your question is yes. If you are having a problem, please start a new post where we can discuss your particular issue.
-craig

"You can never have too many knives" -- Logan Nine Fingers
rameshrr3
Premium Member
Premium Member
Posts: 609
Joined: Mon May 10, 2004 3:32 am
Location: BRENTWOOD, TN

Post by rameshrr3 »

Despite its many strengths in customizability using C++, I still find the standard (prebuilt) DataStage parallel job function library to be hands down the worst (& worst documented) of any self respecting ETL tool in the Gartner magic quadrant (as opposed to its BASIC transformer cousin). I see that many architects well versed in the old school still use BASIC transformer (despite some negative publicity) because its just a pain to graphically debug parallel custom routines unless you set up a properly functioning eclipse or net beans remote development toolkit. The BASIC transformer, for all its limitations (no loop variables etc, no grid support) is still ahead when it comes to functions , moreover in an SMP environment it is easily 'parallelizable' (internally uses a "dsjob" operator in multi-instance invocation mode) and doesn't create a whole host of issues. On a grid environment however it would have to be constrained to a conductor node pool.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Thinking a little outside the square, another possibility is to use the Data Rules stage with a data rule definition based on an in_reference_list check.

Yet another possibility is to use one of the lookup table functions in a Modify stage, with a suitable default value to reflect the "not found" condition.

Of course, the Index() function technique works fine if set up properly (with delimiters).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
rameshrr3
Premium Member
Premium Member
Posts: 609
Joined: Mon May 10, 2004 3:32 am
Location: BRENTWOOD, TN

Post by rameshrr3 »

I would agree with Ray, the lookup functions ( Or Should i call them specifications :wink: ) ) in modify stage have been used a couple of times by me in some projects, but the modify stage does not have a lookup_string_from_string[] specification, its usually an int to to string or string to int ( throw in also the uint and ustring ) .

I was curious to see if an user (developer ) could create their own modify stage spec, but i guess that can happen only if the source code for those classes and the stage itself was made public domain , and developers had an idea how they could go about creating their own user defined spec.

Data rules - i believe the rules would first be defined in the IS Console rich client tool and published for use by data rules stage correct ? What 'scripting' language do data rules expressions use ?
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

rameshrr3 wrote:I believe the rules would first be defined in the IS Console rich client tool and published for use by data rules stage correct ?
Data Rule Definitions can be created in, and published from, the Data Rules stage, as well as using the IA console.
rameshrr3 wrote:What 'scripting' language do data rules expressions use ?
It's really just an expression builder; I don't believe that there's a named language involved.
rameshrr3 wrote:the Modify stage does not have a lookup_string_from_string[] specification
I had only intended it to generate a flag - if its default value is returned, then the sought value was not found. That's really all that would be needed for the scenario described.

I also note that the OP specifies version 7.x, which rules out using the Data Rules stage. But the Modify stage solution would still work.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply