Re: sql plus max function query

The sql statement will always return a single row. Even if the result set contains no rows the max value is returned as null. I'm not sure why you think nvl will make it work. You should not use %notfound but test the value returned for being zero. %notfound return code would be a unexpected error and shouldn't be possible (?). If you need to know whether the result set actually contained any rows then should also return count(*). At least you can then deduce whether all start_dates contained the value null or the result set was empty.

Hope this helps.

e.g.

select

sdgreenwood_at_ybs.co.uk 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.
>
> 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:26:28 CST