Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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, 2013Report 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, 2012Report This Post
Member
posted Hide Post
Thanks. This works fine.


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

Read-Only Read-Only Topic

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-2020 Information Builders