从报表中隐藏空值

Question

使用此SP构建报告。如果报告没有要显示的数据，我们应该只显示tbl_CompanyProfile，但是报告将显示第二次选择的所有空字段。我关心的是:如何隐藏第二次select中的空值，而只显示tbl_CompanyProfile字段？

SELECT Company_Name, Company_Address, Company_ContactNo, Company_Email, 
Company_Website, 'NULL' AS NAME, 'NULL' AS DATE, 0 AS AMOUNT, 
'NULL' AS DEPARTMENT, 0 AS Age, @from AS StartDate, @to AS EndDate
FROM     tbl_CompanyProfile

UNION ALL

SELECT '--' , '--' , '--' , '--' , '--' , tbl_IPD.ipd_pfname + ' ' + tbl_IPD.ipd_plname AS NAME,
CONVERT(VARCHAR, tbl_IPD.ipd_doa, 103) AS DATE, 
ISNULL(SUM(tbl_fee.fee_amount), 0) AS AMOUNT, tblDepartment.DeptName AS DEPARTMENT,
tbl_IPD.ipd_age AS Age, @from AS StartDate, @to AS EndDate
FROM     tbl_fee LEFT OUTER JOIN
tbl_IPD ON tbl_fee.ipd_id = tbl_IPD.ipd_id LEFT OUTER JOIN
tblDepartment ON tbl_IPD.ipd_dpt = tblDepartment.DeptId
WHERE  (tbl_IPD.ipd_doa BETWEEN @from AND @to)
GROUP BY tbl_IPD.ipd_pfname, tbl_IPD.ipd_plname, tbl_IPD.ipd_doa, 
tblDepartment.DeptName, tbl_IPD.ipd_age         

Answer 1

您正在将“NULL”设置为字符串值。这不是NULL值。如果通过删除单引号将“NULL”更改为NULL，则报表应将这些值显示为空。

Answer 2

添加：

having SUM(tbl_fee.fee_amount) > 0

到您的联盟中的第二个select。

这将防止第二个select返回行。