• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 881
  • Last Modified:

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.
0
darrgyas
Asked:
darrgyas
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
 
darrgyasAuthor Commented:
Thank you all
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now