Converting DECODE into CASE statement

Database
Enthusiast

Converting DECODE into CASE statement

I had difficulty in translating this nested DECODE function into CASE statements.

Please help

DECODE('Y', (DECODE(NOTES_QS_CODE.name, 'QS-9000 Certified', 'Y')), 'QS-9000 Certified',
(DECODE(NOTES_ISO_CODE.name, 'Unknown', 'Y')), 'Unknown',
'Error')
3 REPLIES
Junior Contributor

Re: Converting DECODE into CASE statement

I think this should be:

CASE NOTES_QS_CODE.name
WHEN 'QS-9000 Certified' THEN NOTES_QS_CODE.name
WHEN 'Unknown') THEN NOTES_QS_CODE.name
ELSE 'Error'
END

And the DECODE is overly complex:
DECODE(NOTES_QS_CODE.name,
'QS-9000 Certified', 'QS-9000 Certified',
'Unknown', 'Unknown',
'Error')

Dieter
Enthusiast

Re: Converting DECODE into CASE statement

CASE WHEN 'Y' = ( CASE WHEN NOTES_QS_CODE.name = 'QS-9000 Certified'THEN 'Y' ELSE NULL END )
THEN 'QS-9000 Certified'
WHEN 'Y' = ( CASE WHEN NOTES_ISO_CODE.name = 'ISO Certified' THEN 'Y' ELSE NULL END)
THEN 'ISO Certified'
WHEN 'Y' = ( CASE WHEN NOTES_QS_CODE.name = 'Unknown' THEN 'Y' ELSE NULL END)
THEN 'Unknown'
WHEN 'Y' = ( CASE WHEN NOTES_ISO_CODE.name = 'Unknown' THEN 'Y' ELSE NULL END)
THEN 'Unknown'
ELSE 'Error'
END

This is effectively same as

CASE WHEN NOTES_QS_CODE.name = 'QS-9000 Certified'
THEN 'QS-9000 Certified'
WHEN NOTES_ISO_CODE.name = 'ISO Certified'
THEN 'ISO Certified'
WHEN NOTES_QS_CODE.name = 'Unknown'
THEN 'Unknown'
WHEN NOTES_ISO_CODE.name = 'Unknown'
THEN 'Unknown'
ELSE 'Error'

Junior Contributor

Re: Converting DECODE into CASE statement

I didn't notice the 2nd table, so my answer was wrong.
But your CASE doesn't match the original decode, too, there was no 'ISO Certified':

CASE WHEN NOTES_QS_CODE.name = 'QS-9000 Certified'
THEN 'QS-9000 Certified' -- or NOTES_QS_CODE.name
WHEN NOTES_ISO_CODE.name = 'Unknown'
THEN 'Unknown' -- or NOTES_ISO_CODE.name
ELSE 'Error'
END

Dieter