Using a comma in the default param of DECODE

[mamabird]

I am using the DECODE function and my default value has a comma. When I run the query, it's cutting off everything after the comma in my default. How can I get the entire string returned?

SELECT DECODE (
mycol, '1', 'One',
'2', 'Two',
'3', 'Three',
'This, is the default') AS myval
FROM mytable

The query returns only "This" as the default. Thanks!

 

[SantaMufasa]

Mama,

There is something else going on here. Your code works just as expected:

select * from mytable;

MYCOL
-----
    1
    2
    3
    4

SELECT DECODE (
mycol, '1', 'One',
           '2', 'Two',
           '3', 'Three',
           'This, is the default') AS myval
FROM mytable
/

MYVAL
--------------------
One
Two
Three
This, is the default

To troubleshoot this issue, could you please issue this SQL*Plus command prior to re-running your code:

clear columns

Once you re-run your code, let us know if your results change.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[mamabird]

Thanks Dave/Mufasa/Santa. I should have troubleshooted (is that even a word?) this better. I am able to successfully run this DECODE statement in Toad, it gives me the results I am looking for.

Where it's breaking is in ColdFusion. I have the code there (the exact same statement as in Toad) but when I run it using a browser, it takes everthing after the comma and moves it to the next line (I'm using this to populate a dropdown list.)

This is the actual statement I'm using in my code:

SELECT last_name || DECODE(first_name, 'NA', '', ' ,'||first_name) AS name

I am wanting to list people by last name, first name unless the value is NA - I don't want to display NA, NA. This is what I should get:

NA
Grouch, Oscar
Smith, Tom

But instead I'm getting:

NA
Grouch
Oscar
Smith
Tom

Unless you have any insight as to why CF (or browser rendering) is doing this, I will post this question in the CF forum.

Thanks!
mamabird

 

[SantaMufasa]

The word you want is "troubleshot" .

I would post the problem in the ColdFusion forum since this is obviously a deficiency of that software. Once they help resolve the problem there, please be sure to come back to this thread and post the resolution.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]