Re: Problem with SQR procedure using MAX in select

["Sean M. Shaw" <sshaw@BIBLIO.UNM.EDU>]

>BEGIN-PROCEDURE get_seqno
>  move '0' to $max_seqno
>BEGIN-SELECT
>max(sarappd_seq_no) + 1       &col_max_seqno

Oracle will always return a record when you use the min, max, avg, etc.,
functions, even if the where clause excludes all records.  In this case, it
will return a "null" value and a "null" + 1 is still null.  One way to
handle this is:

nvl(max(sarappd_seq_no),0) + 1       &col_max_seqno

Although a "sequence" would probably serve you better if it is just a
"sequential" number that you are looking for.

************************************
*  Sean M. Shaw                    *
*  Oracle Data Automation Manager  *
*  The University of New Mexico    *
*  Health Sciences Center Library  *
*  Albuquerque, NM 87131-5686      *
*  Work:  505.277.6109             *
*  Fax:   505.277.5350             *
*  EMail: sshaw@biblio.unm.edu     *
************************************