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
