CODEWITHSUNDEEP
sqloracle-database
don_julian
don_julian

Reputation: 55

Why I get error missing right parenthesis ORA-00907

I am stuck in one problem and I have no idea where did I made mistake. Since I check everything and every solution but I can not see what I made wrong.

SELECT
    o.OrderID,
    o.Order_date,
    o.status,
    o.OrderAcceptanceCommentsSaved,
    o.OrderFileAttachment, 
    o.HasErrors,
    o.ErrorsResolved,
    (SELECT ou.Status FROM order_unload ou WHERE ou.OrderID = o.OrderID
     AND rownum <= 1 ORDER BY ou.Id DESC) AS UnloadStatus
FROM
    orders o
WHERE
    ProjectID = 141
ORDER BY ou.Id DESC;

The problem here is second SELECT

(SELECT ou.Status FROM order_unload ou WHERE ou.OrderID = o.OrderID
   AND rownum <= 1 ORDER BY ou.Id DESC) AS UnloadStatus)

However, when I want to execute only second SELECT I also get error

o.OrderID invalid identifier

Can someone guide me and tell me where I made mistake? What is wrong with this query?

Upvotes: 0

Views: 72

Answers (1)

MT0
MT0

Reputation: 167982

You have several problems:

  1. The ORDER BY clause is not allowed in a correlated sub-query so the SQL engine expects the query to end before the ORDER BY and there to be a closing brace at that point. Remove the ORDER BY clause in the inner select and that error would go away (and you would get a different error).
  2. ROWNUM is applied before the ORDER BY is evaluated so, even if the query was syntactically valid, it would not do what you wanted as you would get a random row (the first the SQL engine happens to read) which would be given a ROWNUM of 1 and then the rest of the rows discarded and then that single (random) row would be ordered. You want to order first and then get the first row.
  3. You are using ou.id to order the outer query but the ou alias is not visible in that outer select.

You can use:

SELECT o.OrderID,
       o.Order_date,
       o.status,
       o.OrderAcceptanceCommentsSaved,
       o.OrderFileAttachment, 
       o.HasErrors,
       o.ErrorsResolved,
       ou.status AS UnloadStatus
FROM   orders o
       LEFT OUTER JOIN (
         SELECT status,
                orderid,
                id,
                ROW_NUMBER() OVER ( PARTITION BY orderid ORDER BY id DESC ) AS rn
         FROM   order_unload
       ) ou
       ON ( o.orderid = ou.OrderID AND ou.rn = 1 )
WHERE  ProjectID = 141
ORDER BY ou.Id DESC;

db<>fiddle here

Upvotes: 2

Related Questions