I'm trying a case expression:

SELECT
    CASE
        WHEN CAB.CODTIPOPER IN (3200, 3201, 3210) 
            THEN CAB.NUMNOTA
        WHEN CAB.CODTIPOPER IN (3100, 3106) 
            THEN (SELECT DISTINCT GET_NFES(VAR.NUNOTAORIG) 
                  FROM TGFVAR VAR 
                  WHERE VAR.NUNOTAORIG = CAB.NUNOTA) 
        ELSE NULL
    END AS "NUM_NF"
--this select inside the parenthesis is a sql typed per the ERP devs

But I get this error:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

What am I doing wrong?

CodePudding user response：

Given that your error is:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

Then:

• CAB.NUMNOTA is a NUMBER data type and
• GET_NFES(VAR.NUNOTAORIG) returns a string data type.

You need to convert them to be the same data type by either using TO_CHAR(CAB.NUMNOTA) or TO_NUMBER(GET_NFES(VAR.NUNOTAORIG)).

So either:

SELECT CASE
       WHEN CAB.CODTIPOPER IN (3200, 3201, 3210) 
       THEN TO_CHAR(CAB.NUMNOTA)
       WHEN CAB.CODTIPOPER IN (3100, 3106) 
       THEN (SELECT DISTINCT
                    GET_NFES(VAR.NUNOTAORIG) 
             FROM   TGFVAR VAR 
             WHERE  VAR.NUNOTAORIG = CAB.NUNOTA) 
       ELSE NULL
       END AS "NUM_NF"

or:

SELECT CASE
       WHEN CAB.CODTIPOPER IN (3200, 3201, 3210) 
       THEN CAB.NUMNOTA
       WHEN CAB.CODTIPOPER IN (3100, 3106) 
       THEN (SELECT DISTINCT
                    TO_NUMBER(GET_NFES(VAR.NUNOTAORIG))
             FROM   TGFVAR VAR 
             WHERE  VAR.NUNOTAORIG = CAB.NUNOTA) 
       ELSE NULL
       END AS "NUM_NF"

(Note: if GET_NFES(VAR.NUNOTAORIG) does not return a string containing a number then trying to convert it to a number will fail so its more likely that the you want the first option over the second.)

(Note 2: you may then get further errors if the sub-query returns more than one row.)

CodePudding user response：

The cause is certainly that the GET_NFES() is returning VARCHAR result while the NUMNOTA is a NUMBER. You can fix the types with a TO_NUMBER(), however this SELECT DISTINCT ... just triggers me since places where you can get more than one row as a result in a CASE just looks wrong.