| ROWNUM returns NULL rows when value > 1 [message #534861] |
Fri, 09 December 2011 14:49  |
 |
aaichah
Messages: 24
Registered: July 2011
Location: Ottawa
|
Junior Member |
|
|
Hi, I am trying to do a simple query where I need to return the rows from a table and treat each rown according to some rules.
The query works fine, and returns all the rows, usually I have 2 rows returned. WHen I add to the query where ROWNUM = 1, I get
the first row returned, but when I use when ROWNUM =2 OR ROWNUM >1, I always get null rows retured, even if I have rows in the database. Here is my query:
SELECT on_time
INTO on_time2
FROM work.work_unit
WHERE work_code = 1
AND emp_no = :entry_blk.p_emp_no
AND work_date = :entry_blk.p_work_date
WHERE ROWNUM = 2;
--RETURN NULL
I changed it to the following format, but still I get the same results, only I get data when I say when rownum = 1, i get back the first record in the query
SELECT on_time
INTO on_time2
FROM (SELECT on_time
FROM work.work_unit
WHERE work_code = 1
AND emp_no = :entry_blk.p_emp_no
AND work_date = :entry_blk.p_work_date)
WHERE ROWNUM = 2;
Please help, I can't move forward in my form until I figure out why this is not returning records
Thank you
|
|
|
|
|
|
| Re: ROWNUM returns NULL rows when value > 1 [message #534864 is a reply to message #534861] |
Fri, 09 December 2011 15:05  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
As you can see, Michel sent you to the explanation of what ROWNUM is, but I am interested to know what you "think" ROWNUM=2 means.
You probably think it means the second row "displayed" from a set of returned rows. Even if it was that, without an ORDER BY clause, you can get rows in a random order every time you run the same exact query.
|
|
|
|
|
|
| Re: ROWNUM returns NULL rows when value > 1 [message #534880 is a reply to message #534873] |
Fri, 09 December 2011 23:38 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
aaichah wrote on Fri, 09 December 2011 23:41to tell you the truth, I am still confused why I am not getting rows when we say rownum > 1
I also put the order by in my query, and still not getting any rows.
I am not sure what is the fix for this
OK, here is more detailed explanation: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:948366252775
Just think about it. There are other conditions in the query than the one using ROWNUM. Would you assign ROWNUM=1 to the row which does not satisfy them? And no, ROWNUM condition is not evaluated separately. When should it be assigned, if you would compare it not with a constant, but e.g. with a correlated subquery?
In fact, it is not clear what exactly you want to achieve, as (in your understanding of ROWNUM) it would be equivalent to use ROWNUM=2, ROWNUM=5 or ROWNUM=1 (although only the last would exactly do that because of ROWNUM real implementation). All of them would pick one row satisfying other conditions, without any other rule - it may be any of them even each time you run the query and in all those cases, that result will be correct.
Without knowing the purpose of this query, it is impossible to propose any "fix" for this.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
