Oracle CASE statement in Datastage

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

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.

can you try like this?

Code: Select all

(Oconv(DSLink.code, "R700,709") And Oconv(DSLink2.num, "R700,709") And Oconv(DSLink2.typ, "R700,709")) 

like this separate each combination and try..

[chulett]

Pay closer attention to what Ray noted was the result of the OConv.

[kumar444]

Pay closer attention to what Ray noted was the result of the OConv.

Code: Select all

NOT((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 want to reject those codes, so i have used something like this above,
a NOT of the Oconv.
Please Correct me if i am wrong.

[chulett]

Again, what comes from the OConv is nothing that can be treated as a boolean. You'll need to explicitly check the output and then (if so desired) set the result to true/false.

[kumar444]

Again, what comes from the OConv is nothing that can be treated as a boolean. You'll need to explicitly check the output and then (if so desired) set the result to true/false.

I am new to this Oconv thing and never used stage variables.
Can you please explain in detail? Sorry if you get annoyed.

[chulett]

I just meant you can't let the derivation resolve itself, you need to wrap it in an if-then-else and say "if BLAH_BLAH_BLAH then @TRUE else @FALSE". Do that in a well-named stage variable and now you can just put that stage variable all by itself in the constraint. As an example, name the stage variable "svRowIsValid" and then simply put "svRowIsValid" in the constraint - when it is true, rows will pass through.

Sorry, not annoyed.

[kumar444]

I just meant you can't let the derivation resolve itself, you need to wrap it in an if-then-else and say "if BLAH_BLAH_BLAH then @TRUE else @FALSE". Do that in a well-named stage variable and now you can just put that stage variable all by itself in the constraint. As an example, name the stage variable "svRowIsValid" and then simply put "svRowIsValid" in the constraint - when it is true, rows will pass through.

Sorry, not annoyed.

Its better than clear now. Thanks. Will try to do this.

[ray.wurlod]

A minor correction to what Craig said.

If there were no zeroes in your test ranges, the output from Oconv() in this case could be treated as Boolean. In DataStage BASIC, 0 or "" are "false" in a Boolean context and any other non-null value is "true".

[chulett]

Huh... wasn't sure how that empty string would test and thought that was the issue with it (apparently) not testing as expected, so thought it would be prudent to be explicit in the assignment of the result.