How To Textually Indicate That There Are No Results In A Sql Select?

August 30, 2023 Post a Comment

I have the following SQL CASE statement that doesn't appear to be evaluating the conditional correctly. This table currently is empty (0 non-null rows) so I would assume the ELSE

Solution 1:

IF ((SELECTCOUNT(*) FROM PS_MASTER_ITEM_EC EC WHERE EC.INV_ITEM_ID <>''AND EC.INV_ITEM_ID ISNOTNULL) >0)
SELECT EC.INV_ITEM_ID FROM PS_MASTER_ITEM_EC EC WHERE EC.INV_ITEM_ID <>''AND EC.INV_ITEM_ID ISNOTNULLELSESELECT'NO ITEMS IN STAGING TABLES'

First line will calculate how many non empty rows there are in the table second line will return a 1 column table with all the IDs that were not NULL or '' if any is found at all 4th row will return the default message if the table was actually empty

Solution 2:

All values in a column must have the same data type. So if the INV_ITEM_ID column is being used for a string value of 'NO ITEMS IN STAGING TABLES' in the case where there aren't any records then the column must have a textual data type.

If this isn't the case, you'll need to convert the IDs to strings to match this in the case where there are records. Personally I'd advise against this as it's like mixing chalk and cheese - a string value doesn't really belong in a numeric ID column - and even if the IDs happen to be strings it's still questionable as semantically the "no results" string isn't an ID.

But if you really want to do it, something like the following SQL can achieve it:

SELECT'NO ITEMS IN STAGING TABLES'AS INV_ITEM_ID
WHERENOTEXISTS (SELECT*FROM PS_MASTER_ITEM_EC)
UNIONALLSELECTCAST(INV_ITEM_ID ASVARCHAR(26))
FROM PS_MASTER_ITEM_EC EC
ORDERBY INV_ITEM_ID;

Rextester demo:https://rextester.com/FIRWF53864

Solution 3:

You can't return records from an empty table. The case statement is being evaluated against every record in the table. When there are no records then there will be nothing to evaluate, thus the empty result set. There are a few ways to check for this condition. If the client is expecting a specific result set and will check a known field for the existence of data, (which seems hacky) then you can return similar shaped data from a derived result set with your messaging, however, I would encourage you to think about checking for this condition at the consumer as database queries are better suited for inserting/updating/deleting and getting data.

DECLARE@PS_MASTER_ITEM_EC TABLE(INV_ITEM_ID NVARCHAR(200))
--INSERT @PS_MASTER_ITEM_EC VALUES ('I''m Here')

IF(NOTEXISTS(SELECT EC.INV_ITEM_ID FROM@PS_MASTER_ITEM_EC EC))
    SELECT INV_ITEM_ID ='NO ITEMS IN STAGING TABLES'ELSESELECTCASEWHEN EC.INV_ITEM_ID <>''THEN EC.INV_ITEM_ID
     ELSE'EC.INV_ITEM_ID has an empty string for a value'ENDAS INV_ITEM_ID
  
FROM@PS_MASTER_ITEM_EC EC
ORDERBY INV_ITEM_ID

Solution 4:

Your problem is that your CASE is evaluated only for existant rows, if your table is empty you will get no record at all.

But, you want to get a record with a message even if the table is empty so you must create that row. You can do it using UNION to add a new row to result set only when PS_MASTER_ITEM_EC is empty.

So, you can simply list all your records if there is any, and show your message when PS_MASTER_ITEM_EC is empty.

SELECT EC.INV_ITEM_ID
FROM PS_MASTER_ITEM_EC EC
ORDERBY INV_ITEM_ID

UNIONALLSELECT'NO ITEMS IN STAGING TABLES'WHERENOTEXISTS(SELECT TOP 1'X' X FROM PS_MASTER_ITEM_EC )

Pay attention, you need INV_ITEM_ID column to be of type char/varchar/nvarchar or you will get an error. If your INV_ITEM_ID is a number you can use this workaround:

SELECT EC.INV_ITEM_ID, NULL Message
FROM PS_MASTER_ITEM_EC EC
ORDERBY INV_ITEM_ID

UNIONALLSELECTNULL INV_ITEM_ID, 'NO ITEMS IN STAGING TABLES' Message
WHERENOTEXISTS(SELECT TOP 1'X' X FROM PS_MASTER_ITEM_EC )

Solution 5:

SQL Server is a relational database. Relational databases works of off sets, which is basically a list of records. If there is no record, then your function / logic (CASE in this case) cannot be applied. If you need a record returned when your query returns zero record, then you need to provide a record from another source to be returned. One way of doing that is providing a record through UNION with a WHERE clause that guarantees a special record is returned when the result set is empty. When the result set is NOT empty, then WHERE clause makes sure this new record is not returned. The gotcha here is that, this special record has to have the same number of columns as the regular query result and with the same or compatible data types.

; WITH cte (
 ---- your complex query goes here select colString, colNumeric 
   .............
) 
SELECT colString, colNumeric FROM cte 
UNIONALLSELECT'no result found'as colString, NULLas colNumeric  WHERENOTEXISTS (SELECT*FROM cte) --- this returns true only if no record in cte

Lab Sql