Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    34

    Unanswered: Error in MERGE Statament

    I have a stored procedure which populates target table from source table
    Now i have to modify that stored procedure according to my requirment.
    My requirement:

    I have added a new column called INTGRT_SRC_TRX_CD to source table and that column will have values like 'D', 'I', 'U'. That column is not there in target table and i cant even map that column to any other column in target table.
    Now according to the value of that column i have to update or insert into the target table.
    So whenever INTGRT_SRC_TRX_CD = 'D' then i have to keep all target column values except 4 columns which are
    1. DWT00007_IMC_DMS_CNTAC.PRM_ADR_LN_1
    2. DWT00007_IMC_DMS_CNTAC.PRM_ADR_LN_2
    3. DWT00007_IMC_DMS_CNTAC.PRM_ADR_LN_3
    4. DWT00007_IMC_DMS_CNTAC.PRM_ADR_LN_4

    for those 4 columns i have to write something like this
    case when INTGRT_SRC_TRX_CD = 'D' THEN target.column ELSE stage.column

    In merge statement while only updating we need not select INTGRT_SRC_TRX_CD in the code from the source i hope.
    But problem comes while inserting, since there is no column in target for the INTGRT_SRC_TRX_CD column to be mapped.
    I am unable to find a solution for this

    MERGE /*+ PARALLEL APPEND */
    INTO DWT00007_IMC_DMS_CNTAC_WRK WRK
    USING (SELECT IMC_KEY_NO,
    inmkt_prm_st_prov_cd,
    inmkt_prm_st_prov_desc,
    inmkt_prm_postl_cd,
    inmkt_prm_adr_cntry_cd,
    inmkt_prm_adr_cntry_nm,
    inmkt_prm_city_nm,
    INMKT_PRM_ADR_1_DESC,
    INMKT_PRM_ADR_2_DESC,
    INMKT_PRM_ADR_3_DESC,
    INMKT_PRM_ADR_4_DESC
    FROM DWSSTG01.awt20020_dms_adr
    WHERE TABLE_NO = 1) STAGE
    ON (WRK.IMC_KEY_NO = STAGE.IMC_KEY_NO)
    WHEN MATCHED
    THEN
    UPDATE SET
    WRK.PRM_STATE_CD = STAGE.inmkt_prm_st_prov_cd,
    WRK.PRM_STATE = STAGE.inmkt_prm_st_prov_cd,
    WRK.PRM_POST_CODE = STAGE.inmkt_prm_postl_cd,
    WRK.PRM_COUNTRY_CD = STAGE.inmkt_prm_adr_cntry_cd,
    WRK.prm_country = STAGE.inmkt_prm_adr_cntry_nm,
    WRK.prm_city = STAGE.inmkt_prm_city_nm,
    WRK.PRM_ADR_LN_1 = (CASE WHEN (SELECT INTGRT_SRC_TRX_CD FROM DWSSTG01.awt20020_dms_adr stage) = 'D' THEN WRK.PRM_ADR_LN_1 ELSE STAGE.INMKT_PRM_ADR_1_DESC END),
    WRK.PRM_ADR_LN_2 = (CASE WHEN (SELECT INTGRT_SRC_TRX_CD FROM DWSSTG01.awt20020_dms_adr stage) = 'D' THEN WRK.PRM_ADR_LN_2 ELSE STAGE.INMKT_PRM_ADR_2_DESC END),
    WRK.PRM_ADR_LN_3 = (CASE WHEN (SELECT INTGRT_SRC_TRX_CD FROM DWSSTG01.awt20020_dms_adr stage) = 'D' THEN WRK.PRM_ADR_LN_3 ELSE STAGE.INMKT_PRM_ADR_3_DESC END),
    WRK.PRM_ADR_LN_4 = (CASE WHEN (SELECT INTGRT_SRC_TRX_CD FROM DWSSTG01.awt20020_dms_adr stage) = 'D' THEN WRK.PRM_ADR_LN_4 ELSE STAGE.INMKT_PRM_ADR_4_DESC END)
    WHEN NOT MATCHED
    THEN
    INSERT (WRK.IMC_KEY_NO,
    WRK.PRM_STATE_CD,
    WRK.PRM_STATE,
    WRK.PRM_POST_CODE,
    WRK.PRM_COUNTRY_CD,
    WRK.prm_country,
    WRK.prm_city,
    WRK.PRM_ADR_LN_1,
    WRK.PRM_ADR_LN_2,
    WRK.PRM_ADR_LN_3,
    WRK.PRM_ADR_LN_4)
    VALUES (STAGE.IMC_KEY_NO,
    STAGE.inmkt_prm_st_prov_cd,
    STAGE.inmkt_prm_st_prov_desc,
    STAGE.inmkt_prm_postl_cd,
    STAGE.inmkt_prm_adr_cntry_cd,
    STAGE.inmkt_prm_adr_cntry_nm,
    STAGE.inmkt_prm_city_nm,
    CASE WHEN (SELECT INTGRT_SRC_TRX_CD FROM DWSSTG01.awt20020_dms_adr stage) = 'D' THEN WRK.PRM_ADR_LN_1 ELSE STAGE.INMKT_PRM_ADR_1_DESC END,
    CASE WHEN (SELECT INTGRT_SRC_TRX_CD FROM DWSSTG01.awt20020_dms_adr stage) = 'D' THEN WRK.PRM_ADR_LN_2 ELSE STAGE.INMKT_PRM_ADR_2_DESC END,
    CASE WHEN (SELECT INTGRT_SRC_TRX_CD FROM DWSSTG01.awt20020_dms_adr stage) = 'D' THEN WRK.PRM_ADR_LN_3 ELSE STAGE.INMKT_PRM_ADR_3_DESC END,
    CASE WHEN (SELECT INTGRT_SRC_TRX_CD FROM DWSSTG01.awt20020_dms_adr stage) = 'D' THEN WRK.PRM_ADR_LN_4 ELSE STAGE.INMKT_PRM_ADR_4_DESC END);

    Even though i am getting error
    Error report:
    SQL Error: ORA-38101: Invalid column in the INSERT VALUES Clause: "WRK"."PRM_ADR_LN_1"
    38101. 00000 - "Invalid column in the INSERT VALUES Clause: %s"
    *Cause: INSERT VALUES clause refers to the destination table columns
    *Action:

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool No way josey...

    Quote Originally Posted by rahulsony111 View Post
    ... Etc ...
    Even though i am getting error
    Error report:
    SQL Error: ORA-38101: Invalid column in the INSERT VALUES Clause: "WRK"."PRM_ADR_LN_1"
    38101. 00000 - "Invalid column in the INSERT VALUES Clause: %s"
    *Cause: INSERT VALUES clause refers to the destination table columns
    *Action:
    What did you not undestand about "INSERT VALUES clause refers to the destination table columns"?
    You cannot refer to the destination table columns in the VALUES clause of an INSERT.

    You should better try something like this:
    Code:
    MERGE
    /*+ PARALLEL APPEND */
    INTO DWT00007_IMC_DMS_CNTAC_WRK WRK USING
    (SELECT
      IMC_KEY_NO
    , inmkt_prm_st_prov_cd
    , inmkt_prm_st_prov_desc
    , inmkt_prm_postl_cd
    , inmkt_prm_adr_cntry_cd
    , inmkt_prm_adr_cntry_nm
    , inmkt_prm_city_nm
    , INMKT_PRM_ADR_1_DESC
    , INMKT_PRM_ADR_2_DESC
    , INMKT_PRM_ADR_3_DESC
    , INMKT_PRM_ADR_4_DESC
       FROM
      DWSSTG01.awt20020_dms_adr
      WHERE
      TABLE_NO                 = 1
    ) STAGE ON (WRK.IMC_KEY_NO = STAGE.IMC_KEY_NO)
    WHEN MATCHED THEN
       UPDATE
      SET
        WRK.PRM_STATE_CD   = STAGE.inmkt_prm_st_prov_cd
      , WRK.PRM_STATE      = STAGE.inmkt_prm_st_prov_cd
      , WRK.PRM_POST_CODE  = STAGE.inmkt_prm_postl_cd
      , WRK.PRM_COUNTRY_CD = STAGE.inmkt_prm_adr_cntry_cd
      , WRK.prm_country    = STAGE.inmkt_prm_adr_cntry_nm
      , WRK.prm_city       = STAGE.inmkt_prm_city_nm
      , WRK.PRM_ADR_LN_1   = 
        DECODE(STAGE.INTGRT_SRC_TRX_CD,'D',WRK.PRM_ADR_LN_1,STAGE.INMKT_PRM_ADR_1_DESC)
      , WRK.PRM_ADR_LN_2 = 
        DECODE(STAGE.INTGRT_SRC_TRX_CD,'D',WRK.PRM_ADR_LN_2,STAGE.INMKT_PRM_ADR_2_DESC)
      , WRK.PRM_ADR_LN_3 = 
        DECODE(STAGE.INTGRT_SRC_TRX_CD,'D',WRK.PRM_ADR_LN_3,STAGE.INMKT_PRM_ADR_3_DESC)
      , WRK.PRM_ADR_LN_4 = (
        DECODE(STAGE.INTGRT_SRC_TRX_CD,'D',WRK.PRM_ADR_LN_4,STAGE.INMKT_PRM_ADR_4_DESC)
      WHEN NOT MATCHED THEN
       INSERT
        (
          WRK.IMC_KEY_NO
        , WRK.PRM_STATE_CD
        , WRK.PRM_STATE
        , WRK.PRM_POST_CODE
        , WRK.PRM_COUNTRY_CD
        , WRK.prm_country
        , WRK.prm_city
        , WRK.PRM_ADR_LN_1
        , WRK.PRM_ADR_LN_2
        , WRK.PRM_ADR_LN_3
        , WRK.PRM_ADR_LN_4
        )
        VALUES
        (
          STAGE.IMC_KEY_NO
        , STAGE.inmkt_prm_st_prov_cd
        , STAGE.inmkt_prm_st_prov_desc
        , STAGE.inmkt_prm_postl_cd
        , STAGE.inmkt_prm_adr_cntry_cd
        , STAGE.inmkt_prm_adr_cntry_nm
        , STAGE.inmkt_prm_city_nm
        , DECODE(STAGE.INTGRT_SRC_TRX_CD,'D','N/A',STAGE.INMKT_PRM_ADR_1_DESC)
        , DECODE(STAGE.INTGRT_SRC_TRX_CD,'D','N/A',STAGE.INMKT_PRM_ADR_2_DESC)
        , DECODE(STAGE.INTGRT_SRC_TRX_CD,'D','N/A',STAGE.INMKT_PRM_ADR_3_DESC)
        , DECODE(STAGE.INTGRT_SRC_TRX_CD,'D','N/A',STAGE.INMKT_PRM_ADR_4_DESC)
        );
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Nov 2009
    Posts
    34
    I tried the code you have, but i am getting error

    Error at Command Line:65 Column:13
    Error report:
    SQL Error: ORA-00904: "STAGE"."INTGRT_SRC_TRX_CD": invalid identifier
    00904. 00000 - "%s: invalid identifier"
    *Cause:
    *Action:

    I think the error is because of not selecting INTGRT_SRC_TRX_CD.
    But no clue what to do
    I cannot select that code in the select statement
    If i select i have to use that in insert also, but i dont have any column in target table to map to that column and i should not do that

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    ? what ?

    Select the darn column. It doesn't matter if there is a column to map to. But I think you are missing the trial piece of trial and error.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down Duh!

    Quote Originally Posted by rahulsony111 View Post
    I think the error is because of not selecting INTGRT_SRC_TRX_CD.
    But no clue what to do
    I cannot select that code in the select statement
    DUH! You need to select that column in the 'MERGE...USING (SELECT..." statement even if it does not map to anything in the target table.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •