Kapil R Kapil R - 4 months ago 6
SQL Question

Decode function in oracle for null values

I am working on a java code in which i am comparing the user input values with database values.
I am here writing to display the alert message to the users on selecting particular division from drop down menu. But there are some alert message which I want to display to all users of every division.
I have the table columns as

sr_n0
,
alert_desc
,
div_code
,
alert_flag
.

Initially I am checking the
div_code
and
alert_flag
to display alert message for particular division.
But now I want to know how the
decode
function will work if I does not provide
div_code
but still the
alert_flag
= y

I have tried this code :

SELECT DECODE(DIV_CODE,'61','Division A',
'62','Division B',
'ALL')
FROM ALERTS WHERE ALERT_FLAG='Y';

Answer

If there is no match, then the result will be 'ALL'. I would strongly encourage you to use the ANSI-standard CASE statement, instead of the Oracle-specific DECODE() function:

SELECT (CASE DIV_CODE
            WHEN '61' THEN 'Division A'
            WHEN '62' THEN 'Division B' 
            ELSE 'ALL'
        END)
FROM ALERTS
WHERE ALERT_FLAG = 'Y';
Comments