Hi All,

How to write SQL in report script to get the latest value based on the latest date.
From table TCMCS008, I want to get the latest RATE value based on latest APDT date.

My SQL:
select MAX(tcmcs008.apdt):maxapdt, Last(tcmcs008.rate):LastRATE
from tcmcs008
where tcmcs008.ccur = {:rep.inv.ccur}
and tcmcs008.rtyp = "INT"

selectdo
endselect

Can anybody assist me, instead of use Last, any reserve word which this script will understand.

SQLState 42I00: Unexpected token 'last' (error 302)

Thank You.

Hi,

You can use order by with as set with one rows

select tcmcs008.apdt,tcmcs008.rate
from tcmcs008
where tcmcs008.ccur = {:rep.inv.ccur}
and tcmcs008.rtyp = "INT"
order by tcmcs008.apdt desc
as set with 1 rows
selectdo
endselect

Also check if the query is correct. You might need to include 2 currencies.

Hi Bhushan,

It's working..Marvellous

Thank You.