Oracle内联视图SQL查询报错ORA-00907：缺失右括号排查

你的SQL报错是因为Oracle不允许用AS给表指定别名，加上还有几处和其他数据库（比如SQL Server）的语法差异，导致解析报错。以下是具体问题和修正方案：

错误根源 #

错误提示指向的第33行对应M_ITEM_H AS H，Oracle中表别名不能用AS关键字，必须直接写表名 别名，这是导致ORA-00907的直接原因——Oracle解析到AS时语法出错，误判为缺少右括号。

此外还有两处语法问题（当前报错解决后会触发新错误）：

• Oracle字符串截取用SUBSTR，而非SUBSTRING
• Oracle字符串连接用||，而非+（+在Oracle中是算术运算符，用于数字相加）

修正后的完整SQL #

SELECT
    COMP_CODE, 
    CUST_CODE, 
    ITEM_CODE, 
    CASE WHEN UOM1 IS NOT NULL THEN QTY1 * QTY2 * QTY3 * QTY4 * QTY5 ELSE NULL END AS QTY1, 
    CASE WHEN UOM2 IS NOT NULL THEN QTY2 * QTY3 * QTY4 * QTY5 ELSE NULL END AS QTY2, 
    CASE WHEN UOM3 IS NOT NULL THEN QTY3 * QTY4 * QTY5 ELSE NULL END AS QTY3, 
    CASE WHEN UOM4 IS NOT NULL THEN QTY4 * QTY5 ELSE NULL END AS QTY4, 
    CASE WHEN UOM5 IS NOT NULL THEN QTY5 ELSE NULL END AS QTY5, 
    CASE WHEN UOM1 IS NOT NULL THEN SUBSTR((UOM1 || '    '), 1, 4) ELSE NULL END AS UOM1, 
    CASE WHEN UOM2 IS NOT NULL THEN SUBSTR((UOM2 || '    '), 1, 4) ELSE NULL END AS UOM2,
    CASE WHEN UOM3 IS NOT NULL THEN SUBSTR((UOM3 || '    '), 1, 4) ELSE NULL END AS UOM3, 
    CASE WHEN UOM4 IS NOT NULL THEN SUBSTR((UOM4 || '    '), 1, 4) ELSE NULL END AS UOM4, 
    CASE WHEN UOM5 IS NOT NULL THEN SUBSTR((UOM5 || '    '), 1, 4) ELSE NULL END AS UOM5
FROM
    (
    SELECT
        H.COMP_CODE, 
        H.CUST_CODE,
        H.ITEM_CODE, 
        H.QTY_BKD_PROF_CODE, 
        MAX(CASE WHEN D1.ITEM_QTY_BKD_LEV_NUM = 1 THEN D1.ITEM_QTY_BKD_QTY ELSE 1 END) AS QTY1, 
        MAX(CASE WHEN D1.ITEM_QTY_BKD_LEV_NUM = 2 THEN D1.ITEM_QTY_BKD_QTY ELSE 1 END) AS QTY2,
        MAX(CASE WHEN D1.ITEM_QTY_BKD_LEV_NUM = 3 THEN D1.ITEM_QTY_BKD_QTY ELSE 1 END) AS QTY3,
        MAX(CASE WHEN D1.ITEM_QTY_BKD_LEV_NUM = 4 THEN D1.ITEM_QTY_BKD_QTY ELSE 1 END) AS QTY4,
        MAX(CASE WHEN D1.ITEM_QTY_BKD_LEV_NUM = 5 THEN D1.ITEM_QTY_BKD_QTY ELSE 1 END) AS QTY5, 
        MAX(CASE WHEN D1.ITEM_QTY_BKD_LEV_NUM = 1 THEN BK.SKU_CODE ELSE NULL END) AS UOM1, 
        MAX(CASE WHEN D1.ITEM_QTY_BKD_LEV_NUM = 2 THEN BK.SKU_CODE ELSE NULL END) AS UOM2, 
        MAX(CASE WHEN D1.ITEM_QTY_BKD_LEV_NUM = 3 THEN BK.SKU_CODE ELSE NULL END) AS UOM3, 
        MAX(CASE WHEN D1.ITEM_QTY_BKD_LEV_NUM = 4 THEN BK.SKU_CODE ELSE NULL END) AS UOM4, 
        MAX(CASE WHEN D1.ITEM_QTY_BKD_LEV_NUM = 5 THEN BK.SKU_CODE ELSE NULL END) AS UOM5
    FROM
        M_ITEM_H H
        INNER JOIN M_ITEM_D1 D1  
            ON H.COMP_CODE = D1.COMP_CODE AND H.CUST_CODE = D1.CUST_CODE AND H.ITEM_CODE = D1.ITEM_CODE
        INNER JOIN M_QTY_BKD_PROF_D BK  
            ON H.COMP_CODE = BK.COMP_CODE AND H.QTY_BKD_PROF_CODE = BK.QTY_BKD_PROF_CODE AND D1.ITEM_QTY_BKD_LEV_NUM = BK.QTY_BKD_PROF_LEV_NUM
    GROUP BY 
        H.COMP_CODE,
        H.CUST_CODE,
        H.ITEM_CODE, 
        H.QTY_BKD_PROF_CODE
         )

关键语法差异说明 #

1. 表别名：Oracle仅允许列别名使用AS，表别名必须省略AS，直接写表名 别名
2. 字符串操作：
   • 替换SUBSTRING为Oracle原生的SUBSTR函数，参数一致（字符串、起始位置、长度）
   • 替换字符串连接符+为||，避免Oracle将其当作算术运算处理

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