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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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?
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
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?
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
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?
Last edited by kumar444 on Tue Jun 28, 2011 9:14 am, edited 1 time in total.
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
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?
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
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
(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.