MERGE COMMAND WITH DECODE AND INNER SELECT

From: srini <srini.upp_at_gmail.com>
Date: 24 Aug 2005 13:03:35 -0700
Message-ID: <1124913815.715124.100800@g44g2000cwa.googlegroups.com>

 I have the below MERGE command with a decode function as part of the select clause.Irrespective of the data, the value of column ABND_ELEC_PYMT_FEE_IN, always evaluates to a 'N'. once the MEREG command runs successfully.

MERGE INTO ELECPYMT_OWNER.UNUSE_ELEC_PYMT_FEE_RPT_at_DVTBOLT T USING (SELECT DISTINCT

		 FEE.ELEC_PYMT_FEE_ID,FEE.RQST_PYMT_PROC_DT,FEE.ALT_ELEC_PYMT_FEE_ID,
		 DECODE( NVL(p.ELEC_PYMT_FEE_ID,0),0,'Y', 'N') AS
ABND_ELEC_PYMT_FEE_IN,
	  	 FEE.CRE_USR_ID,FEE.CRE_TS,FEE.UPDT_USR_ID,FEE.UPDT_TS
	  	FROM
	  	   (SELECT

E.ELEC_PYMT_FEE_ID,E.RQST_PYMT_PROC_DT,E.ALT_ELEC_PYMT_FEE_ID,E.UPDT_TS
		     FROM   ELEC_PYMT_FEE E, ELEC_PYMT PYMT
		     WHERE  E.ELEC_PYMT_FEE_ID = PYMT.ELEC_PYMT_FEE_ID (+)
		     AND    PYMT.ELEC_PYMT_FEE_ID IS NULL
			 AND	(E.UPDT_TS BETWEEN TO_DATE('&1','MM/DD/YYYY HH24:MI:SS') AND
TO_DATE('&2','MM/DD/YYYY HH24:MI:SS')- (3/24)


				 )

		     ) FEE , ELEC_PYMT P
       	 WHERE  FEE.alt_elec_pymt_fee_id = P.ELEC_PYMT_FEE_ID (+)) E
ON(
       T.ELEC_PYMT_FEE_ID = E.ELEC_PYMT_FEE_ID
       AND
       T.RQST_PYMT_PROC_DT = E.RQST_PYMT_PROC_DT

)
WHEN MATCHED THEN
UPDATE SET

		 T.ALT_ELEC_PYMT_FEE_ID = E.ALT_ELEC_PYMT_FEE_ID,
		 T.ABND_ELEC_PYMT_FEE_IN = E.ABND_ELEC_PYMT_FEE_IN,

WHEN NOT MATCHED THEN
INSERT (

		T.ALT_ELEC_PYMT_FEE_ID,
		T.ABND_ELEC_PYMT_FEE_IN,

 )VALUES (
 		E.ALT_ELEC_PYMT_FEE_ID,
 		E.ABND_ELEC_PYMT_FEE_IN,
 		)

if i execute the select clause all by itself without the MERGE command , as below, then the value of ABND_ELEC_PYMT_FEE_IN evaluates perfectly with values of 'Y' as well as 'N' depending upon the data.

SELECT DISTINCT

		 FEE.ELEC_PYMT_FEE_ID,FEE.RQST_PYMT_PROC_DT,FEE.ALT_ELEC_PYMT_FEE_ID,
		 DECODE( NVL(p.ELEC_PYMT_FEE_ID,0),0,'Y', 'N') AS
ABND_ELEC_PYMT_FEE_IN,
	  	 FEE.CRE_USR_ID,FEE.CRE_TS,FEE.UPDT_USR_ID,FEE.UPDT_TS
	  	FROM
	  	   (SELECT

E.ELEC_PYMT_FEE_ID,E.RQST_PYMT_PROC_DT,E.ALT_ELEC_PYMT_FEE_ID,E.UPDT_TS
		     FROM   ELEC_PYMT_FEE E, ELEC_PYMT PYMT
		     WHERE  E.ELEC_PYMT_FEE_ID = PYMT.ELEC_PYMT_FEE_ID (+)
		     AND    PYMT.ELEC_PYMT_FEE_ID IS NULL
			 AND	(E.UPDT_TS BETWEEN TO_DATE('&1','MM/DD/YYYY HH24:MI:SS') AND
TO_DATE('&2','MM/DD/YYYY HH24:MI:SS')- (3/24)


				 )

		     ) FEE , ELEC_PYMT P
       	 WHERE  FEE.alt_elec_pymt_fee_id = P.ELEC_PYMT_FEE_ID (+)

But when I run the MERGE command as a whole the value of column ABND_ELEC_PYMT_FEE_IN, always evaluates to a 'N'. Am I missing something ? Is this an issue of MERGE command ? Please help.

Thanks Received on Wed Aug 24 2005 - 15:03:35 CDT