Ora 00907在SQL中缺少右括号

Question

SELECT
    g.msisdn, c.name3, c.id_number,
    decode(g.sub_state, 'B01', 'Active', 'B02', 'Close', 'B03', 'Suspend','B02', 'Barring') STATUS,
    decode(g.prepaid_flag, '0', 'Prepaid', '1', 'Postpaid','3', 'Hybrid') RATING,
    d.offer_name, t.create_date
    FROM inf_subscriber_all g
    INNER JOIN (
    SELECT a.sub_id, a.cust_id, a.offer_id, a.create_date FROM (
    SELECT cust_id,MAX(create_date) AS maxdate FROM his_offers WHERE primary_flag ='1' GROUP BY cust_id) as tm 
    INNER JOIN his_offers a ON a.create_date = tm.maxdate and a.cust_id = tm.cust_id) as a ON a.cust_id = g.cust_id
    INNER JOIN inf_customer_all c ON c.cust_id = a.cust_id
    INNER JOIN pdm_offer d ON d.offer_id = a.offer_id
    WHERE g.msisdn IN ('135018013','197745505','195154724')

请帮帮忙，我不知道为什么此sql脚本缺少右括号错误

Answer 1

您将问题标记为MySQL，但错误消息指出Oracle，语法也一样(DECODE()在Oracle中受支持，但在MySQL中不受支持)。

查询中的括号是平衡的。一个吸引眼球的问题是，您正在使用as关键字为子查询设置别名。Oracle不支持这一点(只允许对列别名使用as)；您需要删除以下内容：

SELECT ...
FROM inf_subscriber_all g
INNER JOIN (
    SELECT ...
    FROM (
        ...
    ) as tm                         ---> here
    INNER JOIN his_offers a ON ...
) as a ON ...                       ---> and here
INNER JOIN inf_customer_all c ON ...
INNER JOIN pdm_offer d ON ...
WHERE ...

至于查询本身:似乎子查询的目的是为每个his_offer选择具有最大create_date的cust_id行。这可以用窗口函数更有效地表达出来：

SELECT
    g.msisdn, c.name3, c.id_number,
    decode(g.sub_state, 'B01', 'Active', 'B02', 'Close', 'B03', 'Suspend','B02', 'Barring') STATUS,
    decode(g.prepaid_flag, '0', 'Prepaid', '1', 'Postpaid','3', 'Hybrid') RATING,
    d.offer_name, t.create_date
FROM inf_subscriber_all g
INNER JOIN (
    SELECT a.*, RANK() OVER(PARTIION BY cust_id ORDER BY create_date) rn
    FROM his_offers
) as a ON a.cust_id = g.cust_id AND a.rn = 1
INNER JOIN inf_customer_all c ON c.cust_id = a.cust_id
INNER JOIN pdm_offer d ON d.offer_id = a.offer_id
WHERE g.msisdn IN ('135018013','197745505','195154724')