Case Function in Oracle - wrong return

Question

I have an table with these fields: id, quarter, description, id_school.

These table have this data:

ID | quarter | description 

12   A         1 YEAR         
12   S         1 Year         
12   A          DONE

I want to get the serie_school based on the below query:

select NVL(
    case 
        WHEN quarter  = 'S' AND 
             (UPPER(description ) LIKE 'DONE' OR 
              UPPER(description ) LIKE '%YEAR' OR  
              UPPER(description ) LIKE 'LANGUAGE') THEN -1 
        WHEN quarter  = 'A' AND 
            (UPPER(description ) LIKE 'DONE' OR  
             UPPER(description ) LIKE '%QUARTER' OR  
             UPPER(description ) LIKE '%STEP' OR 
             UPPER(description ) LIKE 'LANGUAGE') THEN -1 
        WHEN quarter  = 'T' THEN -1 
        ELSE -1 
    end, -1) nvl_return from test 

The return of this on my query is:

ID | quarter | description | nvl_return 

12   A         1 YEAR         1
12   S         1 Year         1 *- (this column has the wrong answer)*
12   A          DONE         -1

The answer of line 2 is wrong, because the QUARTER field is 'S' and the description field have 'year', so it needs to be -1, but in Oracle is returning 1.

Is there anyone who can help me with this?

Thanks in advance

Answer 1

Alexandre,

Examine your statement, where I have added "<======" to point out the various possible amounts for this column.

select NVL(
    case 
        WHEN quarter  = 'S' AND 
             (UPPER(description ) LIKE 'DONE' OR 
              UPPER(description ) LIKE '%YEAR' OR  
              UPPER(description ) LIKE 'LANGUAGE') THEN -1<======= 
        WHEN quarter  = 'A' AND 
            (UPPER(description ) LIKE 'DONE' OR  
             UPPER(description ) LIKE '%QUARTER' OR  
             UPPER(description ) LIKE '%STEP' OR 
             UPPER(description ) LIKE 'LANGUAGE') THEN -1<====== 
        WHEN quarter  = 'T' THEN -1 <======
        ELSE -1 <=======
    end, -1 <-this will never happen) nvl_return from test 

You return "-1" in every single case. "-1" is the only value returned, by the only column returned in your query.

So when you say the results are:

ID | quarter | description | nvl_return 

12   A         1 YEAR         1
12   S         1 Year         1 *- (this column has the wrong answer)*
12   A          DONE         -1

Unfortunately, your query cannot produce the results you've stated.

Try creating a SQL fiddle, to explain the issue. SQL Fiddle