At 01:45 AM 26/02/2008, you wrote:

>I am on Firebird 1.5.4.
>
>I need to get a field value value dependant on the maximum of another
>field value.
>
>For example in the following case, I want the BATCH NUMBER value for
>the maximum RECORDNO value.
>
>I have tried the following SQL, but I keep on getting an error
>message (TERMID is an input variable):

You don't say what the error message is so it's hard to say which syntax error the compiler is finding first! ;-)

FOR SELECT MAX(RECORDNO), BATCHNUM FROM BATCH
WHERE
TERMID = :TERMID
GROUP BY BATCHNUM
INTO
:TRECORDNO, :TBATCHNUM
-- so far, so good
DO
BEGIN
-- 3 syntax errors in next line
-- IF :TRECORDNO > :OLDRECNO THEN
IF (TRECORDNO > OLDRECNO) THEN
BEGIN
/* repeat of one of these errors in next 2 lines and of course we hope you have initialised the variables TRECORDNO and TBATCHNO */
-- :OLDRECNO = :TRECORDNO;
-- :OLDBATCHNO = :TBATCHNO;

OLDRECNO = TRECORDNO;
OLDBATCHNO = TBATCHNO;

END
SUSPEND;
END

-- repeat of one error here:
-- BATCHNUM = :OLDBATCHNO + 1;

BATCHNUM = OLDBATCHNO + 1;
IF (SWITCH = 0) THEN
BEGIN
IF (BATCHNUM > 999999) THEN
BEGIN
BATCHNUM = 1;
END
END

IF (SWITCH > 0) THEN
BEGIN
IF (BATCHNUM > 999) THEN
BEGIN
BATCHNUM = 1;
END
END

There is never a situation where the enclosing brackets for an expression being tested by IF or WHILE can be omitted.

The rule for use of the colon prefix inside a PSQL module or block is that it is to be applied to a *variable* (input, local or output) whenever that variable is referred to in a DML statement, i.e. one starting with SELECT, INSERT, UPDATE or DELETE. Otherwise, a variable is just a variable.

From Fb 1.5 on, I believe there are some situations where you can get away with omitting the colon prefix from a DML statement but nobody has ever actually gotten around to documenting what those situations are. I suspect that it might hover around whether there is a name conflict between a variable name and a column identifier...probably the case where omission of the prefix would make the instruction ambiguous...

Historical experience tells us that implementing rules in the language that are enforced conditionally is Bad Karma so, on that basis, I strongly recommend assuming that the rules are always enforced.

./heLen