| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: sql plus max function query
A copy of this was sent to "Roy Brokvam" <roy.brokvam_at_conax.com>
(if that email address didn't require changing)
On Mon, 1 Feb 1999 11:03:00 +0100, you wrote:
>If I get your problem correctly, you will experience this with all the group
>functions MAX, MIN, AVG, SUM...
>
>The reason is that the group functions *always* return one row, no matter
>how many of your database records match the where-clause. If no database
>rows match the where-clause, the function simply returns NULL (The MAX of
>nothing is NULL).
no, group functions do not always return one row (i've seem more then one application fail because of this belief -- that the query would always return a record).....
Consider:
SQL> l
1 select max( created ) from all_users
2 where username like '%ZZZZ%'
3* group by username
SQL> /
no rows selected
SQL> select max( created ) from all_users 2 where username like '%ZZZZ%';
MAX(CREAT
SQL> if an aggregate has a GROUP BY and no rows meet the where criteria -- no rows returned. It is only true that if an aggregate is used and NO group by is used that one row will be returned.
two potential answers to the question could be:
SQL> select max(created) from all_users where 1=0 group by 1;
no rows selected
SQL> select * from ( select max(created) created from all_users where 1=0)
2 where created is not null
3 /
no rows selected
the first one makes use of the fact that GROUP BY on an empty set is the empty set. The second one is more clear as to what it is doing...
>
>Roy
>
>sdgreenwood_at_ybs.co.uk wrote in message <793sg9$h6r$1_at_nnrp1.dejanews.com>...
>>Max function query
>>-----------------------------
>>
>>select max(start_date)
>>from accounts
>>where ........etc....
>>
>>If there are no rows to satisfy the above query sql plus does not say 'No
>>Rows Selected'.
>>
>>If I ran the above query without the max function 'No Rows Selected' is
>>displayed.
>>
>>The problem is that if the above query is set up in a cursor then %notfound
>>would not work, therefore processing would continue without testing whether
>>any rows have been found.
>>
>>I know that putting NVL(max(start_date,sysdate)) would work, but can
>anybody
>>tell me why the max function works differently.
>>
>>Thanks in anticipation,
>>
>>SDG
>>
>>-----------== Posted via Deja News, The Discussion Network ==----------
>>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Feb 01 1999 - 07:23:46 CST
 |
 |