Replace nulls values in sql using select statement?-change NULL to 'No commission'

Question

Here is a screenshot

I need to remove the Null in the commission column and change it to 'No commission'. Not sure how to write that code. I tried to use UPDATE or SET code but got errors.

This is the code I got so far.

SELECT First_Name || ' ' || Last_Name AS "Full Name", ' NZD' || ' ' || SALARY AS "Salary", 
COMMISSION_PCT AS "COMMISSION_%",SALARY * COMMISSION_PCT AS "Commission"
FROM EMPLOYEES
ORDER BY LAST_NAME desc;

Possible duplicate of Replacing NULL with 0 in a SQL server query — Harsha W
– Harsha W, Commented May 9, 2017 at 3:53
I saw that post and try to do it using that code but keep getting error. — H.Cho
– H.Cho, Commented May 9, 2017 at 3:55

asmgx · Accepted Answer · 2017-05-09 04:05:16Z

1

Try using coalesce() or NVL() for oracle instead of ISNULL()

SELECT ISNULL(COMMISSION_PCT, 'No commission')
FROM EMPLOYEES

edited May 9, 2017 at 4:05

answered May 9, 2017 at 3:54

asmgx

8,15019 gold badges102 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

H.Cho Over a year ago

I've changed my code like SELECT ISNULL (COMMISSION_PCT, "No commission"), First_Name || ' ' || Last_Name AS "Full Name", ' NZD' || ' ' || SALARY AS "Salary", COMMISSION_PCT AS "COMMISSION_%",SALARY * COMMISSION_PCT AS "Commission" FROM EMPLOYEES ORDER BY LAST_NAME desc; but I am still getting error.

H.Cho Over a year ago

ORA-00904: "ISNULL": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 18 Column: 9

asmgx Over a year ago

try coalesce() or NVL() instead of isnull

user743382 Over a year ago

@H.Cho Not the cause of this specific error message, but please note " and ' are not interchangeable.

user330315 · Accepted Answer · 2017-05-09 05:54:46Z

1

All values in a single column of a result must be of the same data type. If you want to show "No commission" instead, that means you also need to convert the number into a string. In Oracle this is done using the to_char() function.

SELECT First_Name || ' ' || Last_Name AS "Full Name", 
       ' NZD' || ' ' || SALARY AS "Salary", 
       COMMISSION_PCT AS "COMMISSION_%",
       nvl(to_char(salary * commission_pct, 'FM9999.99'), 'No Commission') AS "Commission"
FROM EMPLOYEES
ORDER BY LAST_NAME desc;

answered May 9, 2017 at 5:54

user330315

Comments

D.L. · Accepted Answer · 2017-05-09 05:58:25Z

1

For Oracle use:

NVL(pct_commission, 'No commission');

edited May 9, 2017 at 5:58

answered May 9, 2017 at 4:03

D.L.

3241 silver badge7 bronze badges

3 Comments

H.Cho Over a year ago

how do we use that code? Do I just add on to SELECT section? SELECT First_Name || ' ' || Last_Name AS "Full Name", ' NZD' || ' ' || SALARY AS "Salary", COMMISSION_PCT AS "COMMISSION_%",SALARY * COMMISSION_PCT AS "Commission", NVL(pct_commission, 'No commission') FROM EMPLOYEES ORDER BY LAST_NAME desc; like this?

D.L. Over a year ago

Try this: SELECT First_Name || ' ' || Last_Name AS "Full Name", ' NZD' || ' ' || SALARY AS "Salary", NVL(COMMISSION_PCT, 'No commission') AS "COMMISSION_%",SALARY * NVL(COMMISSION_PCT, 0) AS "Commission" FROM EMPLOYEES ORDER BY LAST_NAME desc;

D.L. Over a year ago

Well, yeah. Ideally, you would replace null with 0 instead of "No commision" so the types would match up. Will probably need to use to_char() to convert everything to a string: SELECT First_Name || ' ' || Last_Name AS "Full Name", ' NZD' || ' ' || SALARY AS "Salary", COMMISSION_PCT, AS "COMMISSION_%",SALARY * NVL(TO_CHAR(COMMISSION_PCT), 0) AS "Commission" FROM EMPLOYEES ORDER BY LAST_NAME desc;

Collectives™ on Stack Overflow

Replace nulls values in sql using select statement?-change NULL to 'No commission'

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related