SQL -120 Error

[MikeMcKeown]

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

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

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.

Any help, much appreciated

Thanks in advance,

 

[fredericofonseca]

try
with ab as (select max(a.effective_date) max_date from smm03)
SELECT ASU_NEW_NET_RATE
INTO :SMM03-ASU-NEW-NET-RATE from smm03 a
where a.effective_date = ab.max_date

or
select aa into :variable from bb where bb.date in (select max(effective_date) from xx)

Note. I haven´t tried this, so you may need to work around it, but it should give you an idea

 

[kkitt]

How about this max date will float to top then just check for the -811 ( more the on row in singleton select) and ignore:

EXEC SQL
   SELECT ASU_NEW_NET_RATE
   INTO  :SMM03-ASU-NEW-NET-RATE
   FROM SMM03 A
   order by A.EFFECTIVE_DATE  DESC
END-EXEC

evaluate SQLCODE
   when ZERO
   when -811
      CONTINUE   (row found)
   when +100
      display 'No row Found processing'
   when other
      perform DB2 Error Routines
end-evaluate

 

[Crox]

SQL questions don't belong here. Better ask this at the DB2 forum.