Invalid identifier while trying to multiply-CodePudding

SELECT 
    CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME) AS "Fn and Ln",
    JOB_ID AS "Job_title",
    CASE COMMISSION_PCT 
        WHEN NULL THEN SALARY
        WHEN '-' THEN SALARY
        ELSE (COMISSION_PCT * SALARY) * 12
    END AS "Year income"
FROM HR.EMPLOYEES

I have to find year income of employee considering premium. If there is no premium i have to just ignore it. In my code i get 'ORA-00904: "COMISSION_PCT": invalid identifier' i dont know what is the reason. Dtype of COMISSION_PCT NUMBER(2,2) and SALARY dtype is NUMBER(8,2).

Clarification: Specify the amount of annual income of each employee, given that the bonus is indicated as a percentage

CodePudding user response：

Your syntax is a bit off.

Try this as your CASE statement:

CASE 
  WHEN COMMISSION_PCT IS NULL THEN SALARY 
  WHEN COMMISSION_PCT = '-' THEN SALARY
  ELSE (COMMISSION_PCT * SALARY) 
END * 12 AS "Year income"

CodePudding user response：

The immediate problem is that you have a typo; as the error says you refer to COMISSION_PCT, when the column is called COMMISSION_PCT with two Ms.

        ELSE (COMMISSION_PCT * SALARY) * 12

Then, your case expression is comparing with string literal '-', but as the column is numeric no rows can have that value, so you get "ORA-00932: inconsistent datatypes: expected NUMBER got CHAR". It appears your client is displaying null values as dashes. So you don't need to handle those.

    CASE COMMISSION_PCT 
        WHEN NULL THEN SALARY
        ELSE (COMMISSION_PCT * SALARY) * 12
    END AS "Year income"

Although that form of case expression is valid - there are two forms - in this situation you need a searched expression (as @Vvdl said!) because a simple one can't compare with null (as nothing is equal to, or not equal to, null):

    CASE
        WHEN COMMISSION_PCT IS NULL THEN SALARY
        ELSE (COMMISSION_PCT * SALARY) * 12
    END AS "Year income"

Though even that might not get the result you want. If salary is monthly then you need to multiply by 12 in both cases, or at the end:

    CASE
        WHEN COMMISSION_PCT IS NULL THEN SALARY
        ELSE (COMMISSION_PCT * SALARY)
    END * 12 AS "Year income"

fiddle