December 20, 2011 at 6:27 am
Hi,
I recently moved from Orcale to SQL Server. I received help with a cursor in Oracle but I'm battling to reproduce this in SQL Server. Not sure if anyone will be able to help. The logic shouldn't be too difficult to follow.
declare
cursor curs is
select NameID, IncidentDate
from table_1 t
--where rownum < 6
for update of itc_latest;
w_itc_latest number(4,0);
w_itc_latest_date date;
begin
for i in curs loop
begin
-- get latest itc date
select max(ITCScoreDate) into w_itc_latest_date
from Staging.ITCChecks
where NameID = i.NameID
and ITCScoreDate <= i.IncidentDate;
-- get latest itc score
select ITCScore into w_itc_latest
from Staging.ITCChecks
where NameID = i.NameID
and ITCScoreDate = w_itc_latest_date
and ITCScoreDate <= i.IncidentDate
and rownum = 1;
exception when no_data_found then
w_itc_latest_date := NULL;
w_itc_latest := NULL;
end;
update table_1
set itc_latest = w_itc_latest
, itc_latest_date = w_itc_latest_date
where current of curs;
end loop;
end;
/
December 20, 2011 at 6:37 am
MS SQL cursor is not a well performing solution (Oracle is somewhat better in it). Please try to achieve it with set based approach.
December 20, 2011 at 6:47 am
I went through the same process a while back (coming from Oracle) and the syntax of SQL Server is fairly similar. If you look at Books Online for SQL Server, search under CURSOR and FETCH. It is very easy to switch the syntax.
E.g. DECLARE curs CURSOR FOR
SELECT....
FROM
FETCH NEXT FROM curs
It's true though that in SQL Server CURSORS are discouraged... I'm sure that one of the gurus will jump in soon with a suggestion to point you in a better direction. You'll find this forum much more friendly than some of the people in certain Oracle forums 😉
Steve
December 20, 2011 at 11:06 pm
Thanks for the feedback. People on this forum are noticeably more friendly than Oracle forums!:-D
December 20, 2011 at 11:16 pm
One thing that will really help you get good answers quickly is to follow the instructions in the first article you will find referenced below in my signature block. If you provide the DDL (CREATE TABLE statement(s)) for the table(s) involved, sample data (as a series of INSERT INTO statements) for the table(s) involved (no more than 10 to 15 rows being needed as long as it properly represents the problem domain and isn't live or production data), and the expected results based on the provided sample data.
Make sure the code you post works, isn't dependent on anything on your system that we may not have, and remember that some environments out here are case sensitive (mine at home for one).
The more you provide us, the better answers you will get plus the code will be tested as well.
December 20, 2011 at 11:24 pm
Will definitely keep that in mind for future queries:-) Thanks
December 20, 2011 at 11:28 pm
mic.con87 (12/20/2011)
Will definitely keep that in mind for future queries:-) Thanks
Glad to hear this, but it is also important for this query as well. I'd help right now, but without the information I talked about, not many of us may jump in when we have to create everything from scratch and guess at what you are actually trying to accomplish.
I worked as an Oracle developer myself for a year, and even there I fought hard to NOT use cursors, finding set-based methods of accomplish the required tasks.
December 21, 2011 at 12:28 am
Hi,
I was able to achieve the result I needed without the use of a cursor. Thanks again for your advice and your willingness to help.
December 21, 2011 at 12:38 am
Would you mind sharing your script here? Its SSC tradition and it will complete the thread. 🙂
December 21, 2011 at 6:11 am
Sure thing.
Select it.*
,(Select --Find the latest ITC Score before the incident took place
ITCScore
from Staging.ITCChecks ri
where ri.NameID = it.NameID
and ITCScoreDate = (select max(ri.ITCScoreDate)
from Staging.ITCChecks ri
where ri.NameID = it.NameID
and ri.ITCScoreDate <= it.IncidentDate
)
) as ITCScore
INTO #InitialTable_18
From #InitialTable_17 it
Select * from #InitialTable_18
Drop Table #InitialTable_18
December 21, 2011 at 6:14 am
Thanks!
Glad it worked out well.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply