close MERGE statement in stored procedure

Hi all

I have the following SP:

MERGE INTO DBO.FM_ALERT t1
USING (select max(timestamp) as ts from  DBO.FM_ALERT where active=1 and MJID = IMJID and typeid=itypeid) t2
on
t1.TIMESTAMP=t2.ts and
t1.MJID=IMJID and t1.active=1
WHEN MATCHED THEN
       UPDATE SET t1.NEXTTIMESTAMP=TIMESTAMP(CDTWHEN);
ELSE
      INSERT INTO DBO.FM_ALERT (TIMESTAMP, nextTIMESTAMP, MJID,CHID,
            LOCALX, LOCALY, MESSAGE, DETAILS, ACTIVE, TYPEID)
      VALUES (CDTWHEN, CDTWHEN, IMJID,
            CCHID, ILOCALX, ILOCALY, CMESSAGE, CDETAILS, 1, ITYPEID);

There's a number of statements after that, but they all are only executed if WHEN MATCHED is ELSE.
I want them to be always executed, not depending on the outcome of MATCHED.

How? How do I "close" the "WHEN MATCHED THEN" - "ELSE"?

All help is appreciated, thank you.
darrgyasAsked:
Who is Participating?
 
Kent OlsenData Warehouse Architect / DBACommented:
Parentheses around the join condition (ON clause) are not required in DB2 or SQL Server.

However, the affected table is declared in the initial MERGE INTO xxx clause.  That is the ONLY place where the insert can occur.  Remove the reference to the table name from from the INSERT statement (as noted earlier).


Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Howdy...


'ELSE' should be 'WHEN NOT MATCHED'


Otherwise, it looks pretty good.  :)


Kent
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<somewhat redundant>

WHEN MATCHED
WHEN NOT MATCHED
WHEN NOT MATCHED BY SOURCE

ELSE no worky in MERGE statements..
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
remove the ";" before the ELSE... ?
MERGE INTO DBO.FM_ALERT t1
USING (select max(timestamp) as ts from  DBO.FM_ALERT where active=1 and MJID = IMJID and typeid=itypeid) t2
on
t1.TIMESTAMP=t2.ts and
t1.MJID=IMJID and t1.active=1
WHEN MATCHED THEN
       UPDATE SET t1.NEXTTIMESTAMP=TIMESTAMP(CDTWHEN)
ELSE
      INSERT INTO DBO.FM_ALERT (TIMESTAMP, nextTIMESTAMP, MJID,CHID,
            LOCALX, LOCALY, MESSAGE, DETAILS, ACTIVE, TYPEID)
      VALUES (CDTWHEN, CDTWHEN, IMJID,
            CCHID, ILOCALX, ILOCALY, CMESSAGE, CDETAILS, 1, ITYPEID);

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and indeed, as jimhorn indicates, it's not ELSE, but WHEN NOT MATCHED ...
I presume you have added the ";" to fix the syntax issue

MERGE INTO DBO.FM_ALERT t1
USING (select max(timestamp) as ts from  DBO.FM_ALERT where active=1 and MJID = IMJID and typeid=itypeid) t2
on
t1.TIMESTAMP=t2.ts and
t1.MJID=IMJID and t1.active=1
WHEN MATCHED THEN
       UPDATE SET t1.NEXTTIMESTAMP=TIMESTAMP(CDTWHEN)
WHEN NOT MATCHED THEN
      INSERT INTO DBO.FM_ALERT (TIMESTAMP, nextTIMESTAMP, MJID,CHID,
            LOCALX, LOCALY, MESSAGE, DETAILS, ACTIVE, TYPEID)
      VALUES (CDTWHEN, CDTWHEN, IMJID,
            CCHID, ILOCALX, ILOCALY, CMESSAGE, CDETAILS, 1, ITYPEID);

Open in new window

0
 
darrgyasAuthor Commented:
Changing the statement to this:

MERGE INTO DBO.FM_ALERT t1
USING (select max(timestamp) as ts from  DBO.FM_ALERT where active=1 and MJID = IMJID and typeid=itypeid) t2
on
t1.TIMESTAMP=t2.ts and
t1.MJID=IMJID and t1.active=1
WHEN MATCHED THEN
       UPDATE SET t1.NEXTTIMESTAMP=TIMESTAMP(CDTWHEN)
WHEN NOT MATCHED THEN
      INSERT INTO DBO.FM_ALERT (TIMESTAMP, nextTIMESTAMP, MJID,CHID,
            LOCALX, LOCALY, MESSAGE, DETAILS, ACTIVE, TYPEID)
      VALUES (CDTWHEN, CDTWHEN, IMJID,
            CCHID, ILOCALX, ILOCALY, CMESSAGE, CDETAILS, 1, ITYPEID);

returns the following error:

ERROR: A character, token, or clause is invalid or missing.

DB2
SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=INTO;MATCHED
THEN
      INSERT;<merge_values>, DRIVER=3.57.82
Error Code:
-104
0
 
Kent OlsenData Warehouse Architect / DBACommented:
The table name in the INSERT clause is redundant


WHEN NOT MATCHED THEN
      INSERT (TIMESTAMP, nextTIMESTAMP, MJID,CHID,
            LOCALX, LOCALY, MESSAGE, DETAILS, ACTIVE, TYPEID)
      VALUES (CDTWHEN, CDTWHEN, IMJID,
            CCHID, ILOCALX, ILOCALY, CMESSAGE, CDETAILS, 1, ITYPEID);
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please use () around the ON condition.
also:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0010873.htm
expression
    Indicates the new value of the column. The expression must not include a column function (SQLSTATE 42903).  

=> TIMESTAMP(CDTWHEN) is invalid, please fix that as needed.


MERGE INTO DBO.FM_ALERT t1
USING (select max(timestamp) as ts from  DBO.FM_ALERT where active=1 and MJID = IMJID and typeid=itypeid) t2
on ( t1.TIMESTAMP=t2.ts and t1.MJID=IMJID and t1.active=1 )
WHEN MATCHED THEN
       UPDATE SET t1.NEXTTIMESTAMP=TIMESTAMP(CDTWHEN)
WHEN NOT MATCHED THEN
      INSERT INTO DBO.FM_ALERT (TIMESTAMP, nextTIMESTAMP, MJID,CHID,
            LOCALX, LOCALY, MESSAGE, DETAILS, ACTIVE, TYPEID)
      VALUES (CDTWHEN, CDTWHEN, IMJID,
            CCHID, ILOCALX, ILOCALY, CMESSAGE, CDETAILS, 1, ITYPEID);

Open in new window

0
 
darrgyasAuthor Commented:
Thank you all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.