Problem with using max in the below update statement

Question

Hi I'm having a problem with using the max statement in the below query i am working on

UPDATE TB_TS_CM_OFIRECPT SET AMT = (COALESCE (ABS(CMLEDG.TRANAMT), 0) + 
COALESCE (ABS(CMLEDG_1.TRANAMT), 0)) * - 1, TRANAMT = ABS(CMLEDG.TRANAMT) *-1, 
RTAXAMT = COALESCE (ABS(CMLEDG_1.TRANAMT) * - 1, 0), 
TRANID = CMLEDG.TRANID FROM TB_TS_CM_OFIRECPT INNER JOIN CMLEDG 
ON TB_TS_CM_OFIRECPT.TRANID = CMLEDG.REFNMBR  
AND TB_TS_CM_OFIRECPT.DESCRPTN = CMLEDG.DESCRPTN LEFT OUTER JOIN 
CMLEDG AS CMLEDG_1 ON CMLEDG.TRANID = CMLEDG_1.PARENTTRANID 
AND CMLEDG.DESCRPTN = CMLEDG_1.DESCRPTN WHERE (TB_TS_CM_OFIRECPT.IMPID = '195') 
AND (TB_TS_CM_OFIRECPT.ACTION = 'REVERSED')

TRANID = CMLEDG.TRANID FROM TB_TS_CM_OFIRECPT INNER JOIN CMLEDG  - This particualr line 

should actually be something like TRANID = SELECT MAX(TRANID) FROM ....

However i kerep getting a syntax error. appreciate yuor help

Regards,

Abhi

Answer 1

Not entirely sure but try:

tranID = (SELECT MAX(tranID) ... FROM ... INNER JOIN ... ),

The brackets may be enough to let the compiler group the sub query together nicely before assigning the value.

If you can create views I usually find it much easier to define a view with all the calculated fields and then just make the update itself as plain as possible. the separation makes it easier to work out where the real problem lies.