February 20, 2013 at 11:01 pm
All,
I have to write a generic query for the below requirement.
Table Structure:
create table swift_spec(
bra_code varchar(5) not null,
cur_code char(3) not null,
acc_xref_code varchar(20) not null,
direction varchar(10) not null,
swift_type varchar(5) not null,
field_name varchar(3) not null,
field_value varchar(255) not null,
constraint lev_swift_spec_pk primary key clustered ( bra_code, cur_code, acc_xref_code, direction, swift_type, field_name )
)
insert into swift_spec values ('%','RUB','%','%','MT22','65','/RPP/GEO+@lev_id+.+@date+.6.ELEK')
insert into swift_spec values ('RPP','EUR','00039D','sender','MT22','65','/EMIS210/+@72_suite')
insert into swift_spec values ('RPP','EUR','000041Y','sender','MT22','65','/EMIS210/+@72_suite')
insert into swift_spec values ('RPP','EUR','044Z','sender','MT22','65','/EMIS210/+@72_suite')
insert into swift_spec values ('RPP','EUR','69051N','sender','MT22','65','/EMIS210/+@72_suite')
insert into swift_spec values ('RPP','EUR','69054J','sender','MT22','65','/EMIS210/+@72_suite')
insert into swift_spec values ('RPP','EUR','065G','sender','MT22','65','/EMIS210/+@72_suite')
insert into swift_spec values ('RPP','EUR','369194M','sender','MT22','65','/EMIS210/+@72_suite')
insert into swift_spec values ('RPP','EUR','69203W','sender','MT22','65','/EMIS210/+@72_suite')
insert into swift_spec values ('RPP','EUR','369221J','sender','MT22','65','/EMIS210/+@72_suite')
insert into swift_spec values ('RPP','EUR','00369225M','sender','MT22','65','/EMIS210/+@72_suite')
insert into swift_spec values ('RPP','EUR','0369228X','sender','MT22','65','/EMIS210/+@72_suite')
insert into swift_spec values ('RPP','EUR','00369231Z','sender','MT22','65','/EMIS210/+@72_suite')
insert into swift_spec values ('RPP','EUR','0369277W','sender','MT22','65','/EMIS210/+@72_suite')
insert into swift_spec values ('RPP','EUR','0369278F','sender','MT22','65','/EMIS210/+@72_suite')
create procedure pr_swift_spec_msg
(
@bra_code varchar(5),
@cur_code char(3),
@acc_xref_code varchar(20),
@swift_type varchar(5)
)
as
begin
declare @65 varchar(255)
if @cur_code = 'RUB'
begin
select @65 = field_value from swift_spec where cur_code = 'RUB' and swift_type ='65'
end
else
begin
select @65 = field_value
from swift_spec
where bra_code = @bra_code
and cur_code = @cur_code
and acc_xref_code = @acc_xref_code
and swift_type = @swift_type
exec DelimiterSplit @72 , '+'
--The above procedure will insert the delimited values into a table called "Delimit_Values"
--I will read the values one by one by using cursor and check something like below.
FETCH cur_split INTO @colString
WHILE @@fetchstatus = 0
BEGIN
IF ((@colString = '/EMIS210/')
SET @65= @65+ @colString
-- If @colString = '/RPP/GEO'
IF ((@colString = '/RPP/GEO')
SET @65= @65+ @colString
IF (@colString = '@lev_id')
BEGIN
SET @65 = @65 + convert(varchar(50),@lev_id) + '.'
END
IF (@colString = '@date')
BEGIN
SET @65 = @65 + @lev_treasury_date
END
IF (@colString = '.6.ELEK')
BEGIN
SET @65 = @65 + @colString
END
FETCH cur_split INTO @colString
END
The problematic area is IF (@colString = '.6.ELEK')
I have hardcoded the value in the condition, what will happen if they update this particular row to .7.ELEK ?
I need a generic way to do this. Inputs are welcome
karthik
February 21, 2013 at 6:21 pm
Just a few points:
1. While I didn't actually try it, I don't think the code you provided can be run because @cur_code doesn't seem to be defined anywhere and you didn't provide the code for the SP: DelimiterSplit
2. I recommend that you use a proven-fast splitter like the one here (DelimitedSplit8K):
Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]
3. There is no need to use a CURSOR to do the final processing. All of that can be done using CASE inside of a single query.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply