SQL Error: ORA-00907: missing right parenthesis in Oracle 10g

Question

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.

The query:

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;

This gives me:

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.

Answer 1

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' 

and:

                    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' 

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).

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 
                    ...

and:

                    SELECT ADP.Record_ID FROM AD_Private_Access ADP 
                    ...