How to replace 0 with N/A

问题:

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;

回答1:

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;

回答2:

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,

回答3:

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

回答4:

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')