Bernie, Why not kill 2 birds with one stone.
SELECT MAX(PaymentNo),count(*) INTO MaxPaymentNo IND iv1 , cntpayments iv2 FROM PAYMENTS WHERE OrderNo = 596096 If cnpayments = 0 then code to process if there are no payments else code to process assigning maxpaymentno + 1 endif --- Bernard Lis <[EMAIL PROTECTED]> wrote: > David, > It looks like my workaround will be as you > suggested: > I would count the number of records first, if it is > 0 then my next payments > no. would be 1. > If it is 3, then the next payment no. would be 4 > I just have to hope that they don't go back in and > delete payment no. 2 > But then this wouldn't be bulletproof if they did. > Bernie > ----- Original Message ----- > From: "David M. Blocker" > <[EMAIL PROTECTED]> > To: "RBG7-L Mailing List" <[email protected]> > Sent: Tuesday, July 05, 2005 4:08 PM > Subject: [RBG7-L] - Re: Select Max question > > > > Interesting: > > > > A ONE row SELECT will return SQLCODE = 100 > > > > SELECT PaymentNo INTO vPaymentNo IND iv1 FROM > PAYMENTS WHERE OrderNo > = > > 9596096 > > > > and a GROUP by SELECT will return SQLCODE = 100 > > > > SELECT OrderNo,Max(PaymentNo) INTO vOrderNo IND > iv1 ,vMaxPaymentNo IND iv2 > > FROM PAYMENTS WHERE OrderNo > = 9596096 GROUP BY > OrderNo > > > > But an AGGREGATE SELECT will always just return 0! > > > > SO you'll have to test with SELECT COUNT(*) first! > > > > David Blocker > > [EMAIL PROTECTED] > > 781-784-1919 > > Fax: 781-784-1860 > > Cell: 339-206-0261 > > ----- Original Message ----- > > From: "Bernard Lis" <[EMAIL PROTECTED]> > > To: "RBG7-L Mailing List" <[email protected]> > > Sent: Tuesday, July 05, 2005 2:47 PM > > Subject: [RBG7-L] - Select Max question > > > > > >> set var MaxPaymentNo int=null > >> SELECT MAX(PaymentNo) INTO MaxPaymentNo IND iv1 > FROM PAYMENTS WHERE > > OrderNo > >> = 9596096 > >> > >> sqlcode returns 0 even though the record does not > exist. > >> MaxPaymentNo becomes 0 and the indicator iv1 is > also 0 > >> > >> Is this the way it's supposed to be? > >> > >> Bernie Lis > >> > >> > >> > > > > > > Jim Bentley American Celiac Society [EMAIL PROTECTED] tel: 1-504-737-3293 __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
