联合Oracle SQL中的所有困难

Question

当尝试在Oracle SQL中执行UNION All查询时，我遇到了右括号的问题。

((SELECT DISTINCT call.call_id,
                  call_key_msg.key_msg,
                  Substr(call_key_msg.clm_id, 0, Length(call_key_msg.clm_id) - 4
                  )
                    AS asset,
                  call_clickstream.track_elmnt_desc
                    AS TRACKELEMENT
  FROM   outbound.call,
         outbound.call_key_msg,
         outbound.call_clickstream
  WHERE  call.call_id = call_key_msg.call_id
         AND call_clickstream.call(+) = call.call_id
         AND call.call_id IN ( '1', '2',
                               '3', '4' )
  MINUS
  SELECT DISTINCT call.call_id,
                  call_key_msg.key_msg,
                  Substr(call_key_msg.clm_id, 0, Length(call_key_msg.clm_id) - 4
                  )
                  AS asset,
                  call_clickstream.track_elmnt_desc
                  AS TRACKELEMENT
  FROM   outbound.call,
         outbound.call_key_msg,
         outbound.call_clickstream
  WHERE  call.call_id = call_key_msg.call_id
         AND call_clickstream.call = call.call_id
         AND call_key_msg.key_msg = call_clickstream.key_msg
         AND call.call_id IN ( '1', '2',
                               '3',
                                                   '4' )
  ORDER  BY 3)
 UNION ALL
 (SELECT DISTINCT call.call_id,
                  call_key_msg.key_msg,
                  Substr(call_key_msg.clm_id, 0, Length(call_key_msg.clm_id) - 4
                  )
                  AS asset,
                  call_clickstream.track_elmnt_desc
                  AS TRACKELEMENT
  --case when call_clickstream.CLM_ID = 'CLM ID' THEN call_clickstream.PRSNTN_ID 
  --ELSE SUBSTR(call_key_msg.CLM_ID, 0, LENGTH(call_key_msg.CLM_ID) - 4) 
  --END AS asset
  FROM   outbound.call,
         outbound.call_key_msg,
         outbound.call_clickstream
  WHERE  call.call_id = call_key_msg.call_id
         AND call_clickstream.call = call.call_id
         AND call_key_msg.key_msg = call_clickstream.key_msg
         AND call.call_id IN ( '1', '2',
                               '3',
                                                   '4' )
  ORDER  BY 3)); 

不知道这里有什么问题，但我一直收到错误'ORA-00907:缺少右括号‘。我检查了我的单行本，它们似乎排成一排。有什么想法吗？忽略where call.call_id中奇怪的空格--我刚刚使用了一个SQL格式化程序，这让它看起来有点时髦。

Answer 1

请试一下这个。我删除了第一个SELECT语句之前的第二个括号和最后一个括号。

 (SELECT DISTINCT call.call_id,
                  call_key_msg.key_msg,
                  Substr(call_key_msg.clm_id, 0, Length(call_key_msg.clm_id) - 4
                  )
                    AS asset,
                  call_clickstream.track_elmnt_desc
                    AS TRACKELEMENT
  FROM   outbound.call,
         outbound.call_key_msg,
         outbound.call_clickstream
  WHERE  call.call_id = call_key_msg.call_id
         AND call_clickstream.call(+) = call.call_id
         AND call.call_id IN ( '1', '2',
                               '3', '4' )
  MINUS
  SELECT DISTINCT call.call_id,
                  call_key_msg.key_msg,
                  Substr(call_key_msg.clm_id, 0, Length(call_key_msg.clm_id) - 4
                  )
                  AS asset,
                  call_clickstream.track_elmnt_desc
                  AS TRACKELEMENT
  FROM   outbound.call,
         outbound.call_key_msg,
         outbound.call_clickstream
  WHERE  call.call_id = call_key_msg.call_id
         AND call_clickstream.call = call.call_id
         AND call_key_msg.key_msg = call_clickstream.key_msg
         AND call.call_id IN ( '1', '2',
                               '3',
                                                   '4' )
  ORDER  BY 3)
 UNION ALL
 (SELECT DISTINCT call.call_id,
                  call_key_msg.key_msg,
                  Substr(call_key_msg.clm_id, 0, Length(call_key_msg.clm_id) - 4
                  )
                  AS asset,
                  call_clickstream.track_elmnt_desc
                  AS TRACKELEMENT
  --case when call_clickstream.CLM_ID = 'CLM ID' THEN call_clickstream.PRSNTN_ID 
  --ELSE SUBSTR(call_key_msg.CLM_ID, 0, LENGTH(call_key_msg.CLM_ID) - 4) 
  --END AS asset
  FROM   outbound.call,
         outbound.call_key_msg,
         outbound.call_clickstream
  WHERE  call.call_id = call_key_msg.call_id
         AND call_clickstream.call = call.call_id
         AND call_key_msg.key_msg = call_clickstream.key_msg
         AND call.call_id IN ( '1', '2',
                               '3',
                                                   '4' )
  ORDER  BY 3); 

Answer 2

您的问题是将每个union all分支放在括号内，大概是为了解决在union all分支中不能使用order by的问题。

您可以在括号查询之前将select * from添加到，以使其正常工作。请参阅此快速DBfiddle，它演示了您的错误和语法修复。

对于您的查询，它将如下所示：

select * from (SELECT DISTINCT call.call_id,
                  call_key_msg.key_msg,
                  Substr(call_key_msg.clm_id, 0, Length(call_key_msg.clm_id) - 4
                  )
                    AS asset,
                  call_clickstream.track_elmnt_desc
                    AS TRACKELEMENT
  FROM   outbound.call,
         outbound.call_key_msg,
         outbound.call_clickstream
  WHERE  call.call_id = call_key_msg.call_id
         AND call_clickstream.call(+) = call.call_id
         AND call.call_id IN ( '1', '2',
                               '3', '4' )
  MINUS
  SELECT DISTINCT call.call_id,
                  call_key_msg.key_msg,
                  Substr(call_key_msg.clm_id, 0, Length(call_key_msg.clm_id) - 4
                  )
                  AS asset,
                  call_clickstream.track_elmnt_desc
                  AS TRACKELEMENT
  FROM   outbound.call,
         outbound.call_key_msg,
         outbound.call_clickstream
  WHERE  call.call_id = call_key_msg.call_id
         AND call_clickstream.call = call.call_id
         AND call_key_msg.key_msg = call_clickstream.key_msg
         AND call.call_id IN ( '1', '2',
                               '3',
                                                   '4' )
  ORDER  BY 3)
 UNION ALL
select * from 
 (SELECT DISTINCT call.call_id,
                  call_key_msg.key_msg,
                  Substr(call_key_msg.clm_id, 0, Length(call_key_msg.clm_id) - 4
                  )
                  AS asset,
                  call_clickstream.track_elmnt_desc
                  AS TRACKELEMENT
  --case when call_clickstream.CLM_ID = 'CLM ID' THEN call_clickstream.PRSNTN_ID 
  --ELSE SUBSTR(call_key_msg.CLM_ID, 0, LENGTH(call_key_msg.CLM_ID) - 4) 
  --END AS asset
  FROM   outbound.call,
         outbound.call_key_msg,
         outbound.call_clickstream
  WHERE  call.call_id = call_key_msg.call_id
         AND call_clickstream.call = call.call_id
         AND call_key_msg.key_msg = call_clickstream.key_msg
         AND call.call_id IN ( '1', '2',
                               '3',
                                                   '4' )
  ORDER  BY 3); 

我去掉了最外面的括号，因为它们并不能真正解决一个目的，但它们也不会造成什么伤害。