Hi guys,
I'm getting the following error message when trying to run a MERGE
statement I'm putting together. The syntax looks right to me But i must
be missing something. The "srce" table reference runs fine on its own.
[Error Code: -788, SQL State: S1000] [IBM][CLI Driver][DB2/6000]
SQL0969N
There is no message text corresponding to SQL error "-788" in the
message file on this workstation.
The error was returned from module "SQLRIERR" with original tokens
"SFDPRFP.TB_FA_ALLOC_HIST_C".
Can anyone spot any glaring errors just through eyeballing this?
MERGE INTO SFDPRFP.TB_FA_ALLOC_HIST_C target
USING (
SELECT (CASE WHEN LEFT(driver.ALLOC_TYPE, 6)='Unpaid' THEN
RTRIM(COALESCE(hist.Alloc_type, driver.ALLOC_TYPE)) ||
CASE WHEN hist.ALLOC_TYPE='PAY Non Ledger Acc' THEN '/' ||
COALESCE(hist.Alloc_Account, '') ELSE '' END
ELSE
RTRIM(driver.ALLOC_TYPE) ||
CASE WHEN driver.ALLOC_TYPE='PAY Non Ledger Acc' THEN '/' ||
COALESCE(driver.Alloc_Account, '') ELSE '' END
END ) AS merged_value, driver.alloc_type, hist.alloc_type,
driver.alloc_item_id
FROM SFDPRFP.TB_FA_ALLOC_HIST_C driver
LEFT OUTER JOIN SFDPRFP.VW_FA_ALLOC_HIST_M hist
ON driver.FROM_ID = hist.TO_ID
AND driver.RMT_PYMNT_ID = hist.RMT_PYMNT_ID
AND driver.ALLOC_ITEM_ID <> hist.ALLOC_ITEM_ID
) AS srce
ON target.ALLOC_ITEM_ID = srce.ALLOC_ITEM_ID
WHEN MATCHED THEN
UPDATE SET target.derived_allocation_type = srce.merged_value
;
If I simplify the case, the following works. It's a bit pointless, but
it works.
MERGE INTO SFDPRFP.TB_FA_ALLOC_HIST_C target
USING (
SELECT 'test' AS merged_value, driver.alloc_item_id
FROM SFDPRFP.TB_FA_ALLOC_HIST_C driver
) AS srce
ON target.ALLOC_ITEM_ID = srce.ALLOC_ITEM_ID
WHEN MATCHED THEN
UPDATE SET target.derived_allocation_type = srce.merged_value
;