SQL错误:ORA-00907:Oracle 10g中缺少右括号 [英] SQL Error: ORA-00907: missing right parenthesis in Oracle 10g
问题描述
请找到以下查询.当我运行此查询时，我得到一个ORA-00907:缺少右括号错误.我无法理解此查询中的问题.请给我解决此错误的方法.我已经阅读了与此主题相关的一些主题.但是，我没有找到正确的解决方案.我正在使用Oracle 10g版本.
Please find the below query. When I run this query I am getting an ORA-00907: missing right parenthesis error. I am unable to understand the problem on this query. Please give me the solution for this error. I have read some related topics on this topic. but, I didn't get the right solution. I am using Oracle 10g version.
查询:
SELECT A.City||'.' AS AAddress,
M_InOut_Header_v.Org_Location_ID AS Org_Location_ID,
M_InOut_Header_v.ContactName,
M_InOut_Header_v.Title,
M_InOut_Header_v.EMail,
M_InOut_Header_v.Phone,
M_InOut_Header_v.BPGreeting,
M_InOut_Header_v.Name2,
M_InOut_Header_v.Name,
B.City||'.' AS BAddress,
M_InOut_Header_v.C_Location_ID AS C_Location_ID,
M_InOut_Header_v.BPContactGreeting,
--M_InOut_Header_v.BPGreeting,
--M_InOut_Header_v.Name,
--M_InOut_Header_v.Name2,
M_InOut_Header_v.Address1,
M_InOut_Header_v.Address2,
M_InOut_Header_v.Address3,
M_InOut_Header_v.Address4,
M_InOut_Header_v.City,
M_InOut_Header_v.POSTAL1,
M_InOut_Header_v.CountryName,
--M_InOut_Header_v.BPContactGreeting,
--M_InOut_Header_v.Name,
M_InOut_Header_v.OrderRemarks,
(SELECT NVL(C_Order.DocumentNo,'')||'
- '||NVL(TRIM(TO_CHAR(C_Order.DateOrdered,'DD/MM/YYYY')),'')
FROM C_Order
WHERE M_InOut_Header_v.C_Order_ID=C_Order.C_Order_ID
) AS CC_Order_ID,
M_InOut_Header_v.C_Order_ID AS C_Order_ID,
M_InOut_Header_v.OrderType,
M_InOut_Header_v.ReferenceNo,
M_InOut_Header_v.POReference,
(SELECT NVL(M_Warehouse.Name,'')
FROM M_Warehouse
WHERE M_InOut_Header_v.M_Warehouse_ID=M_Warehouse.M_Warehouse_ID
) AS DM_Warehouse_ID,
M_InOut_Header_v.M_Warehouse_ID AS M_Warehouse_ID,
(SELECT NVL(M_Shipper.Name,'')
FROM M_Shipper
WHERE M_InOut_Header_v.M_Shipper_ID=M_Shipper.M_Shipper_ID
) AS EM_Shipper_ID,
M_InOut_Header_v.M_Shipper_ID AS M_Shipper_ID,
M_InOut_Header_v.Vehicle_No_BizInt,
M_InOut_Header_v.TrackingNo,
M_InOut_Header_v.Permit_No_BizInt,
M_InOut_Header_v.LR_Number_BizInt,
M_InOut_Header_v.FREIGHTCOSTRULE2,
M_InOut_Header_v.FreightAmt,
M_InOut_Header_v.POREMARKS,
M_InOut_Header_v.NoPackages,
M_InOut_Header_v.Gross_Weight_BizInt,
M_InOut_Header_v.UOMSymbol,
M_InOut_Header_v.SalesRepPhone,
M_InOut_Header_v.SalesRepEmail,
M_InOut_Header_v.DocumentType,
InOut_Header_v.DocumentNo,
M_InOut_Header_v.MovementDate,
M_InOut_Header_v.Description,
(SELECT NVL(M_InOut.DocumentNo,'')||'
- '||NVL(TRIM(TO_CHAR(M_InOut.MovementDate,'DD/MM/YYYY')),'')
FROM M_InOut
WHERE M_InOut_Header_v.M_InOut_ID=M_InOut.M_InOut_ID
) AS FM_InOut_ID,
M_InOut_Header_v.M_InOut_ID AS M_InOut_ID,
M_InOut_Header_v.DocumentTypeNote,
M_InOut_Header_v.REMARKS1,
M_InOut_Header_v.REMARKS2,
M_InOut_Header_v.REMARKS3,
M_InOut_Header_v.REMARKS4
FROM M_InOut_Header_v
LEFT OUTER JOIN C_Location A
ON (M_InOut_Header_v.Org_Location_ID=A.C_Location_ID)
LEFT OUTER JOIN C_Location B
ON (M_InOut_Header_v.C_Location_ID=B.C_Location_ID)
WHERE (M_InOut_Header_v.M_InOut_ID=1002241)
AND M_InOut_Header_v.AD_Client_ID IN (1000008,0)
AND M_InOut_Header_v.AD_Org_ID IN (1000099,1000098,0,1000100,1000096,1000097)
AND (A.C_Location_ID IS NULL
OR A.C_Location_ID NOT IN (
SELECT PA.Record_ID FROM AD_Private_Access AS PA
WHERE PA.AD_Table_ID = 162 AND PA.AD_User_ID <> 1013144
AND PA.IsActive = 'Y'
))
AND ( B.C_Location_ID IS NULL
OR B.C_Location_ID NOT IN (
SELECT ADP.Record_ID FROM AD_Private_Access AS ADP
WHERE ADP.AD_Table_ID = 162 AND ADP.AD_User_ID <> 1013144
AND ADP.IsActive = 'Y'
))
ORDER BY M_InOut_Header_v.DocumentNo;
这给了我
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:
Error at Line: 80 Column: 26
但是我没有发现那条线有任何错误.我认为这在语法上是正确的.
But I didn't find any mistake in that line. I think it is syntactically correct.
推荐答案
行号有些误导，但它指向问题的开始.问题在于这两个子查询:
The line number is slightly misleading, but it's pointing to the start of the problem. The issue is with these two subqueries:
SELECT PA.Record_ID FROM AD_Private_Access AS PA
WHERE PA.AD_Table_ID = 162 AND PA.AD_User_ID <> 1013144
AND PA.IsActive = 'Y'
和:
SELECT ADP.Record_ID FROM AD_Private_Access AS ADP
WHERE ADP.AD_Table_ID = 162 AND ADP.AD_User_ID <> 1013144
AND ADP.IsActive = 'Y'
您不能使用AS标记别名表名称，仅(可选)列名称或表达式.实际上并没有缺少括号.很难确切知道解析器在想什么，但是在这种情况下，它看起来像是试图将AS PA解释为该子查询的列别名，这意味着该子查询应该已经结束了，因此应该已经是一个紧密的括号了. (另一种选择可能是尝试将AS 视为表别名，但随后必须尝试确定PA的含义；加上AS是关键字，因此仍然不能用作别名).
You cannot use AS to mark an alias for a table name, only (optionally) for a column name or expression. There isn't actually a missing parenthesis. It's hard to know exactly what the parser is thinking, but in this case it looks like it's trying to interpret the AS PA as a column alias for that subquery, and that implies that the subquery should have ended by now, and so there should have been a close parenthesis already. (Another option might have been to try to treat AS as the table alias, but then it would have had to try to decide what PA meant; plus AS is a keyword so it wouldn't be valid as an alias name anyway).
只需从这两个关键字中删除AS关键字，它就会起作用(或移至另一个错误).
Just remove the AS keyword from both of those and it'll work (or move on to another error).
SELECT PA.Record_ID FROM AD_Private_Access PA
...
和:
SELECT ADP.Record_ID FROM AD_Private_Access ADP
...
这篇关于SQL错误:ORA-00907:Oracle 10g中缺少右括号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
