CODEWITHSUNDEEP
sqlsql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

How to apply check on '0'?

I am creating a query where I am supposed to add column values and values is in one column at a time. Now I am using ISNULL(Col1,Col2) to check if any column contains value. Now suddenly there is some sort of change in the code and now instead of going NULL in the database column it is saving 0 so ISNULL is not working on those columns and picking up the Col1 which is in the first place I suppose.

Is there anyway to handle this without rewriting the entire query or digging up more ?

Here is my query if anyone wants to look at.

;WITH CTE AS (
SELECT 
ID, SUM(DrAmount) [DrAmount], SUM(CrAmount) [CrAmount]
FROM FICO.tbl_TransactionDetail
GROUP BY ID
)
SELECT ID, D.DrAmount, D.CrAmount, D.Amount, D.Amount-ISNULL(D.DrAmount,D.CrAmount) [Opening]
    FROM(
        SELECT *,
                SUM(ISNULL(DrAmount, 0)+ISNULL(CrAmount, 0)) OVER (ORDER BY ID 
                         ) as Amount
        FROM CTE
        )D

Upvotes: 0

Views: 79

Answers (1)

DatabaseCoder
DatabaseCoder

Reputation: 2032

As per comments, I have use ISNULL(NULLIF(col1, 0), col2) trick for this issue. Here is updated query -

;WITH CTE AS (
  SELECT 
    ID,
    SUM(DrAmount) [DrAmount],
    SUM(CrAmount) [CrAmount]
  FROM FICO.tbl_TransactionDetail
  GROUP BY ID
)
SELECT
  ID,
  D.DrAmount,
  D.CrAmount,
  D.Amount,
  D.Amount-ISNULL(D.DrAmount,D.CrAmount) [Opening]
FROM(
        SELECT
          *,
          SUM(ISNULL(NULLIF(DrAmount, 0), 0)+ISNULL(NULLIF(CrAmount, 0), 0)) OVER (ORDER BY ID) as Amount
        FROM CTE
    )D

Upvotes: 2

Related Questions