Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    7

    Thumbs up Unanswered: [SOLVED] Merge >> Update ok | Insert not

    Hello,

    I'm currently struggeling with the merge command on a AIX DB2 9.7.
    All I want to achieve is write a command which makes and Update when there is already a row with the primary keys checked and if not, then perform the Insert statement.

    The Update statements works (I can see in my Toad) and of course in the database e.g. timestamp. But the Insert doesn't work.
    Threre no syntay error shown, but no insert is made. Toad shows "executed successfully".

    Any help is appreciated.

    Code:
    MERGE INTO DSPTMCP.KENNZAHL_DEFINITION as KD
    USING(Select NR_MANDANT, SL_GRUPPE_KENNZAHL, SL_KENNZAHL
    FROM DSPTMCP.KENNZAHL_DEFINITION
    WHERE NR_MANDANT = 5472 AND SL_GRUPPE_KENNZAHL = '_VBH' AND SL_KENNZAHL = 106) as KD1
    ON(KD.NR_MANDANT = KD1.NR_MANDANT AND KD.SL_GRUPPE_KENNZAHL = KD1.SL_GRUPPE_KENNZAHL AND KD.SL_KENNZAHL = KD1.SL_KENNZAHL)
    WHEN MATCHED Then
    UPDATE SET
    BEZ_KENNZAHL_ABS = 'Aufgaben',
    BEZ_KENNZAHL_REL = 'Aufgaben',
    BEZ_EINHEIT_ABS = '%',
    BEZ_EINHEIT_REL = '%',
    SL_MODIFIZIERUNG = 3,
    ANZ_NACHKOMMASTELLEN_ABS = 2,
    ANZ_NACHKOMMASTELLEN_REL = 2,
    KZ_QUALITAETSZIEL_ABS = 'H',
    KZ_QUALITAETSZIEL_REL = 'H',
    BEZ_ERMITTLUNGSFREQUENZ = 'Monatl.',
    BEZ_ERMITTLUNGSART = 'Automat',
    BEZ_DATENLIEFERANT = 'Geschäftsfelddaten',
    TXT_QUELLINFORMATION = 'Geschäftsfelddaten',
    TXT_KNZ_BESCHREIBUNG = 'Aufgaben',
    FAK_REF_GEWICHT = 1,
    KZ_HILFSGROESSE = 'N',
    SL_GRUPPE_KENNZAHL_REL = 'ALLG',
    SL_KENNZAHL_REL = 10,
    FAK_ERGEBNIS_REL = 1,
    BEZ_EINHEIT_QUELLE = '%',
    FAK_UMRECHNUNG_QUELLE = 1,
    KZ_REF_OHNE_VORZEICHEN = 'N'
    WHEN Not MATCHED Then
    INSERT (NR_MANDANT,
    SL_GRUPPE_KENNZAHL,
    SL_KENNZAHL,
    SYS_DWH_TSP,
    SL_MODIFIZIERUNG,
    UID_ERFASSUNG,
    TSP_ERFASSUNG,
    UID_AENDERUNG,
    TSP_AENDERUNG,
    BEZ_EINHEIT_ABS,
    ANZ_NACHKOMMASTELLEN_ABS,
    SL_GRUPPE_KENNZAHL_REF,
    SL_KENNZAHL_REF,
    KZ_REF_OHNE_VORZEICHEN,
    SL_REF_VERDICHTUNG,
    BEZ_KENNZAHL_ABS,
    BEZ_KENNZAHL_REL,
    KZ_HIERARCHIESUMME,
    KZ_QUALITAETSZIEL_ABS,
    KZ_QUALITAETSZIEL_REL,
    BEZ_ERMITTLUNGSFREQUENZ,
    BEZ_ERMITTLUNGSART,
    IHT_MINIMAL,
    IHT_MAXIMAL,
    IHT_MINIMAL_REL,
    IHT_MAXIMAL_REL,
    BEZ_DATENLIEFERANT,
    TXT_QUELLINFORMATION,
    DAT_ERFASSUNG_AB,
    DAT_ERFASSUNG_BIS,
    TXT_KNZ_BESCHREIBUNG,
    FAK_REF_GEWICHT,
    KZ_HILFSGROESSE,
    SL_GRUPPE_KENNZAHL_REL,
    SL_KENNZAHL_REL,
    FAK_ERGEBNIS_REL,
    BEZ_EINHEIT_QUELLE,
    FAK_UMRECHNUNG_QUELLE,
    BEZ_EINHEIT_REL,
    ANZ_NACHKOMMASTELLEN_REL)
    VALUES(5472,
    '_VBH',
    '104',
    current timestamp,
    3,
    'AUTOMAT',
    current timestamp,
    'AUTOMAT',
    current timestamp,
    '%',
    2,
    null,
    null,
    'N',
    null,
    'Aufgaben',
    'Aufgaben',
    'N',
    'H',
    'H',
    'Monatl.',
    'Automat',
    null,
    null,
    null,
    null,
    'Geschäftsfelddaten',
    'Geschäftsfelddaten',
    '01.01.2000',
    '31.12.9999',
    'Aufgaben',
    1,
    'N',
    'ALLG',
    '10',
    1,
    '%',
    1,
    '%',
    2);
    Last edited by Xenobiologist; 06-23-13 at 07:01. Reason: SOLVED

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see the description of USING table-reference in
    MERGE - IBM DB2 9.7 for Linux, UNIX, and Windows
    USING table-reference
    Specifies a set of rows as a result table to be merged into the target. If the result table is empty, a warning is returned (SQLSTATE 02000).
    The result of the table-reference shouldn't be empty.
    So, please try like this...
    Code:
    MERGE INTO DSPTMCP.KENNZAHL_DEFINITION as t/*arget*/
    USING (Select 5472 AS NR_MANDANT , '_VBH' AS SL_GRUPPE_KENNZAHL , 106 AS SL_KENNZAHL
            FROM  sysibm.sysdummy1
          ) as s/*ource*/
     ON   t.NR_MANDANT         = s.NR_MANDANT
      AND t.SL_GRUPPE_KENNZAHL = s.SL_GRUPPE_KENNZAHL
      AND t.SL_KENNZAHL        = s.SL_KENNZAHL
    WHEN MATCHED Then
    ...

  3. #3
    Join Date
    Jun 2011
    Posts
    7
    Thank you!
    It seems to work now.
    It is the first time I'm using the merge command.
    I didn't understand the syntax

    Thanks again!

    Mega

Posting Permissions

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