How to replace 0 with N/A

Question

In my case statement, I'm using 0 if the value is NULL. Now I want to replace 0 with N/A. I've tried using ISNULL and COALESCE but its not working.

Below is my code.

SELECT BOM.STYLE_ID, BOM.SEASON_ID, SUM(CASE WHEN BOM.Ad_compo_desc LIKE UPPER('%emb%') THEN 1 ELSE ISNULL(0, 'N/A') END) AS EMBRO, SUM(CASE WHEN BOM.Ad_compo_desc LIKE UPPER('%print%') THEN 1 ELSE ISNULL(0, 'N/A') END) AS PRINTING FROM IPLEXSTY_AD_BOM_DTL BOM WHERE CONTENT_CLASS = 'ART' GROUP BY BOM.STYLE_ID, BOM.SEASON_ID;

Also please don't use ALL CAPS. It reads like you're shouting, which doesn't make us want to help you any more than if you just type the title normally. — grooveplex
– grooveplex, Commented Feb 1, 2019 at 1:32
Sorry, i 'm just used to it, but I am not shouting :) Anyway, there's no error thrown but the output does not display N/A, still 0. — Gie
– Gie, Commented Feb 1, 2019 at 1:44
ISNULL(0, 'N/A') will always return 0 because 0 is never null? Do you perhaps mean isnull(BOM.Ad_compo_desc,'n/a')? — Dale K
– Dale K, Commented Feb 1, 2019 at 1:55
Also you are using a sum statement around your case statement - you can't sum 'n/a' - so that is going to break even if you get the isnull working. — Dale K
– Dale K, Commented Feb 1, 2019 at 1:56

manu vijay · Accepted Answer · 2019-02-09 07:53:46Z

Please try this, include 1 in quotes('1') which will convert 1 to string then 'N/A' will also load so the column acts as string type, with out converting 1 it will act as an integer type which gives error since you are trying to load 1(integer) and 'N/A'(string) in same column.

SELECT BOM.STYLE_ID, BOM.SEASON_ID, SUM(CASE WHEN BOM.Ad_compo_desc LIKE UPPER('%emb%') THEN '1' ELSE 'N/A' END) AS EMBRO, SUM(CASE WHEN BOM.Ad_compo_desc LIKE UPPER('%print%') THEN '1' ELSE 'N/A' END) AS PRINTING FROM IPLEXSTY_AD_BOM_DTL BOM WHERE CONTENT_CLASS = 'ART' GROUP BY BOM.STYLE_ID, BOM.SEASON_ID;

Gordon Linoff · Accepted Answer · 2019-02-01 01:42:27Z

In SQL, it is better to use NULL rather than 'N/A'. You would simply do this by removing the else clause:

 SUM(CASE WHEN BOM.Ad_compo_desc LIKE UPPER('%emb%') THEN 1 END) AS EMBRO,

If you really need 'N/A', then you need to deal with the fact that the expression must return a string rather than a number. That requires conversion:

 COALESCE(CONVERT(VARCHAR(255), SUM(CASE WHEN BOM.Ad_compo_desc LIKE UPPER('%emb%') THEN 1 END) ), 'N/A' ) AS EMBRO,

It throws an error "Incorrect syntax near the keyword 'AS'."

dwir182 · Accepted Answer · 2019-02-08 03:55:07Z

You can Use it with Replace Function. So it will be

SELECT BOM.STYLE_ID, BOM.SEASON_ID, SUM(CASE WHEN BOM.Ad_compo_desc LIKE UPPER('%emb%') THEN 1 ELSE Replace(Ad_compo_desc,'0','N/A') END) AS EMBRO, SUM(CASE WHEN BOM.Ad_compo_desc LIKE UPPER('%print%') THEN 1 ELSE Replace(Ad_compo_desc,'0','N/A') END) AS PRINTING FROM IPLEXSTY_AD_BOM_DTL BOM WHERE CONTENT_CLASS = 'ART' GROUP BY BOM.STYLE_ID, BOM.SEASON_ID;

Here's a link

karel · Accepted Answer · 2019-02-08 04:26:14Z

ISNULL and COALESCE works if the first parameter passed is NULL so it will return the second parameter, but if the first parameter is NOT NULL then it will never return the second parameter which is 'N/A' in your case.

Declare @firstparam int set @firstparam =NULL select ISNULL(@firstparam,'N/A')

Collectives™ on Stack Overflow

How to replace 0 with N/A

4 Answers 4

Comments

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Related