February 3, 2011 at 8:23 am
Hello Folks,
Am a beginner in Tsql and I have a requirement to write a stored procedure and am all confused how to start and unable to make a headway.
Usually i have this query which i run every month and get the data into Excel and i just use the Sql Server Import and Export wizard using BIDS and dump the data into SQL server Table. The query is nothing but retreiving the clientcode and employergroup for the previous month from the summary table in Oracle.
I got the requirement to automate this thing of manually running the Import and Export Wizard for every client every month which takes lot of time.
So i have the requirement of creating a Stored procedure in sql server to get the data from oracle. Can anyone please help me out on how to start?? The query is below.
select '801' report_no, -- i have to create this for the reports 801,8,88,802,803
null CLIENT_ID,
r.payor_key CLIENT_CODE,
null CLIENT_NAME,
r.payor_key legacy_client_code,
null major_client_name,
null major_client_code,
null end_user_id,
null user_name,
null employer_group_id,
replace(r.group_ref_key, '/', ' ') employer_group_code,
translate(substr(group_name,
instr(group_name, '-') + 1,
length(group_name)),
'/\',
' ') employer_group_name,
'{R08_SUMMARY.PAYOR_KEY} = ' || '"' || r.payor_key || '"' ||
' and {R08_SUMMARY.GROUP_NAME} = "' || r.group_name || '"' selform,
null as path, --
null email,
'A' status,
'EMPGRPWISE CLIENT BO' burst_type,
'PDF' burst_file_type,
'-1' id,
null bouser,
'G' std_rpt_burst
from summary r
where r.month_id = '201012' --i hav to include the logic to get the data every month i have to change this month
and r.payor_key IN ('HM') -- i have to include the logic to create this for various clients such as HM,BM,CM etc
and r.print_flag = 'Y'
February 4, 2011 at 1:05 pm
We end up pulling data from Oracle quite often. SQL Server can create Linked Servers To Oracle, and you can also use OPENQUERY to pull data across.
Here are some starting point links:
February 7, 2011 at 10:00 am
Hi there Thanks for getting back. The links reaaly helped me a lot. Am actually was able to execute the procedure with some values. But am having difficulty in selecting all the reportnumbers and all the clients.
please see the errors below
Msg 156, Level 15, State 1, Procedure pGetReportData, Line 12
Incorrect syntax near the keyword 'declare'.
Msg 102, Level 15, State 1, Procedure pGetReportData, Line 15
Incorrect syntax near '801'.
Msg 156, Level 15, State 1, Procedure pGetReportData, Line 87
Incorrect syntax near the keyword 'ELSE'.
ALTER PROCEDURE [dbo].[pGetReportData]
(@ReportNo varchar(10),
@monthid varchar(20),
@payorkey varchar(10)) AS
declare @reportqry varchar(2000),
declare @rpt varchar(300)
If ((@rpt = ''''801'''') or (@rpt = ''''8'''') or (@rpt = ''''802'''') or (@rpt = ''''803''''))
Begin
set @reportqry = 'select * into AposGrpwise from openquery(EOM,''select ''''' +
@ReportNo + ''''' as report_no,
r.payor_key as CLIENT_CODE,
null CLIENT_NAME,
r.payor_key as legacy_client_code,
null major_client_name,
null major_client_code,
null end_user_id,
null user_name,
null employer_group_id,
replace(r.group_ref_key, ''''/'''', '''' '''') as employer_group_code,
translate(substr(group_name,
instr(group_name, ''''-'''') + 1,
length(group_name)),
''''/\'''',
'''' '''') as employer_group_name,
null branch_group_id,
null branch_group_code,
null branch_group_name,
''''{R08_SUMMARY.PAYOR_KEY} = '''' || ''''"'''' || r.payor_key || ''''"'''' ||
'''' and {R08_SUMMARY.GROUP_NAME} = "'''' || r.group_name || ''''"'''' as selform,
null path,
null email,
''''A'''' status,
''''EMPGRPWISE CLIENT BO'''' burst_type,
''''PDF'''' burst_file_type,
''''-1'''' id,
null bouser,
''''G'''' std_rpt_burst
from r08_summary1 r
where r.month_id = '''''+ @monthid + '''''
and r.payor_key = '''''+ @payorkey + '''''
and r.print_flag = ''''Y'''''')'
END
ELSE
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'AposGrpwise') AND type = (N'U'))
DROP TABLE AposGrpwise
set @reportqry = 'select * into AposGrpwise from openquery(EOM,''select ''''' +
@ReportNo + ''''' as report_no,
r.payor_key as CLIENT_CODE,
null CLIENT_NAME,
r.payor_key as legacy_client_code,
null major_client_name,
null major_client_code,
null end_user_id,
null user_name,
null employer_group_id,
replace(r.group_ref_key, ''''/'''', '''' '''') as employer_group_code,
translate(substr(group_name,
instr(group_name, ''''-'''') + 1,
length(group_name)),
''''/\'''',
'''' '''') as employer_group_name,
null branch_group_id,
null branch_group_code,
null branch_group_name,
''''{R08_SUMMARY.PAYOR_KEY} = '''' || ''''"'''' || r.payor_key || ''''"'''' ||
'''' and {R08_SUMMARY.GROUP_NAME} = "'''' || r.group_name || ''''"'''' as selform,
null path,
null email,
''''A'''' status,
''''EMPGRPWISE CLIENT BO'''' burst_type,
''''PDF'''' burst_file_type,
''''-1'''' id,
null bouser,
''''G'''' std_rpt_burst
from r08_summary1 r
where r.month_id = '''''+ @monthid + '''''
and r.payor_key = '''''+ @payorkey + '''''
and r.print_flag = ''''Y'''''')'
exec(@reportqry)
February 7, 2011 at 10:34 am
hnaveen84 (2/7/2011)
Hi there Thanks for getting back. The links reaaly helped me a lot. Am actually was able to execute the procedure with some values. But am having difficulty in selecting all the reportnumbers and all the clients.please see the errors below
Msg 156, Level 15, State 1, Procedure pGetReportData, Line 12
Incorrect syntax near the keyword 'declare'.
declare @reportqry varchar(2000),
declare @rpt varchar(300)
The first syntax error is because you either need to get rid of the second declare or the comma. Either one by itself would work fine.
The second error looks like a quoting error. If the value you're looking for is '801' then you'd use 3 quotes, not 4. If it's just 801, then you'd only use 1 set of quotes (or none if it's actually a number value and not a string).
February 7, 2011 at 11:24 am
Hi seth you da man. After following your instructins i was able to nail down on the errors. but i still cannot find the logic for looking into all payors i.e if @payorkey is null get all the payors and @reportno is null get all the reports data somehow i get the error at the ELSE part. Any ideas on this please
Error:
Msg 156, Level 15, State 1, Procedure pGetReportData, Line 84
Incorrect syntax near the keyword 'Else'.
ALTER PROCEDURE [dbo].[pGetReportData]
(@ReportNo varchar(10),
@monthid varchar(20),
@payorkey varchar(10)) AS
declare @reportqry varchar(2000)
declare @rpt varchar(300)
If ((@rpt = '801') or (@rpt = '8') or (@rpt = '802') or (@rpt = '803'))
Begin
set @reportqry = 'select * into AposGrpwise from openquery(EOM,''select ''''' +
@ReportNo + ''''' as report_no,
r.payor_key as CLIENT_CODE,
null CLIENT_NAME,
r.payor_key as legacy_client_code,
null major_client_name,
null major_client_code,
null end_user_id,
null user_name,
null employer_group_id,
replace(r.group_ref_key, ''''/'''', '''' '''') as employer_group_code,
translate(substr(group_name,
instr(group_name, ''''-'''') + 1,
length(group_name)),
''''/\'''',
'''' '''') as employer_group_name,
null branch_group_id,
null branch_group_code,
null branch_group_name,
''''{R08_SUMMARY.PAYOR_KEY} = '''' || ''''"'''' || r.payor_key || ''''"'''' ||
'''' and {R08_SUMMARY.GROUP_NAME} = "'''' || r.group_name || ''''"'''' as selform,
null path,
null email,
''''A'''' status,
''''EMPGRPWISE CLIENT BO'''' burst_type,
''''PDF'''' burst_file_type,
''''-1'''' id,
null bouser,
''''G'''' std_rpt_burst
from r08_summary1 r
where r.month_id = '''''+ @monthid + '''''
and r.payor_key = '''''+ @payorkey + '''''
and r.print_flag = ''''Y'''''')'
Else (--For Null)
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'AposGrpwise') AND type = (N'U'))
DROP TABLE AposGrpwise
set @reportqry = 'select * into AposGrpwise from openquery(EOM,''select ''''' +
@ReportNo + ''''' as report_no,
r.payor_key as CLIENT_CODE,
null CLIENT_NAME,
r.payor_key as legacy_client_code,
null major_client_name,
null major_client_code,
null end_user_id,
null user_name,
null employer_group_id,
replace(r.group_ref_key, ''''/'''', '''' '''') as employer_group_code,
translate(substr(group_name,
instr(group_name, ''''-'''') + 1,
length(group_name)),
''''/\'''',
'''' '''') as employer_group_name,
null branch_group_id,
null branch_group_code,
null branch_group_name,
''''{R08_SUMMARY.PAYOR_KEY} = '''' || ''''"'''' || r.payor_key || ''''"'''' ||
'''' and {R08_SUMMARY.GROUP_NAME} = "'''' || r.group_name || ''''"'''' as selform,
null path,
null email,
''''A'''' status,
''''EMPGRPWISE CLIENT BO'''' burst_type,
''''PDF'''' burst_file_type,
''''-1'''' id,
null bouser,
''''G'''' std_rpt_burst
from r08_summary1 r
where r.month_id = '''''+ @monthid + '''''
and r.payor_key = '''''+ @payorkey + '''''
and r.print_flag = ''''Y'''''')'
End
exec(@reportqry)
February 7, 2011 at 12:19 pm
February 7, 2011 at 1:16 pm
Thanks for getting back again. Okay i think i should be more clearer when explaining this stuff. The procedure is executing now but it is not pulling any data.
My question is how will the @rpt passed to the @ReportNo of the select stament inside the openquery??
If ((@rpt = '801') or (@rpt = '8') or (@rpt = '802') or (@rpt = '803'))
set @reportqry = 'select * into AposGrpwise from openquery(EOM,''select ''''' +
@ReportNo + ''''' as report_no,
February 7, 2011 at 1:37 pm
Honestly, I haven't been sanity checking that long select statement of yours, I've just been answering your syntax error questions. Simplify things first. Do just a select of the top 25 of the table to make sure you're getting the data properly.
Something like:
SELECT *
FROM OPENQUERY(EOM, 'SELECT * FROM r08_summary1 WHERE rownum <= 25')
Then gradually add things to it until it breaks and you'll figure out where your problem is.
If there's not a lot of data in the table, you might actually simplify things immensely by either A: Selecting all of the data across to SQL and then working with it, or applying just the filters that you send across to oracle and then do the rest of it on the SQL side.
February 7, 2011 at 3:16 pm
Hi seth, I have tried the query with rownum and it has data in it.So i dont think am having any problem with that. Only problem i think now is I have to figure out how to pass in multiple variables to a linked query server?
i.e. i have to have the values select @ReportNo = '802','8','802' etc etc. How to acheive that?? If i sayselect @ReportNo = '802' then it works fine but not with multiple values
February 7, 2011 at 7:42 pm
What are you expecting this to do?
declare @reportqry varchar(2000)
declare @rpt varchar(300)
If ((@rpt = '801') or (@rpt = '8') or (@rpt = '802') or (@rpt = '803'))
You're declaring a variable (which will make it NULL) and then immediately checking it for values, which it will never have. Are ReportNo and Rpt supposed to be the same variable that you're accidentally changing the names of?
Also, you can't say (at least in SQL, I'm not sure if Oracle follows the same rules, but I'd imagine it does) If @ReportNo = '802','8'...etc. because that's looking for a report number that is literally '802,8...etc. and not any one of those values. You need to use IN instead. So it'd be If @ReportNo IN ('802','8'...).
Also, all you're doing with that 'IF' statement (assuming you were checking the proper variable with it) is deciding which select statement to execute. You're not actually filtering by it. You need to put it in the where clause of your select statement as well if you intend to filter by it.
February 8, 2011 at 8:25 am
Hi seth Tahnks for your patience. Well actually i dont have the report number in the SUmmary Table in the Oracle. So am trying to add the report numbers dynamically to the query. So thats the reason am saying If (@rpt = '801') or (@rpt = '8').
I want to add the report numbers to the data that i get from the oracle. How can we achieve that? I think am just doing a trail and error and writing the code. WOuld it be easy if you can write a sample code which has the above criteria matching that would be greatful
February 8, 2011 at 11:11 am
I can't write you sample code because I'm honestly unclear on what you're trying to do still. If report number isn't in the oracle table, then how is it tying into this at all?
Right now your code is checking report number and then executing one of two select statements based on the outcome. However, those select statements are exactly the same, and reportnumber isn't in the table you're pulling data from. So... what is the difference between one of those report numbers and anything else as far as the data you pull out?
February 8, 2011 at 12:42 pm
I think i have the wrong code posted. Please check the code below.
My whole requirement is dump the data from the oracle which doesnt have report numbers into the sql server. How can we add report numbers(801,802,803) to the the data am getting here. Please ignore all the earlier statements i guess i dint explained it clearly.
ALTER PROCEDURE [dbo].[pGetReportData]
(@ReportNo varchar(10),
@monthid varchar(20),
@payorkey varchar(10)
) AS
declare @reportqry varchar(2000)
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'AposGrpwise') AND type = (N'U'))
DROP TABLE AposGrpwise
select @monthid = convert(varchar(4),datepart(year,dateadd(month,-1,getdate())))+
isnull(replicate('0',(2-datalength(ltrim(convert(varchar(4),datepart(month,dateadd(month,-1,getdate()))))))),'') +
ltrim(convert(varchar(4),datepart(month,dateadd(month,-1,getdate()))))
set @reportqry = 'select * into AposGrpwise from openquery(EOM,''select ''''' +
@ReportNo + ''''' as report_no,
null client_id,
r.payor_key as CLIENT_CODE,
null CLIENT_NAME,
r.payor_key as legacy_client_code,
null major_client_name,
null major_client_code,
null end_user_id,
null user_name,
null employer_group_id,
replace(r.group_ref_key, ''''/'''', '''' '''') as employer_group_code,
translate(substr(group_name,
instr(group_name, ''''-'''') + 1,
length(group_name)),
''''/\'''',
'''' '''') as employer_group_name,
null branch_group_id,
null branch_group_code,
null branch_group_name,
''''{R08_SUMMARY.PAYOR_KEY} = '''' || ''''"'''' || r.payor_key || ''''"'''' ||
'''' and {R08_SUMMARY.GROUP_NAME} = "'''' || r.group_name || ''''"'''' as selform,
null path,
null email,
''''A'''' status,
''''EMPGRPWISE CLIENT BO'''' burst_type,
''''PDF'''' burst_file_type,
''''-1'''' id,
null bouser,
''''G'''' std_rpt_burst
from r08_summary1 r
where r.month_id = '''''+ @monthid + '''''
and r.payor_key = '''''+ @payorkey +'''''
and r.print_flag = ''''Y'''''')'
exec(@reportqry)
February 8, 2011 at 1:22 pm
I don't know, how do the report numbers relate to the data you are getting? If they aren't in there, what piece of data determines what the report number should be? Or do you literally wish to just add the report number that the user submits into the output?
February 8, 2011 at 1:40 pm
Yes i just want to add the report numbers literally regardless of the client. There is no relation. Also i want to update the client_id,client_name that i get from oracle with the client_id,client_name thats already present in client table of the sql server.
something like
update AposGrpwise
set client_id =
(select distinct client_id from client where legacy_client_code = AposGrpwise
.client_code),
If i include this in the procedure it is compiled okay but its throwing error while executing?
Thanks.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply