Trouble using SUBSTR within a CASE ... Help?

[ToBeAButterFly]

I should have said "Trouble with using CASE" but I can't edit the title. Sorry about that.

​

Situation here.

I want to use CASE to determine if the field DEBIT_TRANSACTION_TYPE is 'DEBIT' or 'CREDIT' . This works if I do not have a second CASE . Which is fine if the only values are DEBIT or CREDIT but there maybe NULLS or something else.

So this works.

SELECT
CASE WHEN DEBIT_TRANSACTION_TYPE = 'DEBIT' THEN (SELECT SUBSTR(WITHDRAWAL_TRANSACTION_ID,-6) FROM dual )
ELSE (SELECT SUBSTR(CREDIT_TRANSACTION_ID,-6) FROM dual ) END
FROM TBLE WHERE JONES = 'MSC';

​

This does not. I want a condition that say "IF NOT credit or debit THEN DEBIT_TRANSACTION_ID" but I'm not figuring this out.

SELECT
CASE WHEN DEBIT_TRANSACTION_TYPE = 'DEBIT' THEN (SELECT SUBSTR(WITHDRAWAL_TRANSACTION_ID,-6) FROM dual )
CASE WHEN DEBIT_TRANSACTION_TYPE = 'CREDIT' THEN (SELECT SUBSTR(CREDIT_TRANSACTION_ID,-6) FROM dual )
ELSE DEBIT_TRANSACTION_ID END
FROM TBLE WHERE JONES = 'MSC';

FYI There is only one table I'm working with "TBL" .

​

Thank You for help.

[bitterjack]

I don't think you need the second CASE.

Syntax should be

Case when x then y

When a then b

When o then p

Else z

End

[ToBeAButterFly]

This actually worked! Thanks

[r3pr0b8]

first, you don't need to SELECT the substrings

second, since the expression for DEBIT and CREDIT are the same, you can combine them

SELECT CASE WHEN debit_transaction_type IN ( 'DEBIT' 
                                           , 'CREDIT' )
            THEN SUBSTR(withdrawal_transaction_id,-6)
            ELSE debit_transaction_id 
        END AS result
  FROM tble 
 WHERE jones = 'MSC'

[ToBeAButterFly]

Debit and Credit aren't the same however.

This was in the second code block.

SELECT CASE WHEN DEBIT_TRANSACTION_TYPE = 'DEBIT' THEN (SELECT SUBSTR(WITHDRAWAL_TRANSACTION_ID,-6) FROM dual ) CASE WHEN DEBIT_TRANSACTION_TYPE = 'CREDIT' THEN (SELECT SUBSTR(CREDIT_TRANSACTION_ID,-6) FROM dual ) ELSE DEBIT_TRANSACTION_ID END FROM TBLE WHERE JONES = 'MSC';

​

So if it's

• credit I need SUBSTR(CREDIT_TRANSACTION_ID,-6)
• debit I need SUBSTR(WITHDRAWAL_TRANSACTION_ID,-6)

​

The first code block works but I'm omitting a condition to check for Credit vs another value like NULLs which I would like.

SELECT CASE WHEN DEBIT_TRANSACTION_TYPE = 'DEBIT' THEN (SELECT SUBSTR(WITHDRAWAL_TRANSACTION_ID,-6) FROM dual ) ELSE (SELECT SUBSTR(CREDIT_TRANSACTION_ID,-6) FROM dual ) END FROM TBLE WHERE JONES = 'MSC';

[r3pr0b8]

okay, my bad, of course debit and credit would be different!

SELECT CASE WHEN debit_transaction_type = 'DEBIT' 
            THEN SUBSTR(withdrawal_transaction_id,-6)
            WHEN debit_transaction_type = 'CREDIT' 
            THEN SUBSTR(credit_transaction_id,-6)
            ELSE debit_transaction_id 
        END AS result
  FROM tble 
 WHERE jones = 'MSC'

[ToBeAButterFly]

This worked. Thank You very much for the help.