Oracle SQL新增ACCESS计算列遇ORA-00923报错求助

需求说明 #

需要编写SQL查询，新增名为ACCESS的列：当DI_ACCESS_NAME字段存在有效数据时显示'yes'，否则显示'no'。现有表数据如下：

EMPLID  RCD DI_ACCESS_NAME  ACCESS
123450  0   TK              Y
123450  1   HCS-P           Y
123450  1   SI              Y
123450  3   NATO            Y
123451  0                   N
123451  1                   N
123452  0   HCS-P           Y
123453  0                   N

用户编写的查询语句 #

语句1 #

SELECT emplid, empl_rcd, di_access_name, (CASE WHEN coalesce(di_access_name,'') <> '' THEN 'yes' else 'no' end) as access FROM PS_DI_SEC_ACCESS;

语句2 #

SELECT emplid, empl_rcd, di_access_name,(CASE WHEN di_access_name IS NOT NULL THEN 'yes' else 'no' end) as access
FROM PS_DI_SEC_ACCESS;

报错信息 #

ORA-00923: FROM keyword not found where expected
00923. 00000 - "FROM keyword not found where expected"
*Cause:
*Action:
Error at Line: 5 Column: 116

报错原因分析 #

ACCESS是Oracle的保留关键字，直接使用as access作为列别名时，数据库会将其解析为SQL关键字，导致语法解析逻辑混乱，进而抛出"FROM关键字未找到"的错误。

解决方法 #

将列别名access用双引号""括起来，明确告诉数据库这是自定义列名而非关键字。同时优化语句2的逻辑（若DI_ACCESS_NAME可能为空字符串而非NULL，需补充空字符串判断）：

修改后的语句1 #

SELECT emplid, empl_rcd, di_access_name, 
       (CASE WHEN COALESCE(di_access_name, '') <> '' THEN 'yes' ELSE 'no' END) AS "ACCESS" 
FROM PS_DI_SEC_ACCESS;

修改后的语句2（优化逻辑版） #

SELECT emplid, empl_rcd, di_access_name,
       (CASE WHEN di_access_name IS NOT NULL AND di_access_name <> '' THEN 'yes' ELSE 'no' END) AS "ACCESS" 
FROM PS_DI_SEC_ACCESS;

内容的提问来源于stack exchange，提问作者Bach-Nga