asked on

oracle decode statement

SELECT BATCH_ID,
PRODUCT_CODE,
UNIT_ID,
TIME_FROM_COLLECTION,
to_char(FILTRATION_START,' MM/DD/RRRR HH24:MI')
filtration_start,FILTRATION_START_USER,
to_char(FILTRATION_END,'MM/DD/RRRR HH24:MI') filtration_end,
FILTRATION_END_USER,
TOTAL_ELAPSED_TIME,
decode(COLOR_RESULT,'RED','Y','N') color_result, TOTAL_ELAPSED_COLOR,
BAG_CODE, FILTRATION_COMMENTS||chr(10)||SUPERVISOR_COMMENTS
comments,
FAILED_FLAG, RECORD_MODIFIED,
decode(WAREHOUSE_ID ,'A','Aurora','Tinley Park') warehouse_id
from AU_LEUKOREDUCTION_TRACKING_VW1
where warehouse_id = 'A'
and bag_code = '54'
and filtration_start = '08-feb-2012' ;
My question is decode(COLOR_RESULT,'RED','Y','N') color_result, TOTAL_ELAPSED_COLOR,
I want to color_result or total_elapsed_color if 'RED' the 'Y' else 'N' . How can I do that in decode ?

Sean Stuber

what you have is correct

decode(COLOR_RESULT,'RED','Y','N')

maybe I'm not understanding the question

anumoses

ASKER

I have two columns that may be exception flag. color_result and total_elapsed_color
if either one is 'RED' then I want to populated value as 'Y' else 'N'. I have the decode for color_result, but is there a way to add or in decode for total_elapsed_color? or we want a case statement?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

I would use CASE if you can:

case when color_result = 'RED' or total_elapsed_color='RED' then 'Y' else 'N' end

the decode would be like:

decode(COLOR_RESULT,'RED','Y',decode(total_elapsed_color,'RED','Y','N'))

anumoses

ASKER

case when 'RED' in (color_result,total_elapsed_color) then 'Y' else 'N' end

this worked fine.

anumoses

ASKER

thanks