5 Replies Latest reply on Mar 28, 2013 2:48 AM by 999458

    Match Merge in OWB 11.2 how it's work?

    999458
      Hello!
      Faced with such a problem that the use "Match Merge" is no update, but always comes insert.
      I am using this statement I want to implement the following SQL:

      MERGE INTO dp_issue_card_es tbl
      USING (SELECT dt,ag,cnt_issue,cnt_replace FROM vw_dp_issue_card_es where dt is not null) vw
      ON (tbl.DT = vw.DT and tbl.ag=vw.ag )
      WHEN MATCHED THEN
      UPDATE SET tbl.cnt_issue = vw.cnt_issue, tbl.cnt_replace = vw.cnt_replace
      WHERE tbl.cnt_issue <> vw.cnt_issue
      or tbl.cnt_replace <> vw.cnt_replace
      WHEN NOT MATCHED THEN
      INSERT (tbl.DT,tbl.cnt_replace,tbl.cnt_issue,tbl.ag)
      VALUES (vw.DT, vw.cnt_replace,vw.cnt_issue,vw.ag);
      commit;
      Help me, please.
        • 1. Re: Match Merge in OWB 11.2 how it's work?
          778433
          Hi

          this statement is generated by OWB?

          If you use OWB and create a mapping in UPDATE/INSERT you can generate a merge statement and after you try MERGE.
          • 2. Re: Match Merge in OWB 11.2 how it's work?
            999458
            No. This sql is my.
            • 3. Re: Match Merge in OWB 11.2 how it's work?
              778433
              You can try to create a mapping where a Source Table is vw and target is tbl.
              On the TBL you configure UPDATE/INSERT.

              When all is ready you generate the SQL on the tbl and OWB creates the MERGE STATEMENT.

              You can't use your SQL in OWB but you have to do a MAPPING.

              Bye
              • 4. Re: Match Merge in OWB 11.2 how it's work?
                Cortanamo
                If you always get inserts then probably your select doesn't produce the existing PK value in the target table.
                Update will only happen if you supply existing PK.
                What does this question have to do with OWB anyway? It's general SQL.
                • 5. Re: Match Merge in OWB 11.2 how it's work?
                  999458
                  Thanks for the info, I almost got me some need, but OWB generates the following code:
                  MERGE
                  INTO
                  "DP_ISSUE_CARD_ES" "DP_ISSUE_CARD_ES"
                  USING
                  (SELECT
                  /* VW_DP_ISSUE_CARD_ES.INOUTGRP1 */
                  "VW_DP_ISSUE_CARD_ES"."DT" "DT",
                  "VW_DP_ISSUE_CARD_ES"."AG" "AG",
                  "VW_DP_ISSUE_CARD_ES"."CNT_ISSUE" "CNT_ISSUE",
                  "VW_DP_ISSUE_CARD_ES"."CNT_REPLACE" "CNT_REPLACE"
                  FROM
                  "VW_DP_ISSUE_CARD_ES" "VW_DP_ISSUE_CARD_ES"
                  )
                  "MERGE_SUBQUERY"
                  ON (
                  "DP_ISSUE_CARD_ES"."DT" = "MERGE_SUBQUERY"."DT" AND
                  "DP_ISSUE_CARD_ES"."AG" = "MERGE_SUBQUERY"."AG"
                  )

                  WHEN MATCHED THEN
                  UPDATE
                  SET
                  "CNT_ISSUE" = "MERGE_SUBQUERY"."CNT_ISSUE",
                  "CNT_REPLACE" = "MERGE_SUBQUERY"."CNT_REPLACE"

                  WHEN NOT MATCHED THEN
                  INSERT
                  ("DP_ISSUE_CARD_ES"."DT",
                  "DP_ISSUE_CARD_ES"."AG",
                  "DP_ISSUE_CARD_ES"."CNT_ISSUE",
                  "DP_ISSUE_CARD_ES"."CNT_REPLACE")
                  VALUES
                  ("MERGE_SUBQUERY"."DT",
                  "MERGE_SUBQUERY"."AG",
                  "MERGE_SUBQUERY"."CNT_ISSUE",
                  "MERGE_SUBQUERY"."CNT_REPLACE")

                  there are no conditions WHERE ( WHERE tbl.cnt_issue != vw.cnt_issue
                  or tbl.cnt_replace != vw.cnt_replace)
                  , and it must be if you can do something for this condition?

                  Edited by: 996455 on 27.03.2013 19:47

                  Edited by: 996455 on 27.03.2013 19:47

                  Edited by: 996455 on 27.03.2013 19:47

                  Edited by: 996455 on 27.03.2013 19:48