SQL -102 Error

[MikeMcKeown]

I'm getting an SQL -102 error on this bit of SQL

EXEC SQL
SELECT ASU_NEW_NET_RATE
INTO :SMM03-ASU-NEW-NET-RATE
FROM SMM03 A
WHERE A.EFFECTIVE_DATE = MAX(A.EFFECTIVE_DATE)
END-EXEC

I just want to select the most recent row from the table.

Thanks in advance,

 

[MikeMcKeown]

Sorry, bit of a typo. The SQL error I'm getting is a -120, not a -102.

-120 A WHERE CLAUSE, SET CLAUSE, VALUES CLAUSE, OR A SET ASSIGNMENT STATEMENT INCLUDES A COLUMN FUNCTION

 

[MarcLodge]

Hi Mike,
The way you've coded the MAX is causing you the problem. Are you trying to get just one row back or are you expecting numerous rows?

You could open this is a cursor in descending order on the date, optimising by using the FETCH FIRST 1 ROW (if you are only after one row obviously!).

Another way to do it would be to code a sub select on the date along the lines of:

SELECT ASU_NEW_NET_RATE
INTO :SMM03-ASU-NEW-NET-RATE
FROM SMM03 A
WHERE A.EFFECTIVE_DATE =
SELECT MAX(B.EFFECTIVE_DATE)
FROM SMM03 B

If there are any key fields they would be needed in the sub select.

Let us know if this helps, or get back to us with a little more info in what you are trying to achieve.
Marc

 

[MikeMcKeown]

Thanks a lot marc, I shall try the sub select code.

I'm only trying to select a single row - the most recent value. So I suspect a cursor is not necessary?

Thanks for your help,

 

[MarcLodge]

Either way will work. The cursor might be more efficient I would guess.

Marc

 

[MikeMcKeown]

Yeah thanks a lot...efficiency shouldn't matter too much so I used the code

SELECT ASU_NEW_NET_RATE
INTO :SMM03-ASU-NEW-NET-RATE
FROM SMM03 A
WHERE A.EFFECTIVE_DATE =
SELECT MAX(B.EFFECTIVE_DATE)
FROM SMM03 B

except I required the = to be replaced by an in, to make it a sub select...should have seen that in the first place!

Thanks again,

 

[MarcLodge]

Sorry about that, hadn't actually tested it!!