基于SQL中的空列插入错误消息列

Question

我有一张如下创建的表。

CREATE TABLE TEST_NULL 
(
ID INT, 
NAME VARCHAR(10), 
PLACE VARCHAR(10), 
ADDRESS VARCHAR(10), 
ERRORMESSAGE VARCHAR(100) 
) 

INSERT INTO TEST_NULL 
VALUES 
(1, 'ABC', 'BLR', 'WHT', NULL), 
(2, 'DEF', 'BLR', NULL, NULL), 
(3, 'GHI', NULL, NULL, NULL), 
(4, 'JKL', NULL, 'WHT', NULL) 

SELECT * FROM TEST_NULL 

当我为表格做了选择时，我想要得到ERRORMESSAGE列的预期结果，如下所示。

我需要得到第一纪录：

1, 'ABC', 'BLR', 'WHT', NULL 

我需要获得第二个纪录：

2, 'DEF', 'BLR', NULL, 'Address is NULL' 

我需要得到第三个纪录：

3, 'GHI', NULL, NULL, 'Place, Address is NULL' 

我需要得到第4条记录为：

4, 'JKL', NULL, 'WHT', 'Place is NULL' 

任何帮助表示赞赏。

感谢。

Answer 1

我想用CASE表达式生成错误消息是去这里最彻底的方法：

SELECT ID, NAME, PLACE, ADDRESS, 
     CASE WHEN PLACE IS NULL AND ADDRESS IS NULL THEN 'Place, Address is NULL' 
      WHEN PLACE IS NULL      THEN 'Place is NULL' 
      WHEN ADDRESS IS NULL     THEN 'Address is NULL' 
      ELSE NULL END 
FROM TEST_NULL 

Answer 2

使用Case条款： -

SELECT ID,NAME,PLACE,ADDRESS, case 

      when 
       PLACE is not null and ADDRESS is not null 
      then null 
      when 
       PLACE is null and ADDRESS is not null 
      then 'PLACE is NULL' 
      when 
       PLACE is not null and ADDRESS is null 
      then 'ADDRESS is NULL' 
      when 
       PLACE is null and ADDRESS is null 
      then 'PLACE, Address is NULL' 
      end as ERRORMESSAGE 

FROM TEST_NULL 

结果： -

ID NAME PLACE ADDRESS ERRORMESSAGE 
1 ABC  BLR  WHT  NULL 
2 DEF  BLR  NULL ADDRESS is NULL 
3 GHI  NULL NULL PLACE, Address is NULL 
4 JKL NULL  WHT PLACE is NULL