Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] Using MERGE n a DM procedure
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Using MERGE n a DM procedure
 Login/Join
 
Member
posted
I'm trying to run the following the following MERGE statement through a DM procedure and it errors indicating that MERGE statements require a semi-colon (this syntax is required by SLQ Server). However, DM does not accept semi-colon.

DOes anyone know of any workarounds for running SQL MERGE statements


MERGE INTO d_calendar t
USING
(
SELECT c.definition_id,
CONVERT(CHAR(4),DATEPART(yyyy,current_timestamp))+RIGHT(REPLICATE('0',2)+ CAST(DATEPART(mm,current_timestamp) AS VARCHAR(2)),2)+RIGHT(REPLICATE('0',2)+ CAST(DATEPART(d,current_timestamp) AS VARCHAR(2)),2) curr_datestring,
RIGHT(REPLICATE('0',2)+ CAST(DATEPART(mm,current_timestamp) AS VARCHAR(2)),2) curr_month,
CAST(DATEPART(q,current_timestamp) AS CHAR(1)) curr_quarter,
CONVERT(CHAR(4),DATEPART(yyyy,current_timestamp)) curr_year,
DATENAME(dy,current_timestamp) curr_running_day_cnt,
DATEDIFF(m,sdate, current_timestamp) months_ago,
DATEDIFF(d,sdate, current_timestamp) days_ago,
CASE WHEN
(CONVERT(CHAR(4),DATEPART(yyyy,current_timestamp))+RIGHT(REPLICATE('0',2)+ CAST(DATEPART(mm,current_timestamp) AS VARCHAR(2)),2)) = SUBSTRING(CONVERT(CHAR(8),datestring),1,6)
THEN
'Current Month'
WHEN
CONVERT(CHAR(4),DATEPART(yyyy,DATEADD(mm,-1,current_timestamp))) +RIGHT(REPLICATE('0',2)+ CAST(DATEPART(mm,DATEADD(mm,-1,current_timestamp)) AS VARCHAR(2)),2) = SUBSTRING(CONVERT(CHAR(8),datestring),1,6)
THEN
'Last Month'
WHEN datestring < CONVERT[CHAR[4),DATEPART[yyyy,current_timestamp))+RIGHT[REPLICATE['0',2)+ CAST[DATEPART[mm,current_timestamp) AS VARCHAR[2)),2)+RIGHT[REPLICATE['0',2)+ CAST[DATEPART[d,current_timestamp) AS VARCHAR[2)),2)
THEN
CONVERT(VARCHAR(4),DATEDIFF(m,sdate, current_timestamp)) + ' Months Ago'
ELSE
null
END relative_month_desc
FROM d_calendar c
) s
ON (s.definition_id = t.definition_id)
WHEN MATCHED THEN
UPDATE SET
t.curr_datestring = s.curr_datestring,
t.curr_month = s.curr_month,
t.curr_quarter = s.curr_quarter,
t.curr_year = s.curr_year,
t.curr_running_day_cnt = s.curr_running_day_cnt,
t.relative_month_desc = s.relative_month_desc,
t.months_ago = s.months_ago,
t.days_ago = s.days_ago


END


-EXIT

This message has been edited. Last edited by: <Kathryn Henning>,


Webfocus 8.0
HTML
 
Posts: 20 | Location: Guelph, Ontario | Registered: May 17, 2013Reply With QuoteReport This Post
Member
posted Hide Post
My best advise is to wrap this Merge statment into a sql procedure and execute the procedure

same time sql procedure is is pre compiled and cached will also give u better performance on sql server side

you can also use output caluse to get modified data and report in the procedure

ENGINE SQL DEFAULT_CONNECTION NAME
SQL SQLMSS EX procname

END


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 13 | Registered: March 06, 2012Reply With QuoteReport This Post
Member
posted Hide Post
Thanks. This works fine.


Webfocus 8.0
HTML
 
Posts: 20 | Location: Guelph, Ontario | Registered: May 17, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] Using MERGE n a DM procedure

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.