If null return 0

Question

I am trying to modify this query to return a row with '0' printed in either one or all of the rows when no data is returned. I've tried using ISNULL() but I am not getting anywhere.

SELECT DISTINCT 
SA.DELETED,
PAT.VERSION, 
PAT.PATTERNDATE,
SA.AGENT_VERSION,
SCL.COMPUTER_NAME AS Computer_Name,
SCO.OPERATION_SYSTEM AS Operation_System,
dateadd(s,convert(bigint,SA.CREATION_TIME)/1000,
'01-01-1970 00:00:00') CREATION_DTTM,
dateadd(s,convert(bigint,SA.LAST_UPDATE_TIME)/1000,
'01-01-1970  00:00:00')  Lastupdatetime,
DATEADD(s, convert(bigint,LAST_SCAN_TIME)/1000,
'01-01-1970 00:00:00')LAST_SCAN_TIME,
PAT.PATTERNDATE AS Pattern_Date,
SCL.USER_NAME AS User_Name,
VSC.IP_ADDR1_TEXT AS IP_Add,
IM.NAME AS Group_Name

FROM ((((SEM_AGENT SA

INNER JOIN SEM_CLIENT SCL
ON ((SA.COMPUTER_ID=SCL.COMPUTER_ID) 
AND (SA.DOMAIN_ID=SCL.DOMAIN_ID)) 
AND (SA.GROUP_ID=SCL.GROUP_ID)) 

INNER JOIN SEM_COMPUTER   SCO
ON ((SA.COMPUTER_ID=SCO.COMPUTER_ID) 
AND (SA.DOMAIN_ID=SCO.DOMAIN_ID)) 
AND (SA.DELETED=SCO.DELETED))

INNER JOIN PATTERN PAT 
ON SA.PATTERN_IDX=PAT.PATTERN_IDX) 
INNER JOIN IDENTITY_MAP IM 
ON SCL.GROUP_ID=IM.ID) 

INNER JOIN V_SEM_COMPUTER VSC 
ON SCO.COMPUTER_ID=VSC.COMPUTER_ID 
AND SA.DELETED=0

WHERE PAT.Patterndate < (SELECT MAX(Patterndate) -2 FROM Pattern)
AND SCO.OPERATION_SYSTEM NOT LIKE '%2000%'
ORDER BY Computer_Name

Could you provide a slightly smaller example which has the same functionality as your example? — Misch, Jul 13 '12 at 18:24
Do you know about [`COALESCE`](http://msdn.microsoft.com/en-us/library/ms190349.aspx)? Perhaps this could help. — deefour, Jul 13 '12 at 18:26
This is SQL Server. This query was pre-generated by the software, I know its hard to read — Ben, Jul 13 '12 at 18:26
Can you post your implementation of ISNULL(), I don't see why that wouldn't work — Kevin DiTraglia, Jul 13 '12 at 18:27
If I understand you question correctly, , if NO rows are returned, you are looking to a row with 0's? — Holger Brandt, Jul 13 '12 at 18:28
Yes, I know about COALESCE. This is just difficult query to work with. @HolgerBrandt yes, that is what I am trying to do — Ben, Jul 13 '12 at 18:29
This query may have been "pre-generated by the software", but you could take the time to clean it up before posting it here to make your question more readable; it would improve your chances of getting an answer. — Ken White, Jul 13 '12 at 19:15
@KenWhite - I went back and cleaned up the query the best I could. Let me know if this helps. I am still unable to find a solution. Thank you. — Ben, Jul 16 '12 at 14:55

Conrad Frix · Answer 1 · 2012-07-13T19:00:54.373

3

One approach to returning a NULL row when no records is to

Put your SQL in a CTE
SELECT FROM your CTE with
A Union of Default Values With an NOT EXISTS against your CTE.

WITH CTE AS (
   SELECT "Computer Name".  ... 
)
SELECT * FROM CTE
UNION ALL
SELECT '', 0, 0, '' 
WHERE 
    NOT EXISTS(SELECT * FROM CTE)
ORDER BY 
     "Computer Name"

See DEMO returns "null" record and DEMO returns data.
The difference between the two demos is Where 1=2 and Where 1=1 in the CTE

edited Jul 13 '12 at 19:00

answered Jul 13 '12 at 18:40

Conrad Frix

51,984
12
96
155

I tried this and received "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified." – Ben Jul 13 '12 at 18:52
1

@Ben You just need to move the order from the CTE to after the UNION. See update – Conrad Frix Jul 13 '12 at 19:01
Thanks, I tried this update and am still receiving the same error. – Ben Jul 13 '12 at 19:05
I'm going to guess you added it to the UNION, but forgot to Remove it from the cte – Conrad Frix Jul 13 '12 at 19:06
I have it like this: with cte as (SELECT ) SELECT * FROM CTE UNION ALL SELECT '', 0, 0, '' WHERE NOT EXISTS(SELECT * FROM CTE) ORDER BY "Computer Name" – Ben Jul 13 '12 at 19:11
@Ben can you create a SQLfiddle for it. Creating the DDL for 6 tables without being able to right click on the table and then selecting script table as -> Create to -> new query window... is a bit of a chore – Conrad Frix Jul 13 '12 at 19:18

Erica · Answer 2 · 2012-07-13T18:42:49.193

I would check if there are any rows with your conditions, and if not, select 0, otherwise, select your whole query:

SELECT TOP 1 *
FROM (((("SEM_AGENT" "SEM_AGENT" INNER JOIN "SEM_CLIENT" "SEM_CLIENT"  
  ON (("SEM_AGENT"."COMPUTER_ID"="SEM_CLIENT"."COMPUTER_ID")  
  AND ("SEM_AGENT"."DOMAIN_ID"="SEM_CLIENT"."DOMAIN_ID"))  
  AND ("SEM_AGENT"."GROUP_ID"="SEM_CLIENT"."GROUP_ID")) INNER JOIN "SEM_COMPUTER"   "SEM_COMPUTER"  
  ON (("SEM_AGENT"."COMPUTER_ID"="SEM_COMPUTER"."COMPUTER_ID")  
  AND ("SEM_AGENT"."DOMAIN_ID"="SEM_COMPUTER"."DOMAIN_ID"))  
  AND ("SEM_AGENT"."DELETED"="SEM_COMPUTER"."DELETED")) INNER JOIN "PATTERN" "PATTERN"  
  ON "SEM_AGENT"."PATTERN_IDX"="PATTERN"."PATTERN_IDX") INNER JOIN "IDENTITY_MAP" "IDENTITY_MAP"  
  ON "SEM_CLIENT"."GROUP_ID"="IDENTITY_MAP"."ID") INNER JOIN "V_SEM_COMPUTER" "V_SEM_COMPUTER"  
  ON "SEM_COMPUTER"."COMPUTER_ID"="V_SEM_COMPUTER"."COMPUTER_ID"  
  AND "SEM_AGENT"."DELETED"=0 
WHERE Pattern.Patterndate < (select max(Patterndate) -2 from Pattern) 
AND SEM_COMPUTER.OPERATION_SYSTEM NOT LIKE '%2000%' 

IF @@ROWCOUNT = 0
    SELECT 0
ELSE 
BEGIN
    SELECT DISTINCT "SEM_AGENT"."DELETED" 
      ,"PATTERN"."VERSION"  
      ,"PATTERN"."PATTERNDATE" 
      ,"SEM_AGENT"."AGENT_VERSION" 
      ,"SEM_CLIENT"."COMPUTER_NAME" "Computer Name" 
      , "SEM_COMPUTER"."OPERATION_SYSTEM" "Operation System" 
      ,dateadd(s,convert(bigint,"SEM_AGENT"."CREATION_TIME")/1000,'01-01-1970 00:00:00')     
        CREATION_DTTM 
      , dateadd(s,convert(bigint,"SEM_AGENT"."LAST_UPDATE_TIME")/1000,'01-01-1970  00:00:00')  Lastupdatetime 
      , DATEADD(s, convert(bigint,LAST_SCAN_TIME)/1000, '01-01-1970 00:00:00')"Last Scan Time" 
      , "PATTERN"."PATTERNDATE" "Pattern Date" 
      , "SEM_CLIENT"."USER_NAME" "User Name" 
      , "V_SEM_COMPUTER"."IP_ADDR1_TEXT" "IP Address" 
      , "IDENTITY_MAP"."NAME" "Group Name" 
    FROM (((("SEM_AGENT" "SEM_AGENT" INNER JOIN "SEM_CLIENT" "SEM_CLIENT"  
      ON (("SEM_AGENT"."COMPUTER_ID"="SEM_CLIENT"."COMPUTER_ID")  
      AND ("SEM_AGENT"."DOMAIN_ID"="SEM_CLIENT"."DOMAIN_ID"))  
      AND ("SEM_AGENT"."GROUP_ID"="SEM_CLIENT"."GROUP_ID")) INNER JOIN "SEM_COMPUTER"   "SEM_COMPUTER"  
      ON (("SEM_AGENT"."COMPUTER_ID"="SEM_COMPUTER"."COMPUTER_ID")  
      AND ("SEM_AGENT"."DOMAIN_ID"="SEM_COMPUTER"."DOMAIN_ID"))  
      AND ("SEM_AGENT"."DELETED"="SEM_COMPUTER"."DELETED")) INNER JOIN "PATTERN" "PATTERN"  
      ON "SEM_AGENT"."PATTERN_IDX"="PATTERN"."PATTERN_IDX") INNER JOIN "IDENTITY_MAP" "IDENTITY_MAP"  
      ON "SEM_CLIENT"."GROUP_ID"="IDENTITY_MAP"."ID") INNER JOIN "V_SEM_COMPUTER" "V_SEM_COMPUTER"  
      ON "SEM_COMPUTER"."COMPUTER_ID"="V_SEM_COMPUTER"."COMPUTER_ID"  
      AND "SEM_AGENT"."DELETED"=0 
    WHERE Pattern.Patterndate < (select max(Patterndate) -2 from Pattern) 
    AND SEM_COMPUTER.OPERATION_SYSTEM NOT LIKE '%2000%' 
    ORDER BY "Computer Name" 
END

If you know your inner joins will not change the number of results, then you can remove them from the first query for efficiency's sake.

If you didn't want to use @@ROWCOUNT, you could also use COUNT():

DECLARE @count INT

SELECT @count = COUNT(*)
FROM (
SELECT TOP 1 *
FROM (((("SEM_AGENT" "SEM_AGENT" INNER JOIN "SEM_CLIENT" "SEM_CLIENT"  
  ON (("SEM_AGENT"."COMPUTER_ID"="SEM_CLIENT"."COMPUTER_ID")  
  AND ("SEM_AGENT"."DOMAIN_ID"="SEM_CLIENT"."DOMAIN_ID"))  
  AND ("SEM_AGENT"."GROUP_ID"="SEM_CLIENT"."GROUP_ID")) INNER JOIN "SEM_COMPUTER"   "SEM_COMPUTER"  
  ON (("SEM_AGENT"."COMPUTER_ID"="SEM_COMPUTER"."COMPUTER_ID")  
  AND ("SEM_AGENT"."DOMAIN_ID"="SEM_COMPUTER"."DOMAIN_ID"))  
  AND ("SEM_AGENT"."DELETED"="SEM_COMPUTER"."DELETED")) INNER JOIN "PATTERN" "PATTERN"  
  ON "SEM_AGENT"."PATTERN_IDX"="PATTERN"."PATTERN_IDX") INNER JOIN "IDENTITY_MAP" "IDENTITY_MAP"  
  ON "SEM_CLIENT"."GROUP_ID"="IDENTITY_MAP"."ID") INNER JOIN "V_SEM_COMPUTER" "V_SEM_COMPUTER"  
  ON "SEM_COMPUTER"."COMPUTER_ID"="V_SEM_COMPUTER"."COMPUTER_ID"  
  AND "SEM_AGENT"."DELETED"=0 
WHERE Pattern.Patterndate < (select max(Patterndate) -2 from Pattern) 
AND SEM_COMPUTER.OPERATION_SYSTEM NOT LIKE '%2000%' ) temp

IF @count = 0
SELECT 0
ELSE 
BEGIN
    SELECT DISTINCT "SEM_AGENT"."DELETED" 
      ,"PATTERN"."VERSION"  
      ,"PATTERN"."PATTERNDATE" 
      ,"SEM_AGENT"."AGENT_VERSION" 
      ,"SEM_CLIENT"."COMPUTER_NAME" "Computer Name" 
      , "SEM_COMPUTER"."OPERATION_SYSTEM" "Operation System" 
      ,dateadd(s,convert(bigint,"SEM_AGENT"."CREATION_TIME")/1000,'01-01-1970 00:00:00')     
        CREATION_DTTM 
      , dateadd(s,convert(bigint,"SEM_AGENT"."LAST_UPDATE_TIME")/1000,'01-01-1970  00:00:00')  Lastupdatetime 
      , DATEADD(s, convert(bigint,LAST_SCAN_TIME)/1000, '01-01-1970 00:00:00')"Last Scan Time" 
      , "PATTERN"."PATTERNDATE" "Pattern Date" 
      , "SEM_CLIENT"."USER_NAME" "User Name" 
      , "V_SEM_COMPUTER"."IP_ADDR1_TEXT" "IP Address" 
      , "IDENTITY_MAP"."NAME" "Group Name" 
    FROM (((("SEM_AGENT" "SEM_AGENT" INNER JOIN "SEM_CLIENT" "SEM_CLIENT"  
      ON (("SEM_AGENT"."COMPUTER_ID"="SEM_CLIENT"."COMPUTER_ID")  
      AND ("SEM_AGENT"."DOMAIN_ID"="SEM_CLIENT"."DOMAIN_ID"))  
      AND ("SEM_AGENT"."GROUP_ID"="SEM_CLIENT"."GROUP_ID")) INNER JOIN "SEM_COMPUTER"   "SEM_COMPUTER"  
      ON (("SEM_AGENT"."COMPUTER_ID"="SEM_COMPUTER"."COMPUTER_ID")  
      AND ("SEM_AGENT"."DOMAIN_ID"="SEM_COMPUTER"."DOMAIN_ID"))  
      AND ("SEM_AGENT"."DELETED"="SEM_COMPUTER"."DELETED")) INNER JOIN "PATTERN" "PATTERN"  
      ON "SEM_AGENT"."PATTERN_IDX"="PATTERN"."PATTERN_IDX") INNER JOIN "IDENTITY_MAP" "IDENTITY_MAP"  
      ON "SEM_CLIENT"."GROUP_ID"="IDENTITY_MAP"."ID") INNER JOIN "V_SEM_COMPUTER" "V_SEM_COMPUTER"  
      ON "SEM_COMPUTER"."COMPUTER_ID"="V_SEM_COMPUTER"."COMPUTER_ID"  
      AND "SEM_AGENT"."DELETED"=0 
    WHERE Pattern.Patterndate < (select max(Patterndate) -2 from Pattern) 
    AND SEM_COMPUTER.OPERATION_SYSTEM NOT LIKE '%2000%' 
    ORDER BY "Computer Name" 
END

This returned "The column 'DOMAIN_ID' was specified multiple times for 'temp'." — Ben, Jul 13 '12 at 18:53
That's because I said select * and your tables of columns with the same names - just changed the asterisk to a specific column or specifically list all of your columns with their table identifiers. — Erica, Jul 13 '12 at 19:15

score 0 · Answer 3 · answered Jul 13 '12 at 18:39

You have a query that returns 0 or more rows. When it returns 0 rows, you want to include another row.

This adds computational complexity onto the query. One way to do it is to union together the row you want, along with a flag that indicates where the row comes from. Then, use a windows function to share that information among all rows, and use a where clause to filter the final rows:

select *
from (select t.*, sum(FromQ) over (partition by NULL) as numQ
      from (select *, 1 as FromQ
            from <query>
            union all
            select 0,0,0,0 . . . , 0 as FromQ
           ) t
      ) t
where numQ = 0 or numQ > 0 and FromQ <> 0

You might also just union the groups together, order by FromQ in reverse, and ignore the row in the application if it is not first.

Holger Brandt · Answer 4 · 2012-07-13T19:13:24.790

I broke it into three statements:

DECLARE @RecordCount int;

WITH Temp_CTE
AS
(
SELECT DISTINCT EM_AGENT.DELETED
  ,PATTERN.VERSION 
  ,PATTERN.PATTERNDATE
  ,SEM_AGENT.AGENT_VERSION
  ,SEM_CLIENT.COMPUTER_NAME "Computer Name"
  ,SEM_COMPUTER.OPERATION_SYSTEM "Operation System"
  ,dateadd(s,convert(bigint,SEM_AGENT.CREATION_TIME)/1000
    ,'01-01-1970 00:00:00') CREATION_DTTM
  , dateadd(s,convert(bigint,SEM_AGENT.LAST_UPDATE_TIME)/1000
    ,'01-01-1970  00:00:00') Lastupdatetime
  , DATEADD(s, convert(bigint,LAST_SCAN_TIME)/1000
    , '01-01-1970 00:00:00') "Last Scan Time"
  , PATTERN.PATTERNDATE "Pattern Date"
  , SEM_CLIENT.USER_NAME "User Name"
  , V_SEM_COMPUTER.IP_ADDR1_TEXT "IP Address"
  , IDENTITY_MAP.NAME "Group Name"
FROM (((("SEM_AGENT" "SEM_AGENT" 
INNER JOIN "SEM_CLIENT" "SEM_CLIENT" 
  ON (("SEM_AGENT"."COMPUTER_ID"="SEM_CLIENT"."COMPUTER_ID") 
  AND ("SEM_AGENT"."DOMAIN_ID"="SEM_CLIENT"."DOMAIN_ID")) 
  AND ("SEM_AGENT"."GROUP_ID"="SEM_CLIENT"."GROUP_ID")) 
INNER JOIN "SEM_COMPUTER"   "SEM_COMPUTER" 
  ON (("SEM_AGENT"."COMPUTER_ID"="SEM_COMPUTER"."COMPUTER_ID") 
  AND ("SEM_AGENT"."DOMAIN_ID"="SEM_COMPUTER"."DOMAIN_ID")) 
  AND ("SEM_AGENT"."DELETED"="SEM_COMPUTER"."DELETED"))
INNER JOIN "PATTERN" "PATTERN" 
  ON "SEM_AGENT"."PATTERN_IDX"="PATTERN"."PATTERN_IDX") 
INNER JOIN "IDENTITY_MAP" "IDENTITY_MAP" 
  ON "SEM_CLIENT"."GROUP_ID"="IDENTITY_MAP"."ID") 
INNER JOIN "V_SEM_COMPUTER" "V_SEM_COMPUTER" 
  ON "SEM_COMPUTER"."COMPUTER_ID"="V_SEM_COMPUTER"."COMPUTER_ID" 
  AND "SEM_AGENT"."DELETED"=0
WHERE Pattern.Patterndate < (select max(Patterndate) -2 from Pattern)
  AND SEM_COMPUTER.OPERATION_SYSTEM NOT LIKE '%2000%'
)
SELECT @RecordCount = COUNT(*) FROM Temp_CTE;

IF @RecordCount=0 THEN
  SELECT 0
ELSE
  SELECT * FROM Temp_CTE ORDER BY [Computer Name];

This one also returned "The column 'DOMAIN_ID' was specified multiple times for 'temp'." — Ben, Jul 13 '12 at 19:01
@Ben Hmmm. I don't DOMANIN_ID being returned. However, I did see that I missed a comma in the `WITH Temp_CTE` field selection. I amended my answer to fix that. — Holger Brandt, Jul 13 '12 at 19:07

If null return 0

4 Answers4