How display default values when no row is returned in sql

Question

declare @apl _from datetime,
@apl _to datetime;
set @apl _from = '1/1/2019';
set @apl _to = '12/2/2022';

;with cte as (
SELECT A.Chemical_Name,G.Disposal_Method,SUM(G.Amount * G.Conversion_Factor) TotalWaste
FROM MAT A
INNER JOIN(Select utn.materialid as materialid,D.DM as Disposal_Method,utn.AMOUNT as Amount,F.Conversion_Factor as Conversion_Factor
from AIR_T utn
INNER JOIN AIR C ON C.Equipment_Link = utn.Equipment_Link
INNER JOIN AIRUsage D ON D.Id_Field = utn.Source_Id
INNER JOIN Site E ON E.Site_Id = C.Site_Id
INNER JOIN Unit F ON F.Start_Unit = utn.Units
where utn.Date_From BETWEEN @apl _from AND @apl _to and F.End_Unit = 'tonne' and utn.MATERIALID in (67592,67544) ) AS G ON G.MaterialId = A.MaterialID
group by A.CHEMICAL_NAME,G.Disposal_Method
)

SELECT ISNULL([Landfill], 0) Landfill
,ISNULL([Incineration - Thermal Destruction], 0) Incineration
,ISNULL([Retort/Recycled/Reused], 0) Recycle
,ISNULL([Waste to Energy], 0) WasteEnergy
,Chemical_Name
FROM cte PIVOT (SUM(TotalWaste) FOR DisposalMethod IN ([Incineration - Thermal Destruction],[Retort/Recycled/Reused],[Waste to Energy])) pvt

the query works fine when the data is there. but when no data is there I want to display the chemical name with rest of the columns as 0.
That is the reason why i have input ISNULL. Is there any way to achieve it ?

Answer 1

I don't have your tables and data, so I cannot test, but what happens if you change

FROM MAT A
INNER JOIN(Select utn.materialid as materialid,D.DM as

to be

FROM MAT A
LEFT OUTER JOIN(Select utn.materialid as materialid,D.DM as

Answer 2

Can you show some output from the query currently used as source (CTE) and desired result based on this input?

Answer 3

I think it should work if the cte is Left Joined to the rest of the query. For example, if I run

Create Table MAT(Chemical_Name varchar(20), MaterialID int);
Insert MAT(Chemical_Name, MaterialID) Values('Sodium Chloride', 67592), ('Water', 67544)
Create Table AIR_T(materialid int, Equipment_Link int, Source_Id int, Units int, Date_From date, Amount int);
Insert AIR_T(materialid, Equipment_Link, Source_Id, Units, Date_From, Amount) Values(67544, 3, 4, 5, '1/1/2020', 100);
Create Table AIR(Equipment_Link int, Site_Id int);
Insert AIR(Equipment_Link, Site_Id) Values(3, 6);
Create Table AIRUSAGE(Id_Field int, DM varchar(100));
Insert AIRUSAGE(Id_Field, DM) Values(4, 'Retort/Recycled/Reused');
Create Table Site(Site_Id int);
Insert Site(Site_Id) Values(6);
Create Table Unit(Start_Unit int, End_Unit varchar(20), Conversion_Factor int);
Insert Unit(Start_Unit, End_Unit, Conversion_Factor) Values(5, 'tonne', 7);


declare @date_from datetime,
@date_to datetime;
set @date_from = '1/1/2019';
set @date_to = '12/2/2022';

;with cte as (
SELECT A.Chemical_Name,G.Disposal_Method,SUM(G.Amount * G.Conversion_Factor) TotalWaste
FROM MAT A
LEFT JOIN(Select utn.materialid as materialid,D.DM as Disposal_Method,utn.AMOUNT as Amount,F.Conversion_Factor as Conversion_Factor
from AIR_T utn
INNER JOIN AIR C ON C.Equipment_Link = utn.Equipment_Link
INNER JOIN AIRUsage D ON D.Id_Field = utn.Source_Id
INNER JOIN Site E ON E.Site_Id = C.Site_Id
INNER JOIN Unit F ON F.Start_Unit = utn.Units
where utn.Date_From BETWEEN @date_from AND @date_to and F.End_Unit = 'tonne' and utn.MATERIALID in (67592,67544) ) AS G ON G.MaterialId = A.MaterialID
group by A.CHEMICAL_NAME,G.Disposal_Method
)

SELECT ISNULL([Landfill], 0) Landfill
,ISNULL([Incineration - Thermal Destruction], 0) Incineration
,ISNULL([Retort/Recycled/Reused], 0) Recycle
,ISNULL([Waste to Energy], 0) WasteEnergy
,Chemical_Name
FROM cte PIVOT (SUM(TotalWaste) FOR Disposal_Method IN ([Landfill], [Incineration - Thermal Destruction],[Retort/Recycled/Reused],[Waste to Energy])) pvt

The result I get is

Landfill Incineration Recycle WasteEnergy Chemical_Name

---

0 0 0 0 Sodium Chloride
0 0 700 0 Water

If that doesn't solve your problem, the best way for us to get you an answer is for you to do what I did above. Give us the SQL to create sample tables and insert sample data. Then show us the result you want from that sample data.

Tom

Answer 4

Try:
declare @apl _from datetime,
@apl _to datetime;
set @apl _from = '1/1/2019';
set @apl _to = '12/2/2022';

;with cte as (  
SELECT A.Chemical_Name,G.Disposal_Method, ISNULL(G.TotalWaste,0) as TotalWaste  
FROM MAT A  
LEFT JOIN(Select utn.materialid as materialid,D.DM as Disposal_Method, SUM(utn.AMOUNT as Amount * F.Conversion_Factor as Conversion_Factor) as TotalWaste  
from AIR_T utn  
INNER JOIN AIR C ON C.Equipment_Link = utn.Equipment_Link  
INNER JOIN AIRUsage D ON D.Id_Field = utn.Source_Id  
INNER JOIN Site E ON E.Site_Id = C.Site_Id  
INNER JOIN Unit F ON F.Start_Unit = utn.Units  
where utn.Date_From BETWEEN @date_from AND @date_to and F.End_Unit = 'tonne' and utn.MATERIALID in (67592,67544)   
group by a.material_id, D.DM  
  
) AS G ON G.MaterialId = A.MaterialID   
  
)  
  
SELECT ISNULL([Landfill], 0) Landfill  
,ISNULL([Incineration - Thermal Destruction], 0) Incineration  
,ISNULL([Retort/Recycled/Reused], 0) Recycle  
,ISNULL([Waste to Energy], 0) WasteEnergy  
,Chemical_Name  
FROM cte PIVOT (SUM(TotalWaste) FOR Disposal_Method IN (  
[Landfill],  
[Incineration - Thermal Destruction],[Retort/Recycled/Reused],[Waste to Energy])) pvt  
  
  

Answer 5

Hi anonymous user
First of all, you need to ensure that the select statement in the CTE can be executed normally and get the required results.
Then remove ISNULL functions, and check whether "NULL" actually appears in the final output,pay attention to distinguish between NULL and blank.
If it is blank, the default value cannot be displayed even if ISNULL is used.

Maybe you can have a try on CASE WHEN sentence,like this:

;WITH CTE AS  
(  
 SELECT ......  
)  
SELECT CASE WHEN [Landfill] IS NULL OR LEN([Landfill])=0 THEN 0 ELSE [Landfill] END AS Landfill  
      ,CASE WHEN [Incineration - Thermal Destruction] IS NULL OR LEN([Incineration - Thermal Destruction])=0 THEN 0 ELSE [Incineration - Thermal Destruction] END AS Incineration  
      ,CASE WHEN [Retort/Recycled/Reused] IS NULL OR LEN([Retort/Recycled/Reused])=0 THEN 0 ELSE [Retort/Recycled/Reused] END AS Recycle  
      ,CASE WHEN [Waste to Energy] IS NULL OR LEN([Waste to Energy])=0 THEN 0 ELSE [Waste to Energy] END AS WasteEnergy  
      ,Chemical_Name  
FROM CTE PIVOT (SUM(TotalWaste) FOR DisposalMethod IN ([Incineration - Thermal Destruction],[Retort/Recycled/Reused],[Waste to Energy])) pvt  

Best regards,
LiHong