ora-00932 inconsistent datatypes expected number got char plsql-CodePudding

I tried to write an SQL

SELECT
    CASE
        WHEN SOURCE_SYSTEM <> 'SHIELD' 
            THEN NVL(NOMINAL_PREMIUM,PAYABLE_PREMIUM)
        WHEN SOURCE_SYSTEM = 'SHIELD' AND UPPER(PRODUCT_NAME_EN) = 'CANCER' AND NVL(NOMINAL_PREMIUM,PAYABLE_PREMIUM) < 100000
            THEN 'LESS THAN 100K'
        ELSE 'OTHER' 
    END AS NEW_GROUP
FROM TABLE

I also tried the same code but with a little twist

SELECT
    CASE
        WHEN SOURCE_SYSTEM <> 'SHIELD' 
            THEN NVL(NOMINAL_PREMIUM,PAYABLE_PREMIUM)
        WHEN SOURCE_SYSTEM = 'SHIELD' AND UPPER(PRODUCT_NAME_EN) = 'CANCER' AND TO_NUMBER(NVL(NOMINAL_PREMIUM, PAYABLE_PREMIUM)) < 100000
            THEN 'LESS THAN 100K'
        ELSE 'OTHER' 
    END AS NEW_GROUP
FROM TABLE

The same error still appears:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

What can I do to fix that?

CodePudding user response：

It happens because of differet types you have in the first "case-when" and "else" parts.

This returns a number:

THEN NVL(NOMINAL_PREMIUM,PAYABLE_PREMIUM)

whereas here you'll get a char:

THEN 'LESS THAN 100K'

The solution would be to convert number to a char:

NVL(NOMINAL_PREMIUM,PAYABLE_PREMIUM) -> to_char(NVL(NOMINAL_PREMIUM,PAYABLE_PREMIUM))

So, here's what I did in order to get rid of this error

with test_data(source_system, nominal_premium, payable_premium, product_name_en) as (
  select 'a', 1, 2, 'b' from dual
)

SELECT
CASE WHEN SOURCE_SYSTEM <> 'SHIELD' 
     THEN to_char(NVL(NOMINAL_PREMIUM,PAYABLE_PREMIUM))
     WHEN SOURCE_SYSTEM = 'SHIELD' AND upper(PRODUCT_NAME_EN) = 'CANCER' AND TO_NUMBER(NVL(NOMINAL_PREMIUM,PAYABLE_PREMIUM)) <100000
     THEN 'LESS THAN 100K'
     ELSE 'OTHER' END AS NEW_GROUP
FROM test_data