Hi,
I’m trying to migrate the merge upsert into Postgresql.I’m stuck. Not sure. Please.
---------------------------- ORACLE ---------------------------
MERGE INTO igsd
USING (SELECT inv_333,
’ || vsq || ‘,
ccn2,
pid4,
pfg
FROM isg ISW1
’ || vsq2 || ’
WHERE ccn2 = ‘’’ || pCCNo || ‘’’
AND pid4 = ’ || pPoleId || ’ ’
|| REPLACE(pWhereClause, ‘^#@’, ‘%’) || ’ ’
|| vDynamicInvoiceWhereClause || ‘)
ON (iinvd = i333 AND igid = ’ || pGroupId || ‘)
WHEN MATCHED THEN
UPDATE
SET isid = ’ || pSegmentNo || ‘,
srs =
(CASE
WHEN srs IS NULL THEN
‘’’ || pRuleSet || ‘’’
ELSE
(srs || ‘’,’’ || ‘’’ || pRuleSet || ‘’‘)
END),
sd = DECODE(IGSD_OLD_SEGMENT_ID,’ || pSegmentNo || ‘,
sd,FNC_CA_GetPoleDate(’ || pPoleId || ', 0)),
itime = SYSDATE
WHERE (isid IS NULL OR isid = ’ || pSegmentNo || ‘)
WHEN NOT MATCHED THEN
INSERT(IGSD_ID, iinvd, igid, IGSD_CC_NO, IGSD_POLE_ID, isid,
IGSD_OLD_SEGMENT_ID, srs, sd, IGSD_PAID_FLAG, itime)
VALUES(seqsg.NEXTVAL, i333, ’ || pGroupId || ‘,
ccn2, pid4, ’ || pSegmentNo || ‘,
(CASE
WHEN ’ || pGroupId || ’ = ’ || GID || ’ THEN
old_segment_id
ELSE
’ || vDefaultSegmentId || ’
END), ‘’’ || pRuleSet || ‘’’, fnc_pdt(’ || pPoleId || ‘, 0),
pfg, SYSDATE)’;
----------------------------- POSTGRESQL ------------------------------
UPDATE igsd
SET
isid = ' || pSegmentNo || ',
srs =
(CASE
WHEN srs IS NULL THEN
''' || pRuleSet || '''
ELSE
(srs || '','' || ''' || pRuleSet || ''')
END),
sd = CASE IGSD_OLD_SEGMENT_ID WHEN ' || pSegmentNo || ' THEN
sd ELSE fnc_pdt(' || pPoleId || ', 0) END,
itime = CURRENT_TIMESTAMP::TIMESTAMP(0)
WHERE (isid IS NULL OR isid = ' || pSegmentNo || ')
INSERT INTO GCARS.igsd
(IGSD_ID, iinvd, igid, IGSD_CC_NO, IGSD_POLE_ID, isid,
IGSD_OLD_SEGMENT_ID, srs, sd, IGSD_PAID_FLAG, itime)
VALUES(NEXTVAL(GCARS.seqsg), i333, ' || pGroupId || ',
ccn2, pid4, ' || pSegmentNo || ',
(CASE
WHEN ' || pGroupId || ' = ' || GID || ' THEN
old_segment_id
ELSE
' || vDefaultSegmentId || '
END), ''' || pRuleSet || ''', GCARS.fnc_pdt(' || pPoleId || ', 0),
pfg, CURRENT_TIMESTAMP::TIMESTAMP(0))';