Oracle CASE statement in Datastage

[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.

[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.

[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.

[pandeesh]

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?

[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.

[pandeesh]

so, if a.code is 701 then it'll return 701. Awesome Ray!

[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..

[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.

[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?

[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.

[kumar444]

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?

[pandeesh]

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

[pandeesh]

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!!

[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.

[kumar444]

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.