Decode function inside a decode function oracle

Anushree Bakshi 0
Viewed 25

I have a requirement wherein I have to execute this query (SUBSTR(sct.state_customer_type,INSTR(sct.state_customer_type,'(')+1,INSTR(sct.state_customer_type,')')-INSTR(sct.state_customer_type,'(')-1)) when user selects English language and if the language is selected as Spanish then execute this query (SUBSTR(sct.state_customer_type,1,INSTR(sct.state_customer_type,'(')-1)). The language id would also come dynamically. How can I write decode inside another decode function.

Thank you for your replies in advance.

oracle
1 Answers

I don't see any decode functions in your code, but if your language id variable is v_lang, you could write:

select decode(v_lang,
    'English', 
        (SUBSTR(sct.state_customer_type,INSTR(sct.state_customer_type,'(')+1,INSTR(sct.state_customer_type,')')-INSTR(sct.state_customer_type,'(')-1)),
    'Spanish',
        (SUBSTR(sct.state_customer_type,1,INSTR(sct.state_customer_type,'(')-1)),
    null -- if v_lang is something else
    )
from ....
where ....

However, these days we recommend using ANSI CASE statements instead of decode. It's similar but easier to read and more portable.

select CASE v_lang
    WHEN 'English'
    THEN (SUBSTR(sct.state_customer_type,INSTR(sct.state_customer_type,'(')+1,INSTR(sct.state_customer_type,')')-INSTR(sct.state_customer_type,'(')-1))
    WHEN 'Spanish'
    THEN (SUBSTR(sct.state_customer_type,1,INSTR(sct.state_customer_type,'(')-1))
    ELSE null -- if v_lang is something else
    END
from ....
where ....
kfinity0
Related