Friday, February 24, 2012

Error in CASE expression

I would appreciate assistance as to how I should correct the following CASE expression::

BilledCarrier = CASE LEN(cur_billed_carrier) WHEN >0 THEN cur_billed_carrier ELSE 'Not Billed' END

I want to return the cur_billed_carrier when its length is >0 but I can't find the correct syntax. My attempt returns an error at the '>' operator. Thank you.

CASE expression has two formats:

case <expr>

when <expr1> then <return_expr1>

when <expr2> then <return_expr2>

...

else <return_expr_N>

end

-- and

case

when <lt_expr1> <operator> <rt_expr1> then <return_expr1>

when <lt_expr2> <operator> <rt_expr2> then <return_expr2>

...

else <return_exprN>

end

So you need to use the 2nd syntax like:

case when LEN(cur_billed_carrier) > 0 then cur_billed_carrier else 'Not Billed' end

-- or

case sign(len(cur_billed_carrier)) when 1 then cur_billed_carrier else 'Not Billed' end

|||Very clear; thank you

No comments:

Post a Comment