November 17, 2011 at 10:17 am
hi friends,
i think people in this group hate cursor and i agree with it. Just looked into our existing functions and found one function which uses cursor to do the following calculation. The aim of the function is to find out the code number if you pass the refno and number of the code.
create table #temp
(refno varchar(10), primary_code varchar(10), secondary_code varchar(10))
insert into #temp
select '1000','Q112',''
Union all
select '1000','W232',''
Union all
select '1000','W343','Y343'
Union all
select '1000','W353','W645'
Union all
select '1000','Q112',''
Union all
select '2000','Q123','T645'
Union all
select '2000','W343','P934'
Union all
select '2000','','W343'
Union all
select '2000','Q232','Q232'
Union all
select '2000','','R343'
select * From #temp
-- refno = 1000
select dbo.getthecode(refno,3) = W343
select dbo.getthecode(refno,3) = W353
-- refno = 2000
select dbo.getthecode(refno,6) = W343
The way it works is for the same refno you count the number from primary to secondary (primary 1, secondary 2, primary 3, secondary 4, etc.). So if you pass the refno and the number of the code the function needs to find out the right code from the right place. Because some of the code is blank we need to read row by row and find out the right number.
This is done using cursor at the moment but i would appreciate your help to write it without cursor.
Thanks.
Vijay
November 17, 2011 at 10:30 am
Hi Vijay. I have a couple of questions.
How can you guarantee that the sequence will remain the same within a refno? For example, with refno 1000 you can't be absolutely sure that Q112 is always the first code because SQL Server may just decide to return the results in another order. Maybe your real situation has another key field?
Second, how can calling the function with 1000 and 3 return different results?
Cliff
November 17, 2011 at 10:38 am
sorry cliff, it returns different numbers whatever is on the right place. It was typo.
Also there is something called sort_order which puts everything in order when cursor starts. I think i can manage that change once i know how to calcuate this without row by row processing and finding out the number.
November 17, 2011 at 10:46 am
create table #temp
(refno varchar(10), sort_order int, primary_code varchar(10), secondary_code varchar(10))
insert into #temp
select '1000',1, 'Q112',''
Union all
select '1000',2, 'W232',''
Union all
select '1000',3,'W343','Y343'
Union all
select '1000',4,'W353','W645'
Union all
select '1000',5,'Q112',''
Union all
select '2000',1,'Q123','T645'
Union all
select '2000',2,'W343','P934'
Union all
select '2000',3,'','W343'
Union all
select '2000',4,'Q232','Q232'
Union all
select '2000',5,'','R343'
select * From #temp
-- refno = 1000
select dbo.getthecode(refno,5) = W343
select dbo.getthecode(refno,7) = W353
-- refno = 2000
select dbo.getthecode(refno,6) = W343
November 17, 2011 at 11:08 am
This is my stab at it. It doesn't do RBAR (unless unpivot does something behind the scenes I don't know about) and it doesn't do any kind of looping. I changed the #temp to a variable @temp in my sample.
declare @temp table
(refno varchar(10), primary_code varchar(10), secondary_code varchar(10))
insert into @temp VALUES ('1000','Q112','')
insert into @temp VALUES ('1000','W232','')
insert into @temp VALUES ('1000','W343','Y343')
insert into @temp VALUES ('1000','W353','W645')
insert into @temp VALUES ('1000','Q112','')
insert into @temp VALUES ('2000','Q123','T645')
insert into @temp VALUES ('2000','W343','P934')
insert into @temp VALUES ('2000','','W343')
insert into @temp VALUES ('2000','Q232','Q232')
insert into @temp VALUES ('2000','','R343')
DECLARE @ref int
DECLARE @C int
SELECT @ref = 1000, @C = 3
SELECT code FROM
(
SELECT refno, code, ROW_NUMBER() OVER (Partition BY refno ORDER BY (SELECT NULL)) as pos
FROM
(
SELECT refno, codename, code
FROM
(
SELECT refno, primary_code, secondary_code
FROM @temp
WHERE refno = @ref
) t
UNPIVOT
(code FOR codename IN (primary_code, secondary_code)) as mup
) r
WHERE code <> ''
) f
WHERE pos = @C
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply