Yes, exactly, that's what Bill downall also recommended.
Thanks to all
Bernie Lis
----- Original Message -----
From: "James Bentley" <[EMAIL PROTECTED]>
To: "RBG7-L Mailing List" <[email protected]>
Sent: Tuesday, July 05, 2005 7:06 PM
Subject: [RBG7-L] - Re: Select Max question
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
