2

  1. This is the query that does not work:

    SELECT distinct ord.DateOrdered
   , (SELECT docno 
      FROM th_mm_c_orderline_history 
      WHERE th_mm_c_orderline_history_id 
            in (SELECT max(th_mm_c_orderline_history_id) 
                FROM th_mm_c_orderline_history 
                GROUP BY c_orderline_id ) 
      order by docno,c_orderline_id) as docno 
FROM c_order ord 
INNER JOIN c_orderline on c_orderline.c_order_id = ord.c_order_id 
INNER JOIN th_mm_c_orderline_history 
      on th_mm_c_orderline_history.c_order_id=ord.c_order_id

    It is throwing me ORA-00936 Missing expression error

  2. This query works fine:

    SELECT docno 
FROM th_mm_c_orderline_history 
WHERE th_mm_c_orderline_history_id 
      in (SELECT max(th_mm_c_orderline_history_id) 
          FROM th_mm_c_orderline_history 
          GROUP BY c_orderline_id ) 
order by docno,c_orderline_id as docno
William Robertson
  • 15,273
  • 4
  • 38
  • 44
non srithong
  • 21
  • 3
  • Hi @nonsrithong please show us in form of a text, the query that does not work ? – VBoka May 29 '20 at 08:14
  • SELECT distinct ord.DateOrdered, (SELECT docno FROM th_mm_c_orderline_history WHERE th_mm_c_orderline_history_id in (SELECT max(th_mm_c_orderline_history_id) FROM th_mm_c_orderline_history GROUP BY c_orderline_id ) order by docno,c_orderline_id) as docno FROM c_order ord INNER JOIN c_orderline on c_orderline.c_order_id=ord.c_order_id INNER JOIN th_mm_c_orderline_history on th_mm_c_orderline_history.c_order_id=ord.c_order_id – non srithong May 29 '20 at 08:18
  • Please do **not** post code as images. See here for more details why: http://meta.stackoverflow.com/questions/285551 –  May 29 '20 at 08:25

2 Answers2

1

Just remove the order by clause from the inline select. You can not use orde by clause there. You can use it in the outer select if you need it...This is how you can do all three of them without the error:

SELECT distinct ord.DateOrdered
       , (SELECT docno FROM th_mm_c_orderline_history 
          WHERE th_mm_c_orderline_history_id 
                 in (SELECT max(th_mm_c_orderline_history_id) 
                     FROM th_mm_c_orderline_history 
                     GROUP BY c_orderline_id) 
          ) as docno 
FROM c_order ord 
INNER JOIN c_orderline on c_orderline.c_order_id = ord.c_order_id 
INNER JOIN th_mm_c_orderline_history on th_mm_c_orderline_history.c_order_id=ord.c_order_id
VBoka
  • 8,995
  • 3
  • 16
  • 24
0

You can use "order by statement" end of the whole select statement. Because of not use the column C_ORDERLINE_ID in select statement, it can be error in order by statement. Try this version in below.

 SELECT DISTINCT
       C_ORDER.DATEORDER,
       (SELECT DOCNO
          FROM TH_MM_C_ORDERLINE_HISTORY
         WHERE     C_ORDER_ID = C_ORDER_ID
               AND TH_MM_C_ORDERLINE_HISTORY_ID IN (  SELECT MAX (TH_MM_C_ORDERLINE_HISTORY_ID)
                                                        FROM TH_MM_C_ORDERLINE_HISTORY
                                                    GROUP BY C_ORDERLINE_ID)) AS DOCNO,
       C_ORDER.DOCUMENTNO
  FROM C_ORDER 
  INNER JOIN C_ORDERLINE ON C_ORDERLINE.C_ORDER_ID = C_ORDER_ID 
  ORDER BY DOCNO, C_ORDERLINE_ID;
BengisuY
  • 46
  • 3