Solved

rownum inbetween

Posted on 2014-03-13
4
333 Views
Last Modified: 2014-03-13
I am using following query but rownum in between function is not working

SELECT p.fldstring folder_level_1,
       p2.fldstring folder_level_2,
       p3.fldstring folder_level3,
       d.doc_name
  FROM document d
       INNER JOIN folder f ON d.id = f.lid
       LEFT JOIN PATH p ON f.foldid = p.fldtype || '-' || p.fldkey
       LEFT JOIN PATH p2 ON p2.fldkey = p.fldkey2
       LEFT JOIN PATH p3 ON p3.fldkey = p2.fldkey2
where rownum = 14127 and rownum = 54127;

Open in new window

Please assist
0
Comment
Question by:CalmSoul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 167 total points
ID: 39927569
Yes that is known behavior of ROWNUM as its actually evaluated after the query run.
You must put it into a subquery and filter in the outer query
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 167 total points
ID: 39927623
Rownum is a pseudo column that assigns a value to a record after the query is actually executed so you can never return records where the rownum = anything (other than 1). For example, select * from anytable where rownum = 2 will not return anything because the first record would receive a rownum value of 1 and, since it must equal 2, it doesn't retrieve it and when the second record gets retrieved it also would receive a rownum value of one and not retrieve it, and so on, never retrieving a record even if there are many rows in the table. Additionally, selecting rownum from a inner join b left join c left join d left join e will produce rownum values that are different from selecting rownum from a inner join b left join e left join d left join c. You need to determine some criteria for the rows that you want to retrieve other than rownum. Perhaps you can provide some sample data for each of the tables and what you want to see as output.
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 166 total points
ID: 39928336
that will not work as already mentioned above.. you can try :

select * from (
SELECT p.fldstring folder_level_1,
       p2.fldstring folder_level_2,
       p3.fldstring folder_level3,
       d.doc_name, rownum rr
  FROM document d
       INNER JOIN folder f ON d.id = f.lid
       LEFT JOIN PATH p ON f.foldid = p.fldtype || '-' || p.fldkey
       LEFT JOIN PATH p2 ON p2.fldkey = p.fldkey2
       LEFT JOIN PATH p3 ON p3.fldkey = p2.fldkey2 )
where rr = 2 and rr = 3 ;

-- i am trying to get the 2nd and the 3rd record from the output in the where clause.

Thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39928499
In addition to the notes provided already about the pseudo-colun rownum, you cannot guarantee the "order of output" will be what you expect without actually stipulating an order by clause.

Instead you could use the row_number() analytic function with more precision and predicable behaviour than by rownum.
SELECT *
FROM (
	SELECT
		  p.fldstring folder_level_1
		, p2.fldstring folder_level_2
		, p3.fldstring folder_level3
		, d.doc_name
		, row_number() over (order by p.fldstring, p2.fldstring, p3.fldstring) as rr /* use row_number() ?? */
	FROM document d
	INNER JOIN folder f ON d.id = f.lid
	LEFT JOIN PATH p ON f.foldid = p.fldtype || '-' || p.fldkey
	LEFT JOIN PATH p2 ON p2.fldkey = p.fldkey2
	LEFT JOIN PATH p3 ON p3.fldkey = p2.fldkey2
	)
WHERE rr = 2 AND rr = 3;

Open in new window

0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question