Page 1 of 1
IN clause in transformer
Posted: Wed Dec 19, 2007 7:11 am
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,
Posted: Wed Dec 19, 2007 8:43 am
by divine
Hi
You can do the same using OR condition if you are giving the hardcoded values.
Posted: Wed Dec 19, 2007 8:58 am
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>
Andy]
Posted: Wed Dec 19, 2007 4:03 pm
by ray.wurlod
What about "E" ?!!
Posted: Wed Dec 19, 2007 5:00 pm
by dspxlearn
Oops !! I left 'E' out !!
Its snowy outside :D
Posted: Wed Apr 13, 2016 12:41 am
by aravindunni31
Is this still working in DataStage 11.3?
Posted: Wed Apr 13, 2016 2:38 am
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.
Posted: Wed Apr 13, 2016 7:02 am
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.
Posted: Wed Apr 13, 2016 12:57 pm
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.
Posted: Wed Apr 13, 2016 5:18 pm
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).
Posted: Thu Apr 14, 2016 2:51 pm
by rameshrr3
I would agree with Ray, the lookup functions ( Or Should i call them specifications
) ) 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 ?
Posted: Thu Apr 14, 2016 5:11 pm
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.