Re: MERGE COMMAND WITH DECODE AND INNER SELECT

Comments embedded.
srini wrote:
> I have the below MERGE command with a decode function as part of the
> select clause.Irrespective of the data,

And, apparently, the version of Oracle you're using. Without such information your question is unanswerable as only pure speculation is available as a diagnostic tool.

> 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

You're missing MUCH, including the Oracle release, patch level and operating system you're running. Provide this information and help may be forthcoming. Do not expect people to respond to incomplete questions. Also do NOT use all capitals in your message title. You may think that it implies urgency; we find it annoying.

David Fitzjarrell Received on Wed Aug 24 2005 - 15:29:26 CDT