Show row with fixed values when no rows retuerned

Transact-SQL
1

Hi

I'm trying to set my query to show a fixed row of values when the main query returns no row.

The query builds a header, a column list and the data using 2 unions.

The final query for the data is, as an example, like this:

.......
UNION ALL
Select 3 as Expr2
, 'D100', Sales_Date, [Sales Count]
FROM (
             select TOP 100 PERCENT DeptID as DeptCode
            , SaleDate
            , SalesID
            FROM SALES s  inner join DEPT d ON s.DEPTID = d.DEPTID
            WHERE <Conditions>
             ) as Sales2
) as SalesList

When no data is returned I want to display the following - zero sales date rather than nothing:

'D101',' 01/01/1900',  0

I'm also trying to figure out how to union this to existing header record returned.

I've tried using case and IF statement but it doesn't seem to make logical sense plus it fails.

Is there another way of doing this?

Thanks

Andrew

2

Add this additional union inside your SalesList table

UNION ALL

SELECT 'D101',' 01/01/1900',  0
WHERE NOT EXISTS
(
SELECT 1 FROM (
             SELECT TOP 100 PERCENT DeptID AS DeptCode
            , SaleDate
            , SalesID
            FROM SALES s  INNER JOIN DEPT d ON s.DEPTID = d.DEPTID
            WHERE <Conditions>
             ) AS Sales2
)
3

hi

another way is using @@rowcount

@@rowcount gets the number of rows effected in previous statement

++++
your SQL statement

if @@rowcount = 0
select 'D101',' 01/01/1900', 0
++++

1 Like