add a sequential number to a query [message #659309] |
Thu, 12 January 2017 07:10 |
|
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 |
Bill B
Messages: 1971 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 #659314 is a reply to message #659309] |
Thu, 12 January 2017 07:57 |
Bill B
Messages: 1971 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
|
|
|