Re: sql plus max function query

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

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
Received on Mon Feb 01 1999 - 04:03:00 CST