Unable to understand Query [message #263736] |
Thu, 30 August 2007 22:11 |
newtooracle123
Messages: 24 Registered: May 2007
|
Junior Member |
|
|
Hi all,
I have come across these two queries. I know what is the output for the same but I dont know why they behaves so
1) select sum(1)from employee;
--This will return the rowcount of the employee table
2) select sequence_name.nextval,sequence_name.nextval from dual;
-- For eg if the sequence is at 10, then this query will return 11 and 11
I want to know why the above queries behave in such a manner.
Thank you
|
|
|
Re: Unable to understand Query [message #263771 is a reply to message #263736] |
Fri, 31 August 2007 00:21 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
First query: Sum normally is used on a column of the resultset. Now they added a column with a constant value 1 for each record in the resultset (= table, because there is no where-clause).
Then a summing is done on all those 1's, resulting in the number of records.
I sincerely hope this is an exercise, because it is a nonsense way to do it.
Second query: It is per definition that sequence.nextval returns the same value for each row fetched. It is just as Oracle designed it; no fancy sql-trick
|
|
|