CASE statement in Oracle sql

Question

i have a problem with CASE sintax (I think) and I hope you can help me. I'll try to explain my problem:

I have 2 fields DV_CODE e DV_NAME and I would like to have DV_NAME = 'Other person' if I haven't the code, that means DV_CODE = null

This is my code:

SELECT rep_code,
   name,
   act_date,
   dv_code,
   CASE
      WHEN DUAL_CODE_AM IS NULL THEN DV_NAME= 'Other person'
      WHEN DUAL_CODE_PM IS NULL THEN DV_NAME= 'Other person'
   END
   AS dv_name
  ......

(in my case "else"statment is useless)

but system returns to me 2 errors:

or: missing keyword or: missing parenthesis

thanks in advance.

You mention in your question you want to check DV_CODE IS null, but then in your sample code you check DUAL_CODE_AM IS NULL and DUAL_CODE_PM ?? so what are you checking for NULL? DV_CODE, DUAL_CODE_AM, or DUAL_CODE_PM ? — Ditto
– Ditto, Commented May 22, 2015 at 13:30
I'm sorry, this is the "correct code": SELECT rep_code, name, act_date, CASE WHEN DUAL_CODE_AM IS NULL THEN DV_NAME= 'Other person' WHEN DUAL_CODE_PM IS NULL THEN DV_NAME= 'Other person' END AS dv_name ...... i check if one of DUAL_CODE_AM OR DUAL_CODE_PM are NULL — Solido
– Solido, Commented May 22, 2015 at 13:32
@Solido you can edit your question and add this new snippet. — kha
– kha, Commented May 22, 2015 at 13:39

jarlh · Accepted Answer · 2015-05-22 13:39:25Z

2

SELECT rep_code,
   name,
   act_date,
   CASE
      WHEN DUAL_CODE_AM IS NULL THEN 'Other person'
      WHEN DUAL_CODE_PM IS NULL THEN 'Other person'
   END
   AS dv_name
  ......

edited May 22, 2015 at 13:39

answered May 22, 2015 at 13:26

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

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

Collectives™ on Stack Overflow

CASE statement in Oracle sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related