How to return '0' for no rows (no information) in the table

Question

Post reply

How to return '0' for no rows (no information) in the table

JeremyU

SSChasing Mays

Points: 640

May 17, 2023 at 2:45 am

#4191123

Hello everyone,

I have a question. I have such a query that returns 'On Hand' result for a warehouse last year. But it returns nothing for this year because there is no inventory. I am trying to understand if there is a way to return 0 if no rows found, because I want to make a calculation SUM On Hand this year - SUM On Hand last year and therefore I need to receive 0 for no rows found.

Does someone know how it can be done?

DECLARE @DateKey1 DATE, @DateKey3 DATE

SELECT  @DateKey1 = CONVERT(CHAR,DATEADD(WEEK,-1,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE())),112),
       @DateKey3 = CONVERT(CHAR,DATEADD(WEEK,-53,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE())),112)
       

 SELECT IUWEDT AS TIME, IUWHS# AS DC, 
sum(IUONHD * ITCOST) AS 'OH' 
 FROM table1
 WHERE CONVERT(DATETIME, CONVERT(CHAR(8), IUWEDT)) IN ( @DateKey1 , @DateKey3)
AND IUWHS# ='22'
AND (ITPRDC != 'AX' OR ITPRDC != ' ')
AND ITCOST != 0
GROUP BY IUWEDT, IUWHS#
-- returns data for DC '22' last year
-- doesn't return data for DC '22' this year, should return 0 and make it a calculation 'This year' - 'Last year'

Question1

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

First of all, I have some suggestions for your existing query.

To obtain a DATE from a DATETIME, simply use CAST(x as DATE). Try to avoid using number-to-character-to-number conversions, because they are unnecessarily expensive.
!= is non-ANSI syntax. It works, but most T-SQL developers use (and expect to see) <> used instead.
If you are going to convert something to CHAR, always explicitly state the length, or you may be in for a surprise. Try this if you don't know what the issue is

SELECT Chars50 = REPLICATE ('x', 50)
      ,LenChars50 = LEN (CONVERT (CHAR, REPLICATE ('x', 50)));

As far as your question is concerned, a possible solution would be to select from a CTE instead

WITH AugmentedTable1 AS (
SELECT IUWEDT, <other cols>
FROM table1
UNION ALL
SELECT @DateKey1, <other cols with default NULL/Zero values>
UNION ALL
SELECT @DateKey3, <other cols with default NULL/Zero values>
)
SELECT <results>
FROM AugmentedTable1

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101097 More actions · Answer 2

You can adjust the SUM(s). I wasn't sure if you wanted to list only the net or all three; if you want only the net, remove the first two "OH" columns:

SELECT IUWEDT AS TIME, IUWHS# AS DC, 
    SUM(CASE WHEN CONVERT(DATE, CONVERT(CHAR(8), IUWEDT)) IN ( @DateKey1 ) 
        THEN IUONHD * ITCOST ELSE 0 END) AS "OH",
    SUM(CASE WHEN CONVERT(DATE, CONVERT(CHAR(8), IUWEDT)) IN ( @DateKey3 ) 
        THEN IUONHD * ITCOST ELSE 0 END) AS "PRIOR_OH",
    SUM(CASE WHEN CONVERT(DATE, CONVERT(CHAR(8), IUWEDT)) IN ( @DateKey1 ) 
        THEN IUONHD * ITCOST ELSE 0 END) -
    SUM(CASE WHEN CONVERT(DATE, CONVERT(CHAR(8), IUWEDT)) IN ( @DateKey3 ) 
        THEN IUONHD * ITCOST ELSE 0 END) AS "NET_OH"
FROM table1
WHERE CONVERT(DATE, CONVERT(CHAR(8), IUWEDT)) IN ( @DateKey1 , @DateKey3)
AND IUWHS# = '22'
AND (ITPRDC <> 'AX' OR ITPRDC <> ' ')
AND ITCOST <> 0
GROUP BY IUWEDT, IUWHS#

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

kaj SSCommitted Points: 1605 More actions · Answer 3

This is not an aswer to your question, but...

Your criteria AND (ITPRDC != 'AX' OR ITPRDC != ' ') looks wrong to me. And it's not your use of the non-ASCII != instead of the normal <> that makes me say so.

I just find it odd to use OR in conjunction with two != criterias on the same column, i.e. select everything, except 'AX' or select everything, except blanks. One of those criterias will always be true, which makes the line irrelevant.

Maybe you meant AND NOT (ITPRDC = 'AX' OR ITPRDC = ' '), which would make more sense?

This reply was modified 1 year, 6 months ago by kaj. Reason: added "on the same column" to avoid ambiguity

Steve Collins SSCrazy Points: 2157 More actions · Answer 4

It seems you could use COUNT in the SELECT list. COUNT always returns an INT even if the FROM clause returns no rows

declare
  @max_num        int=0,
  @row_count      int=0;

select @max_num=max(num),
       @row_count=count(*)
from (values (1),(2),(3)) v(num)
where 1=0;

select @max_num, @row_count;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

drew.allen SSC Guru Points: 76985 More actions · Answer 5

kaj wrote:

This is not an aswer to your question, but...
Your criteria AND (ITPRDC != 'AX' OR ITPRDC != ' ') looks wrong to me. And it's not your use of the non-ASCII != instead of the normal <> that makes me say so.
I just find it odd to use OR in conjunction with two != criterias on the same column, i.e. select everything, except 'AX' or select everything, except blanks. One of those criterias will always be true, which makes the line irrelevant.
Maybe you meant AND NOT (ITPRDC = 'AX' OR ITPRDC = ' '), which would make more sense?

I prefer AND ITPRDC NOT IN ('AX', ''), because it reinforces that the values are from a single field.

Also, one of those criteria will NOT always be true if ITPRDC allows NULL values. SQL uses three-value logic, and your statement only takes two into account.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

JeremyU SSChasing Mays Points: 640 More actions · Answer 6

Thanks everyone for your help. It worked for me.

Scott, your idea helped me a lot. Thanks!!