October 4, 2010 at 10:16 pm
Basically I have a table that stores the definition of a fixed-length record. I want to be able to parse out those records in a .Net app using the String.Substring method. We already have the length of each field stored in the table (as seen below), now I just want to be able to add the calculated Starting Position for each field to my query...
--SAMPLE DATA
CREATE TABLE #Fields
(
FieldId int,
FieldName varchar(50),
FieldLength int
)
INSERT INTO #Fields (FieldId, FieldName, FieldLength)
SELECT 1, 'Field1', 23 UNION ALL
SELECT 2, 'Field2', 34 UNION ALL
SELECT 3, 'Field3', 21 UNION ALL
SELECT 4, 'Field4', 10 UNION ALL
SELECT 5, 'Field5', 5
-- DESIRED OUTPUT
FieldName FieldLength StartPos
========= =========== ========
Field1 23 0
Field2 34 23
Field3 21 57
Field4 10 67
Field5 5 72
October 4, 2010 at 11:55 pm
josh_thoma (10/4/2010)
Basically I have a table that stores the definition of a fixed-length record. I want to be able to parse out those records in a .Net app using the String.Substring method. We already have the length of each field stored in the table (as seen below), now I just want to be able to add the calculated Starting Position for each field to my query...
--SAMPLE DATA
CREATE TABLE #Fields
(
FieldId int,
FieldName varchar(50),
FieldLength int
)
INSERT INTO #Fields (FieldId, FieldName, FieldLength)
SELECT 1, 'Field1', 23 UNION ALL
SELECT 2, 'Field2', 34 UNION ALL
SELECT 3, 'Field3', 21 UNION ALL
SELECT 4, 'Field4', 10 UNION ALL
SELECT 5, 'Field5', 5
-- DESIRED OUTPUT
FieldName FieldLength StartPos
========= =========== ========
Field1 23 0
Field2 34 23
Field3 21 57
Field4 10 67
Field5 5 72
Your output is wrong.
Field 4 dont start at 67 (23+34+21=78) and field 5 is also wrong on the same grounds.
Which you can see if you run this... which should solve your problem
select f1.FieldName, f1.FieldLength, IsNull(sum(f2.FieldLength), 0)
from #Fields f1
left join #Fields f2 on f2.FieldId <= f1.FieldId - 1
group by f1.FieldName, f1.FieldLength
order by f1.FieldName, f1.FieldLength
October 5, 2010 at 5:25 am
Oops...you're right! That's what I get for doing math past my bedtime! Actually the math was right, my eye just wandered to the wrong rows on the last two calculations. Anyway....thanks for the solution! I was halfway there (had the table joined to itself), but couldn't remember how to do the rest.
Thanks again,
Josh
October 5, 2010 at 6:07 am
you dont need to add '-1'
the following will also give the same result
SELECT a.fieldname, a.fieldlength, ISNULL(SUM(b.fieldlength), 0)
FROM fields a LEFT OUTER JOIN fields b ON a.fieldid > b.fieldid
GROUP BY a.fieldname, a.fieldlength
ORDER BY a.fieldname
October 10, 2010 at 11:34 pm
A word of caution. These solutions are all "Triangular Joins". What are those? Well, a full up CROSS JOIN is also known as a "SQUARE JOIN" so you can just imagine how bad a "Triangular Join" can be. To learn more, please see the following article http://qa.sqlservercentral.com/articles/T-SQL/61539/
They'll probably do well enough here because of the extremely limited number of rows but be aware that if the routine takes a lot of hits, you're wasting a lot of clock cycles even for small numbers of rows.
--Jeff Moden
October 11, 2010 at 1:26 am
Jeff Moden (10/10/2010)
A word of caution. These solutions are all "Triangular Joins". What are those? Well, a full up CROSS JOIN is also known as a "SQUARE JOIN" so you can just imagine how bad a "Triangular Join" can be. To learn more, please see the following article http://qa.sqlservercentral.com/articles/T-SQL/61539/They'll probably do well enough here because of the extremely limited number of rows but be aware that if the routine takes a lot of hits, you're wasting a lot of clock cycles even for small numbers of rows.
Nag Nag Nag 😛
You are off course right.
Variant 1: If FieldId is 1->X without gaps
;with cte (FieldId, FieldName, FieldLength, FieldPos)
as
(
select FieldId, FieldName, FieldLength, 0 FieldPos from #Fields
where FieldId = 1
union all
select f.FieldId, f.FieldName, f.FieldLength, cte.FieldPos + cte.FieldLength
from #Fields f join cte on f.FieldId = cte.FieldId + 1
)
select FieldName, FieldLength, FieldPos
from cte OPTION (MAXRECURSION 0);
Variant 2: If FieldId can start anywhere and there can be gaps
declare @Tmp table (FieldNr int, FieldId int, FieldLength int
primary key (FieldNr))
insert into @Tmp (FieldNr, FieldId, FieldLength)
select Row_Number() over (order by FieldId) FieldNr, FieldId,
FieldLength from #Fields
;with cte (FieldNr, FieldId, FieldLength, FieldPos)
as
(
select FieldNr, FieldId, FieldLength, 0 FieldPos from @Tmp where FieldNr = 1
union all
select f.FieldNr, f.FieldId, f.FieldLength,
cte.FieldPos + cte.FieldLength from @Tmp f
join cte on f.FieldNr = cte.FieldNr + 1
)
select f.FieldName, f.FieldLength, cte.FieldPos from cte
join #Fields f on f.FieldId = cte.FieldId order by cte.FieldNr
OPTION (MAXRECURSION 0);
Variant 3: A loop version
declare @Tmp table (FieldId int, FieldPos int primary key (FieldId))
declare @i int
declare @Pos int
declare @FieldLength int
select @FieldLength = FieldLength, @Pos = 0, @i = FieldId
from #Fields
where FieldId = (select top 1 FieldId from #Fields order by FieldId)
while exists (select * from #Fields where FieldId > @i)
begin
insert into @Tmp (FieldId, FieldPos) select FieldId, @Pos
from #Fields where FieldId = @i
select top 1 @Pos = @Pos + @FieldLength, @i = FieldId,
@FieldLength = FieldLength from #Fields where FieldId > @i order by FieldId
end
select f.FieldName, f.FieldLength, t.FieldPos from @Tmp t
join #Fields f on f.FieldId = t.FieldId
Now there are more variants im sure. But this is quite enough for me.
/T
October 11, 2010 at 7:41 am
Thanks for the article Jeff! Learn something new everyday...
And thanks for the variations tommyh! Variant #1 will work for my situation I think.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply