CODEWITHSUNDEEP
sqloracle-databasecase
Robama
Robama

Reputation: 81

Oracle SQL - CASE WHEN THEN ELSE ignoring ELSE part

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.

Upvotes: 2

Views: 5848

Answers (2)

Frank Schmitt
Frank Schmitt

Reputation: 30775

(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

Upvotes: 2

danielku97
danielku97

Reputation: 60

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

Upvotes: -1

Related Questions