Oracle SQL CASE WHEN语句缺失右括号问题求助及语法修复

语法错误原因 #

你的SQL报错missing right parentheses，核心问题有两点：

• FROM子句中直接使用CASE表达式，不符合SQL语法规范——CASE是行级表达式，不能直接作为FROM的数据源表。
• 每个CASE分支嵌套独立SELECT子查询的写法冗余，导致解析器无法正确识别语句结构。

修复后的简洁SQL代码 #

SELECT
    -- 第1列：仅当长度为10时取第1位，否则NULL
    CASE WHEN LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) = 10 
         THEN SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 1, 1) 
         ELSE NULL END AS col1,
    -- 第2列：长度≥9时取第2位，否则NULL
    CASE WHEN LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) >=9 
         THEN SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 2, 1) 
         ELSE NULL END AS col2,
    -- 第3列：长度≥8时取第3位，否则NULL
    CASE WHEN LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) >=8 
         THEN SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 3, 1) 
         ELSE NULL END AS col3,
    -- 第4列：长度≥7时取第4位，否则NULL
    CASE WHEN LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) >=7 
         THEN SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 4, 1) 
         ELSE NULL END AS col4,
    -- 第5列：长度≥6时取第5位，否则NULL
    CASE WHEN LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) >=6 
         THEN SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 5, 1) 
         ELSE NULL END AS col5,
    -- 第6列：长度≥5时取第6位，否则NULL
    CASE WHEN LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) >=5 
         THEN SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 6, 1) 
         ELSE NULL END AS col6,
    -- 第7列：长度≥4时取第7位，否则NULL
    CASE WHEN LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) >=4 
         THEN SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 7, 1) 
         ELSE NULL END AS col7,
    -- 第8列：长度≥3时取第8位，否则NULL
    CASE WHEN LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) >=3 
         THEN SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 8, 1) 
         ELSE NULL END AS col8,
    -- 第9列：长度≥2时取第9位，否则NULL
    CASE WHEN LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) >=2 
         THEN SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 9, 1) 
         ELSE NULL END AS col9,
    -- 第10列：长度≥1时取最后一位，否则NULL
    CASE WHEN LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) >=1 
         THEN SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), -1, 1) 
         ELSE NULL END AS col10
FROM nationalNumber

功能解析 #

这段代码的核心作用是：
将nationalNumber.NATIONAL_IDENTIFIER_NUMBER字段的字符从左到右拆分到10个列中，当字段长度不足10位时，左侧的列填充NULL，仅保留右侧的有效字符。
例如：

• 字段长度为2时：第9、10列取字段的2个字符，前8列为NULL
• 字段长度为10时：10个列分别取字段的第1到第10位字符
• 字段长度不足2或超过10时：所有列均为NULL

内容的提问来源于stack exchange，提问作者Faris Adnan