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?