String sql8 = "update TEMP_FACILITY_MITIGATION_INFO f " +
"set f.MitigationType01=(select xx.TypeAftereRevision from (select m.TypeAftereRevision from MITIGATION_REVISION_RECORD m where (m.FacilityID=f.FaciltyID and m.GuaranteeContractID=f.GuaranteeID01 and m.RecordStatus='10' and m.RefObjectRange='10') order by m.inputtime desc) xx where rownum=1) "+
"where id='001'"出错信息:Caused by: java.sql.SQLException: ORA-00904: "F"."GUARANTEEID01": invalid identifier
TEMP_FACILITY_MITIGATION_INFO这个表里是有GUARANTEEID01这个字段的,应该是f的作用范围的问题
"set f.MitigationType01=(select xx.TypeAftereRevision from (select m.TypeAftereRevision from MITIGATION_REVISION_RECORD m where (m.FacilityID=f.FaciltyID and m.GuaranteeContractID=f.GuaranteeID01 and m.RecordStatus='10' and m.RefObjectRange='10') order by m.inputtime desc) xx where rownum=1) "+
"where id='001'"出错信息:Caused by: java.sql.SQLException: ORA-00904: "F"."GUARANTEEID01": invalid identifier
TEMP_FACILITY_MITIGATION_INFO这个表里是有GUARANTEEID01这个字段的,应该是f的作用范围的问题
update TEMP_FACILITY_MITIGATION_INFO f
set f.MitigationType01 = (select xx.TypeAftereRevision
from (select m.TypeAftereRevision
from MITIGATION_REVISION_RECORD m
where ( m.FacilityID = f.FaciltyID
and m.GuaranteeContractID =f.GuaranteeID01
and m.RecordStatus = '10'
and m.RefObjectRange = '10')
order by m.inputtime desc) xx
where rownum = 1)
where id = '001'
看一下有什么提示。
一般是从后往上的。
MERGE INTO customacc.CUSTMTR_COMPARE a
USING customacc.CUSTMTR_COMPARE@DB89 b
ON (a.code = b.code )
WHEN MATCHED THEN
UPDATE
SET a.material = b.material, a.spec = b.spec,
a.curr_code = b.curr_code
WHEN NOT MATCHED THEN
INSERT (a.code, a.material, a.spec, a.curr_code, a.upddttm, a.upduser,
a.POSITION)
VALUES (b.code, b.material, b.spec, b.curr_code, SYSDATE, USER,
b.POSITION)
----3楼正解,你在oracle中执行下就看出问题所在了
update TEMP_FACILITY_MITIGATION_INFO f set f.MitigationType01=(select xx.TypeAftereRevision from (select m.TypeAftereRevision from MITIGATION_REVISION_RECORD m where (m.FacilityID=f.FaciltyID and m.GuaranteeContractID=f.GuaranteeID01 and m.RecordStatus='10' and m.RefObjectRange='10') order by m.inputtime desc) xx where rownum=1) where id='001;