Oracle SQL - CASE WHEN THEN ELSE ignoring ELSE part

Question

i have a problem with my sql query and dont know how to solve it:

The output table consists of.

1. SERIALNR   VARCHAR(26) NOT NULL
2. LP_BASIC   VARCHAR(20) NOT NULL
3. LP_NEW     VARCHAR(20)
4. SCORE      NUMBER(20)  NOT NULL
5. CURRENT_LP VARCHAR(20)

The query itself works well and contains no errors.

The LP_BASIC column is always filled whereas the LP_NEW is an optional field. CURRENT_LP should always display LP_NEW (if is not empty) else LP_BASIC (which is never empty).

The problem is, that the "ELSE f.LP_BASIC"-Part is not working. It should return the LP_BASIC value, if no LP_NEW record is found but instead it only returns an empty field.

SELECT 
  f.SERIALNR,
  f.LP_BASIC,
  f.LP_NEW, 
  f.SCORE,
  CASE
    WHEN
      f.LP_NEW
      IS NOT NULL
        THEN
          f.LP_NEW
    ELSE
      f.LP_BASIC
  END
AS CURRENT_LP 
FROM (
        SELECT 
          SERIALNR, 
          MAX(SCORE) MAX_ID
        FROM 
          EAPS.PS_TC_SM 
        GROUP BY 
          SERIALNR
     ) x 
INNER JOIN 
  EAPS.PS_TC_SM f 
ON 
  f.SERIALNR=
  x.SERIALNR 
AND 
  f.SCORE = 
  x.MAX_ID

Any help apreciated.

Thank you in advance.

Rob.

UPDATE (solved)

The LP_NEW column was not null, there was a single space char in it, so the "IS NULL" did not work.

Please post some sample data, needed result and what you are getting from your query — Aleksej
– Aleksej, Commented Nov 2, 2016 at 14:41
wouldn't this be much simpler to read? coalesce(f.LP_NEW ,f.LP_BASIC) — Kritner
– Kritner, Commented Nov 2, 2016 at 14:42
@Kritner Coalesce is better. Nvl compares both values, coalesce stops as soon as a value is found. — JohnHC
– JohnHC, Commented Nov 2, 2016 at 14:43
Are you certain lp_new is actually null, and doesn't have a blank in it? — DCookie
– DCookie, Commented Nov 2, 2016 at 14:46
I am inclined to agree with DCookie, and predict that neither NVL() nor COALESCE() will solve the problem. It would be very weird if NVL() or COALESCE() would fix anything CASE does wrong, since they are logically equivalent - with the difference in implementation that makes CASE and COALESCE() a little faster than NVL() in some cases. — user5683823
– user5683823, Commented Nov 2, 2016 at 14:50

Frank Schmitt · Accepted Answer · 2016-11-02 14:56:14Z

2

(This was too long for a comment, therefore posted as an answer)

As already mentioned in the comments, your CASE statement is perfectly fine; you should re-check your input data.

Example that demonstrates the correctness of your approach with fake data:

with v_input(serialnr, lp_basic, lp_new) as (
  select 
    level, 
    'basic_' || to_char(level), 
    (case when mod(level, 2) = 0 then 'new_' || to_char(level) else null end)
  from dual
  connect by level <= 10)
SELECT 
  f.SERIALNR,
  f.LP_BASIC,
  f.LP_NEW, 
  CASE
    WHEN
      f.LP_NEW
      IS NOT NULL
        THEN
          f.LP_NEW
    ELSE
      f.LP_BASIC
  END
AS CURRENT_LP 
FROM v_input f

answered Nov 2, 2016 at 14:56

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

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

1 Comment

Robama Over a year ago

Thank you. There was a single space char in all LP_NEW records that prevented the CASE statement to work so i changed the IS NULL to = ' ' and now the whole thing works as expected. Im kind of new to sql and did not thought about this could happen.

danielku97 · Accepted Answer · 2016-11-02 14:45:49Z

-1

Try using the oracle NVL function instead, essentially it will select the value specified, if it is null, then it will select the secondary value.

SELECT NVL(LP_NEW,LP_BASIC)

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm

answered Nov 2, 2016 at 14:45

danielku97

608 bronze badges

1 Comment

Frank Schmitt Over a year ago

That is totally equivalent to the CASE approach and doesn't solve the OP's problem.

Collectives™ on Stack Overflow

Oracle SQL - CASE WHEN THEN ELSE ignoring ELSE part

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related