Oracle SQL, IF statements

Question

I’m running a query from a table that has info on account numbers, settled charges , the write off figure, and the reason for the write off; so the table is called group_write_off and the table headings, account_id, charge_settled_date, total_charge_amount, amount_written_off, write_off_reason. What I want to do, is pull everything through, so

Select * from group_write_off

but for any case where the write_off_reason includes the word ‘Audit’, I want amount_written_off to show as 0 (zero), for all others i want the original amount_written_off to show.

Many thanks.

Mudassir Hasan · Accepted Answer · 2017-12-07 15:06:29Z

5

Use CASE expression.

SELECT CASE WHEN write_off_reason='Audit' THEN 0 ELSE amount_written_off END
FROM group_write_off

Also try below for checking if Audit word is present in column write_off_reason by using INSTR() function

SELECT CASE WHEN INSTR(write_off_reason,'Audit') = 0 THEN 0 ELSE amount_written_off END
FROM group_write_off

If you want more words

SELECT CASE WHEN INSTR(write_off_reason,'Audit') = 0 THEN 0 
       WHEN INSTR(write_off_reason,'admin') = 0 THEN 0 
       ELSE amount_written_off END
FROM group_write_off

edited Dec 7, 2017 at 15:06

answered Dec 7, 2017 at 14:10

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ex-patriot Over a year ago

Excellent, many thanks. If i wish to include additional write_off reasons, for instance any write_off_reason which includes the word 'admin', how would I insert this into the code above?

Collectives™ on Stack Overflow

Oracle SQL, IF statements

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related