0

Hello i have a select query that have column data from subquery to get first data ordered by date from other table, it is works normal if the subquery doesn't have order by syntax. However when the subquery using order by it gives missing right parenthesis errors. 

Select ...,
 A.number,
               (
                 SELECT L.created_by_user_id
                   FROM user_content L
                  WHERE ( 
                          L.content LIKE '%message%'
                          OR L.content LIKE '%response%'
                        ) 
                        AND L.number = A.number 
                        AND ROWNUM = 1
                        ORDER BY  created_by_date /* If this order by deleted, the query works well*/
               ) as first_user
FROM ...

I also have try separate the query for rownum like below query and order by it stills get error invalid identifier.

  Select ...,
  A.number,
  (select K.created_by_user_id from
                 (SELECT L.created_by_user_id
                   FROM user_content L
                  WHERE ( 
                          L.content LIKE   '%message%' 
                          OR L.content LIKE   '%response%'
                        ) 
                        AND L.number = A.number
                        ORDER BY created_by_date
                        ) K
                 where ROWNUM = 1) as first_user
  FROM ...

The database engine is using oracle Please Help. Thank you.

Sky Blue
  • 203
  • 5
  • 15
  • 1
    ROWNUM = 1 is used for get the first row in the oracle, it is from oracle syntax and it is possible. The problem is not from the rownum but from order by, the oracle doesn't allow to have order by in this subquery. The query works fine when order by is removed and still have rownum syntax – Sky Blue Mar 14 '18 at 04:32
  • 1
    Possible duplicate of [ORA-00907 Missing right Parenthesis issue - select with order by inside insert query](https://stackoverflow.com/questions/9175749/ora-00907-missing-right-parenthesis-issue-select-with-order-by-inside-insert-q) – Barbaros Özhan Mar 14 '18 at 05:13
  • Not duplicate, i still have problem with invalid identifier when following that query. – Sky Blue Mar 14 '18 at 06:19
  • Problem is not order by, but the fact that you are trying to reference outer table alias in the subquery that is two-levels deep (select xxx from (select xxx from ...)). That is not supported by Oracle. Also, your first example is plain wrong. You cannot use rownum and order by in the same subquery - where clause is evaluated BEFORE order by, so you cannot guarantee that rows will e in the correct order when rownum = 1 is applied. – Goran Stefanović Mar 14 '18 at 11:08
  • You mention the problem is not order by is for the second query right? I also already fix it see my answer below. For the first example, yes it can't use rownum and order by in the same subquery so i am asking for the answer, i don't say the first query in question is correct. This problem already closed by the way see my answer below. – Sky Blue Mar 20 '18 at 08:05

1 Answers1

0

I solved by separate the query for rownum and move the number where to upper query. At least the query works well and give correct result. But maybe this query can be improved for performance.

Select ...,
  A.number,
  (select K.created_by_user_id from
                 (SELECT L.created_by_user_id
                   FROM user_content L
                  WHERE ( 
                          L.content LIKE   '%message%' 
                          OR L.content LIKE   '%response%'
                        ) 
                        ORDER BY created_by_date
                        ) K
                 where ROWNUM = 1 AND L.number = A.number) as first_user
  FROM ...
Sky Blue
  • 203
  • 5
  • 15