soup_or_power_at_yahoo.com wrote:
> Can anyone tell me how the ||'s are interpreted in the DECODE below?
> I was thinking string concatenation, but shouldn't there be
> commas after 'M' and 'T'?
> Many thanks for your help.
>
> SELECT
> DECODE(NVL(template_id,0),0,'M' || mailing_id,'T' ||
> template_id),
> from A
>
You owe me a cup of coffee.
I assume there where more lines of code, as the final comma is
out of the question.
- you have a template_id, which can be NULL.
- In order to circumvent NULLs for template_id, there's this NVL();
causing the template_id to have a value always (NULL will become
zero (0), because of this.
- Keeping the above in mind, you code is:
DECODE(template_id,0,'M' || mailing_id,'T' || template_id) from A;
3a: Let's take some values: template_id is 13, mailing_id is 9999:
DECODE(13,0,'M' || 9999,'T' || 13) from A;
Here's the first bad habit: implicit conversions: you
compare/concatenate values to strings. At least in one case: when
mailing_id IS NULL (it becomes a numeric zero, not a string '0').
- Do the math:
Compare 13 to 0, if true, evaluate to 'M', concatenated with 9999.
In all other cases, evaluate to 'T' concatenated with 13.
In the example above, it would result in 'T13'.
4b: If mailing_id IS NULL, then the equation is:
Compare 0 to 0, if true, evaluate to 'M', concatenated with 9999.
In all other cases, evaluate to 'T' concatenated with 13.
You do the math :)
--
Regards,
Frank van Bortel
Received on Wed Aug 03 2005 - 14:12:09 CDT
