Home » SQL & PL/SQL » SQL & PL/SQL » add a sequential number to a query (sql)
add a sequential number to a query [message #659309] Thu, 12 January 2017 07:10 Go to next message
sanjuchand
Messages: 5
Registered: August 2016
Junior Member
Hello,

I need to add a sequential number starting from 1 to the number of rows in the below select. I tried to use row_number() but not able to make it work.

SELECT LEVEL lvl,
                     /*ROW_NUMBER ()
                     OVER (PARTITION BY LEVEL, menu_id, entry_sequence
                           ORDER BY entry_sequence)
                        AS rownumber,*/
                     entry_sequence,
                     (SELECT user_menu_name
                        FROM fnd_menus_vl fmvl
                       WHERE 1 = 1 AND fmvl.menu_id = fmv.menu_id)
                        menu_name,
                    (SELECT menu_id from fnd_menus_vl fmvl WHERE 1 = 1 AND fmvl.menu_id = fmv.menu_id) menu_id,
                     (SELECT user_menu_name
                        FROM fnd_menus_vl fmvl
                       WHERE 1 = 1 AND fmvl.menu_id = fmv.sub_menu_id)
                        sub_menu_name,
                     function_id,
                     prompt,
                     grant_flag,
                     SYS_CONNECT_BY_PATH(prompt, '@') path,
                     description
                FROM (select * from fnd_menu_entries_vl where prompt is not null and grant_flag in 'Y') fmv
          START WITH menu_id = 87486
          CONNECT BY PRIOR sub_menu_id = menu_id
         ORDER SIBLINGS BY entry_sequence;
Re: add a sequential number to a query [message #659311 is a reply to message #659309] Thu, 12 January 2017 07:28 Go to previous messageGo to next message
Bill B
Messages: 1802
Registered: December 2004
Senior Member
if you want a row number over the entire select you do the following. This query will work on your database so you can try it out.

select object_name,owner,row_number() over (order by owner,object_name) line_num
from all_objects;
Re: add a sequential number to a query [message #659313 is a reply to message #659311] Thu, 12 January 2017 07:37 Go to previous messageGo to next message
sanjuchand
Messages: 5
Registered: August 2016
Junior Member
It is not working as I have order siblings by clause later in the query and both appear to be incompatible
Re: add a sequential number to a query [message #659314 is a reply to message #659309] Thu, 12 January 2017 07:57 Go to previous message
Bill B
Messages: 1802
Registered: December 2004
Senior Member
try the following. use rownum AFTER the results are generated and ordered.

SELECT Lvl,
       Entry_sequence,
       Menu_name,
       Menu_id,
       Sub_menu_name,
       Function_id,
       Prompt,
       Grant_flag,
       PATH,
       Description,
       Fmv,
       ROWNUM Line_number
  FROM (           SELECT LEVEL Lvl,
                          Entry_sequence,
                          (SELECT User_menu_name
                             FROM Fnd_menus_vl Fmvl
                            WHERE 1 = 1 AND Fmvl.Menu_id = Fmv.Menu_id)
                              Menu_name,
                          (SELECT Menu_id
                             FROM Fnd_menus_vl Fmvl
                            WHERE 1 = 1 AND Fmvl.Menu_id = Fmv.Menu_id)
                              Menu_id,
                          (SELECT User_menu_name
                             FROM Fnd_menus_vl Fmvl
                            WHERE 1 = 1 AND Fmvl.Menu_id = Fmv.Sub_menu_id)
                              Sub_menu_name,
                          Function_id,
                          Prompt,
                          Grant_flag,
                          SYS_CONNECT_BY_PATH (Prompt, '@') PATH,
                          Description
                     FROM (SELECT *
                             FROM Fnd_menu_entries_vl
                            WHERE Prompt IS NOT NULL AND Grant_flag IN 'Y') Fmv
               START WITH Menu_id = 87486
               CONNECT BY PRIOR Sub_menu_id = Menu_id
        ORDER SIBLINGS BY Entry_sequence);

[Updated on: Thu, 12 January 2017 11:32]

Report message to a moderator

Previous Topic: Compiling in Debug Mode
Next Topic: DBA_VIEWS giving ORA-00942 Error
Goto Forum:
  


Current Time: Fri Oct 19 09:35:39 CDT 2018