如何以文本方式指示 SQL SELECT 中没有结果? [英] How to textually indicate that there are no results in a SQL SELECT?

问题描述

我有以下 SQL CASE 语句似乎没有正确评估条件.该表当前为空(0 非空行)，因此我假设 ELSE 条件应为真，因此输出下面的文本字符串.但是，它不会在结果中填充任何内容.

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 condition should be true and therefore output the text string below. However it doesn't populate anything in the results.

SELECT 
CASE WHEN EC.INV_ITEM_ID <> '' THEN EC.INV_ITEM_ID
     ELSE 'NO ITEMS IN STAGING TABLES'
     END
  
FROM PS_MASTER_ITEM_EC EC
ORDER BY INV_ITEM_ID

推荐答案

IF ((SELECT COUNT(*) FROM PS_MASTER_ITEM_EC EC WHERE EC.INV_ITEM_ID <> '' AND EC.INV_ITEM_ID IS NOT NULL) > 0)
SELECT EC.INV_ITEM_ID FROM PS_MASTER_ITEM_EC EC WHERE EC.INV_ITEM_ID <> '' AND EC.INV_ITEM_ID IS NOT NULL
ELSE
SELECT 'NO ITEMS IN STAGING TABLES'

第一行将计算表中有多少非空行第二行将返回一个 1 列表，其中包含所有不为 NULL 或 ''(如果有的话)的 ID如果表实际上是空的，第 4 行将返回默认消息

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

这篇关于如何以文本方式指示 SQL SELECT 中没有结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！