Re: sql plus max function query

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 01 Feb 1999 13:19:11 GMT
Message-ID: <36b5a907.522931@192.86.155.100>

A copy of this was sent to sdgreenwood_at_ybs.co.uk (if that email address didn't require changing) On Mon, 01 Feb 1999 09:35:04 GMT, you wrote:

>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.
>

aggregates return rows by GROUP BY variables. An aggregate with no group by always returns 1 row. Thats just the way they work.

Try something like this:

SQL> select max(created) from all_users where 1=0 group by 1;

no rows selected

SQL> select max(created) from all_users where 1=1 group by 1;

MAX(CREAT

---

31-JAN-99 group by something, in the above, i'm grouping by a 'constant' 1. If nothing passes the where clause, zero rows are returned since there is nothing to group by. If some rows pass through the where clause, then they are grouped by the number 1 and the answer is returned.

Alternatively, an inline view may be used to achieve the same effect:

SQL> select * from ( select max(created) created from all_users where 1=0)   2 where created is not null
  3 /

no rows selected

SQL> select * from ( select max(created) created from all_users where 1=1)   2 where created is not null
  3 /

CREATED

---

31-JAN-99
>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  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

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:19:11 CST