I am looking to do something similar, but I am getting a syntax error when I run PruSQLer's code. Here is my SQL:
with temp
(id1, maxMeasure)
As (Select CORP_MBR_ID, max(gpi_code)
from dba1.drug_claim
where left(gpi_code,4) = '4410'
Group by CORP_MBR_ID)
Select CORP_MBR_ID, maxMeasure, max(SRVC_FROM_DATE)
from temp,
dba1.drug_claim
where dba1.drug_claim.CORP_MBR_ID = id1
group by dba1.drug_claim.CORP_MBR_ID, maxMeasure
The error message is:
An unexpected token "(" was found following "IS <HEXSTRING> <CHARSTRING> <GRAPHSTRING>", Expected tokens include "", SQLSTATE: 42601.
I am using AQT 6.1.6 with DB2 on a mainframe.