Page 1 of 2
Oracle CASE statement in Datastage
Posted: Mon Jun 27, 2011 4:08 pm
by kumar444
I have this case statement to be implemented in datastage:
Code: Select all
CASE WHEN
((NVL(a.code,'0') IN ('0','00','000')) OR
(a.code BETWEEN '700' AND '709') OR
(a.code BETWEEN '800' AND '849') OR
(a.code BETWEEN '860' AND '867') OR
(a.code BETWEEN '870' AND '876') OR
(a.code BETWEEN '880' AND '899')
)
AND
((NVL(b.typ,'0') IN ('0','00','000')) OR
(b.typ BETWEEN '700' AND '709') OR
(b.typ BETWEEN '800' AND '849') OR
(b.typ BETWEEN '860' AND '867') OR
(b.typ BETWEEN '870' AND '876') OR
(b.typ BETWEEN '880' AND '899')
)
AND
((NVL(c.num,'0') IN ('0','00','000')) OR
(c.num BETWEEN '700' AND '709') OR
(c.num BETWEEN '800' AND '849') OR
(c.num BETWEEN '860' AND '867') OR
(c.num BETWEEN '870' AND '876') OR
(c.num BETWEEN '880' AND '899')
)
THEN REJECT
ELSE PASS
Here a.code,b.typ,c.num are three different columns from different tables.
Please suggest me to get this done in datastage.
Thanks.
Posted: Mon Jun 27, 2011 5:11 pm
by ray.wurlod
What are the data types? What are the data volumes? There's a really elegant solution using "R" conversion in Oconv(), but you would need to use a BASIC Transformer stage or server job to take advantage of this. Otherwise (if you can't or won't use a BASIC Transformer stage or server job), you need to build a large nested If..Then..Else construct.
Posted: Mon Jun 27, 2011 6:11 pm
by kumar444
Thanks Ray ,
I am using a parallel job here. Please tell me how to do this in parallel.
Datatypes are varchar for all. Data volume less than 10000 rows.
Re: Oracle CASE statement in Datastage
Posted: Mon Jun 27, 2011 9:04 pm
by pandeesh
kumar444 wrote:I have this case statement to be implemented in datastage:
Code: Select all
CASE WHEN
((NVL(a.code,'0') IN ('0','00','000')) OR
(a.code BETWEEN '700' AND '709') OR
(a.code BETWEEN '800' AND '849') OR
(a.code BETWEEN '860' AND '867') OR
(a.code BETWEEN '870' AND '876') OR
(a.code BETWEEN '880' AND '899')
)
for example if a.code 701 in the column ,then what's the result?
is it 0 or 701?
Posted: Mon Jun 27, 2011 9:05 pm
by ray.wurlod
You can use a BASIC Transformer stage in a parallel job. To determine whether the value in InLink.code is in a particular range you can use
Code: Select all
Oconv(InLink.code, "R700,709;800,849;860,867;870,876;880,899")
This will return the original value if code falls in any of those ranges, or "" otherwise.
Posted: Mon Jun 27, 2011 9:08 pm
by pandeesh
so, if a.code is 701 then it'll return 701. Awesome Ray!
Posted: Tue Jun 28, 2011 12:46 am
by kumar444
Hi Pandeesh , there is an AND condition between three columns. If all of the three columns together satisfy the above codes, then reject Else pass.
Thanks Ray. I couldnt see your content for now. I just bought a membership, have to wait until it reflects..
Posted: Tue Jun 28, 2011 6:42 am
by chulett
In the meantime, you could always translate that into the same structure you would use anywhere a case statement is not supported - a large nested if-then-else construct.
Posted: Tue Jun 28, 2011 8:44 am
by kumar444
Thanks Chulett. But i have three columns AND 'ed above.
How can i use a nested if -then -else construct?
Did you mean to say if then else with stage variables?
Ray, How can i include three columns in your statement above?
Posted: Tue Jun 28, 2011 9:01 am
by chulett
The syntax is almost identical to what you already have, you basically replace the "CASE WHEN" with "IF". You'll need to change the NVL and IN verbs to DataStage equivalents, of course. And disregard the 'nested' part in this case, you just need one big one if-then-else.
Posted: Tue Jun 28, 2011 9:10 am
by kumar444
chulett wrote:The syntax is almost identical to what you already have, you basically replace the "CASE WHEN" with "IF". You'll need to change the NVL and IN verbs to DataStage equivalents, of course. And disregard the 'nested' part in this case, you just need one big one if-then-else.
Is this placed in constraint box of a transformer?But i wonder how it would go to exclude hundred values from each column?
Posted: Tue Jun 28, 2011 9:12 am
by pandeesh
ray.wurlod wrote:You can use a BASIC Transformer stage in a parallel job. To determine whether the value in InLink.code is in a particular range you can use
Code: Select all
Oconv(InLink.code, "R700,709;800,849;860,867;870,876;880,899")
This will return the original value if code falls in any of those ranges, or "" otherwise.
can we achive the below using Oconv ?
if link.column >=10 and link.column <=30 then 1 else 2
the one you already mentioned is very much useful in the case of returning own values when the condition is satisfied.
But i want to return some other value , is this possible in Oconv?
Thanks
Posted: Tue Jun 28, 2011 9:19 am
by pandeesh
kumar444 wrote:chulett wrote:The syntax is almost identical to what you already have, you basically replace the "CASE WHEN" with "IF". You'll need to change the NVL and IN verbs to DataStage equivalents, of course. And disregard the 'nested' part in this case, you just need one big one if-then-else.
Is this placed in constraint box of a transformer?But i wonder how it would go to exclude hundred values from each column?
You can go with stage variables!!
Posted: Tue Jun 28, 2011 9:28 am
by chulett
Yes, that could be the derivation of a single stage variable that you would then reference in a constraint. For that, I would leave off the if-then-else and simply left the expression resolve to true or false and then leverage the variable as a boolean in the constraint.
Posted: Tue Jun 28, 2011 9:50 am
by kumar444
ray.wurlod wrote:You can use a BASIC Transformer stage in a parallel job. To determine whether the value in InLink.code is in a particular range you can use
Code: Select all
Oconv(InLink.code, "R700,709;800,849;860,867;870,876;880,899")
This will return the original value if code falls in any of those ranges, or "" otherwise.
Code: Select all
(Oconv(DSLink.code, "R700,709;800,849;860,867;870,876;880,899") And Oconv(DSLink2.num, "R700,709;800,849;860,867;870,876;880,899") And Oconv(DSLink2.typ, "R700,709;800,849;860,867;870,876;880,899"))
I have put this in contraint box of a BASIC Xformer and this outputs nothing. Zero rows.