How to check sql database table field null or 0 and if so replace it with other column

Question

I have a SQL query; I want to check two columns in query to check if they are null or 0; if so, then I want to replace then with other column's values.

Here is my query: I have used coalesce to check if it is null but how can I check for 0 and also how can I replace the columns with other values?

SELECT 
    t.Name
    ,t.Code
    ,c.Description
    ,st.APriceOld
    ,st.CPriceOld
    ,st.APriceNew
    ,st.CPriceNew
    COALESCE(st.APriceOld,st.APriceNew),
    COALESCE(st.CPriceOld,st.CPriceNew) 
FROM
    Table t
INNER JOIN
    STCP st ON st.PriceId = t.PriceId

Can anybody help me to get the expected result?

So if old price values are 0 or null, then they should be replaced with new price values

score 2 · Answer 1 · answered May 07 '20 at 06:56

You could try:

SELECT
    t.Name,
    t.Code,
    c.Description,
    st.APriceOld,
    st.CPriceOld,
    st.APriceNew,
    st.CPriceNew,
    CASE WHEN COALESCE(st.APriceOld, 0) <> 0
         THEN st.APriceOld ELSE st.APriceNew END AS APrice,
    CASE WHEN COALESCE(st.CPriceOld, 0) <> 0
         THEN st.CPriceOld ELSE st.CPriceNew END AS CPrice
FROM Table t
INNER JOIN STCP st ON st.PriceId = t.PriceId;

The logic here is to use COALESCE to first conditionally replace a NULL old price value with zero. Then, we use a CASE expression to replace zero values (either naturally being zero, or having been coaleseced to zero) with the backup value.

also if based on cases i have to add values of APriceOld + CPriceOld or APriceOld + CPriceNew — varun, May 07 '20 at 07:28

score 2 · Accepted Answer · answered May 07 '20 at 07:23

2

One more approach - use nullif() function:

SELECT 
    t.Name
    ,t.Code
    ,c.Description
    ,st.APriceOld
    ,st.CPriceOld
    ,st.APriceNew
    ,st.CPriceNew
    COALESCE(NULLIF(st.APriceOld, 0), st.APriceNew),
    COALESCE(NULLIF(st.CPriceOld, 0), st.CPriceNew) 
FROM
    Table t
INNER JOIN
    STCP st ON st.PriceId = t.PriceId

answered May 07 '20 at 07:23

Arvo

10,349
1
31
34

what if i want to add the prices i am getting and show it as another column – varun May 07 '20 at 08:02
Just add them? Either you duplicate formulas or write outer query, which adds calculated columns (`select ..., AP+CP as P from (select ... coalesce(...) as AP, ...) as x`). – Arvo May 07 '20 at 08:33

How to check sql database table field null or 0 and if so replace it with other column

2 Answers2