0

Below is a sub-query of a bigger query, what I am trying to do is to get last 5 documents sorted by SL_DT in descending.

I always get an error that the right parenthesis is missing, I have also considered using row_number() over (order by pa.last_modified_date desc) but it doesn't work.

SELECT   REPLACE (
              wm_concat(   SL_TXN_CODE
                        || ' - '
                        || SL_NO
                        || '('
                        || SL_DT
                        || ') - '
                        || SUM (SL_QTY)),
              ',',
              ' ,'
           )
    FROM   STK_LEDGER
   WHERE   ROWNUM <= 5
           AND SL_ITEM_CODE =
                 (SELECT   IDH_ITEM_CODE
                    FROM   AA_ITEM_DEFINATION_HEAD
                   WHERE   IDH_SUPP_BC_1 = '111' OR IDH_ITEM_CODE = '111')
           AND SL_TXN_TYPE IN ('SARTN', 'GRN', 'LTRFI')
           AND SL_LOCN_CODE NOT IN ('D2', 'D4', 'D5')
GROUP BY   SL_TXN_CODE, SL_NO, SL_DT
ORDER BY   SL_DT DESC

Please suggest the best way to sort SL_DT in descending and getting the 5 records only. As you can see that I need all data in one single field.

The database is Oracle 10g.

Thanks in advance.

user3625561
  • 305
  • 5
  • 25
  • The database is Oracle 10g – user3625561 Apr 28 '15 at 07:50
  • Agree with Peter, on formatting your query to that a human can read it – David Aldridge Apr 28 '15 at 07:56
  • Please provide the create and insert statements as sample data. And don't use `wm_concat`, it is an **undocumented feature**, and is no more available in the latest 12c version. See http://stackoverflow.com/a/28758117/3989608 – Lalit Kumar B Apr 28 '15 at 08:21
  • Updated the question as per above advices. @lalit-kumar-b, my oracle version is 10g. – user3625561 Apr 28 '15 at 08:32
  • @user3625561 In **10g**, you could use `ROW_NUMBER() and SYS_CONNECT_BY_PATH functions` which are available since 9i. Why would you use an undocumented feature which has been removed from latest release. And it would be easy to help you if you provide the create and insert statements. At least a **SQL Fiddle**. – Lalit Kumar B Apr 28 '15 at 08:35

1 Answers1

0
SELECT VALUE
  FROM (SELECT VALUE, ROWNUM AS ROW_NUM
          FROM (SELECT REPLACE(WM_CONCAT(SL_TXN_CODE || ' - ' || SL_NO || '(' ||
                                         SL_DT || ') - ' || SUM(SL_QTY)),
                               ',',
                               ' ,') AS VALUE
                  FROM STK_LEDGER
                 WHERE SL_ITEM_CODE =
                       (SELECT IDH_ITEM_CODE
                          FROM AA_ITEM_DEFINATION_HEAD
                         WHERE IDH_SUPP_BC_1 = '111'
                            OR IDH_ITEM_CODE = '111')
                   AND SL_TXN_TYPE IN ('SARTN', 'GRN', 'LTRFI')
                   AND SL_LOCN_CODE NOT IN ('D2', 'D4', 'D5')
                 GROUP BY SL_TXN_CODE, SL_NO, SL_DT
                 ORDER BY SL_DT DESC))
 WHERE ROW_NUM <= 5
Janis Baiza
  • 951
  • 6
  • 15
  • There is an `AND` after `WHERE`, I removed it but still it gives error that `rownum` is not a single-group group function. – user3625561 Apr 28 '15 at 08:19
  • Don't use `wm_concat`, it is an undocumented feature and is no more available in the latest 12c release. See http://stackoverflow.com/a/28758117/3989608 – Lalit Kumar B Apr 28 '15 at 08:23
  • Missed that `GROUP BY`. Sadly, but another level of `SELECT` needs to be added. – Janis Baiza Apr 28 '15 at 08:23