September 8, 2010 at 11:42 pm
[font="Tahoma"]Hi all
I have a table with the some Statistical data... I am working on a SSIS package to extract this and email it out to people...
I am stuck in one part and need some help -
The table has data for users and some of the rows have two or more users.. E.g.
StaffNo Time
123; 156 40
325; 666; 785 50
etc
I have a function called Split and i would like to know - If can call that function in the where clause of Select Statement. This is a Table Valued Function
My query is as below
Declare @staffno varchar(10);
Set @staffno = '156'
Select * from TableName
where [dbo].split(staffno, ';') = @staffno
can any one please help
thanks
Vani[/font][/size]
September 9, 2010 at 12:01 am
vani_r14 (9/8/2010)
[font="Tahoma"]Hi allI have a table with the some Statistical data... I am working on a SSIS package to extract this and email it out to people...
I am stuck in one part and need some help -
The table has data for users and some of the rows have two or more users.. E.g.
StaffNo Time
123; 156 40
325; 666; 785 50
etc
I have a function called Split and i would like to know - If can call that function in the where clause of Select Statement. This is a Table Valued Function
My query is as below
Declare @staffno varchar(10);Set @staffno = '156'
Select * from TableName
where [dbo].split(staffno, ';') = @staffno
can any one please help
thanks
Vani[/font]
[/size]
Try the following and see what it gives you. You may need to modify it depending on your needs.
Not really sure what you are trying to accomplish from your post.
select
tn.*,
s.item -- what ever your field name for the split items
from
dbo.TableName tn
cross apply dbo.split(tn.staffno, ';') s;
Question I have, is your split function an inline-TVF or a multiline-TVF?
There is a performance difference.
September 9, 2010 at 2:12 am
Is this what you're looking for?
Declare @staffno varchar(10)
Set @staffno = '156'
SELECT TableName.*, S.Item
FROM TableName
CROSS APPLY dbo.split(staffno, ';') AS S
WHERE S.Item = @staffno
September 9, 2010 at 5:02 am
Not sure what your Split function does, so here's a self-contained version which brings all the processing in-line
IF NOT OBJECT_ID('tempdb.dbo.#t', 'U') IS NULL DROP TABLE #t;
SELECT '123; 156' AS StaffNo, '40' AS Time INTO #t
UNION ALL SELECT '325; 666; 785', '50';
Declare @staffno varchar(10);
Set @staffno = '156';
WITH cteTally (N)
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..syscolumns
)
SELECT #t.*, Z.Item
FROM #t
CROSS APPLY
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
LTRIM(SUBSTRING(StaffNo + ';', N, CHARINDEX(';', StaffNo + ';', N) - N))
FROM cteTally
WHERE N < LEN(StaffNo) + 2 AND SUBSTRING(';' + StaffNo + ';', N, 1) = ';'
) AS Z (ROW, Item)
WHERE Z.Item = @staffno;
September 9, 2010 at 4:54 pm
Hi all
Thanks for all your help in the Forum 🙂
I got it figured out and its working now... If any one needs it in case here is what i did -
Initially I took all the Staff no's in the table and split them up and kept them in another table using the below code -
declare @i int; set @i = 0;
Update TempStaffno
set @i = num = @i+1
declare @C int; declare @d int;
declare @staffno table (staffno varchar(1000))
declare @workerid varchar(200);
set @C = (select distinct min(num) from TempStaffno);
set @d = (select distinct max(num) from TempStaffno);
while (@c < = @d)
begin
set @workerid = (select staffno from TempStaffno where num = @C);
insert into @staffno
select * from dbo.split (rtrim(@workerid), ';')
end
insert into tempstaffno (workerid)
select * from @staffno
when I had a table with all the Staff no split - I then used that table to join it with my TempStats table to get what I wanted... In the Tempstats table i added a column called num and numbered each row uniquely to identify the row in the loop and used a loop to split the tempstat table and get what was needed.
declare @C int; declare @d int;
declare @staffno table (staffno varchar(1000))
declare @workerid varchar(200);
set @C = (select distinct min(num) from TempStaffno);
set @d = (select distinct max(num) from TempStaffno);
while (@c < = @d)
begin
select @workerid = (select distinct workerid from TempStaffno t where num = @C);
select @workerid -- presents the staffno at the top of each group
Select t.* from tempstat t
cross apply dbo.split(t.workerid, ';') as s where t.workerid like '%' + @workerid + '%'
end
Now am going to figure out how to do this in SSIS.... YAY 🙂
Thanks for all your help in the Forum 🙂
Cheers
Vani
September 10, 2010 at 4:55 am
vani_r14 (9/8/2010)[hrI have a function called Split and i would like to know - If can call that function in the where clause of Select Statement. This is a Table Valued Function
As the others have stipulated, the use of CROSS APPLY is how your original question is answered.
What I'd really be interested in is seeing your split function. Not trying to be offensive here... trying to help. It's been my experience that most folks end up making some sort of "multiline" table valued function (mlTVF), which will usually be as horribly slow as a scalar function, instead of a very high performance "inline" table valued function (iTVF).
Would you mind posting your split function so we can double check it for you?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2010 at 5:00 am
vani_r14 (9/9/2010)
Hi allThanks for all your help in the Forum 🙂
I got it figured out and its working now... If any one needs it in case here is what i did -
Initially I took all the Staff no's in the table and split them up and kept them in another table using the below code -
declare @i int; set @i = 0;
Update TempStaffno
set @i = num = @i+1
declare @C int; declare @d int;
declare @staffno table (staffno varchar(1000))
declare @workerid varchar(200);
set @C = (select distinct min(num) from TempStaffno);
set @d = (select distinct max(num) from TempStaffno);
while (@c < = @d)
begin
set @workerid = (select staffno from TempStaffno where num = @C);
insert into @staffno
select * from dbo.split (rtrim(@workerid), ';')
end
insert into tempstaffno (workerid)
select * from @staffno
when I had a table with all the Staff no split - I then used that table to join it with my TempStats table to get what I wanted... In the Tempstats table i added a column called num and numbered each row uniquely to identify the row in the loop and used a loop to split the tempstat table and get what was needed.
declare @C int; declare @d int;
declare @staffno table (staffno varchar(1000))
declare @workerid varchar(200);
set @C = (select distinct min(num) from TempStaffno);
set @d = (select distinct max(num) from TempStaffno);
while (@c < = @d)
begin
select @workerid = (select distinct workerid from TempStaffno t where num = @C);
select @workerid -- presents the staffno at the top of each group
Select t.* from tempstat t
cross apply dbo.split(t.workerid, ';') as s where t.workerid like '%' + @workerid + '%'
end
Now am going to figure out how to do this in SSIS.... YAY 🙂
Thanks for all your help in the Forum 🙂
Cheers
Vani
You still have a loop in that final query. It's not necessary with the CROSS APPLY. The whole purpose of the CROSS APPLY is to get rid of the explicit RBAR in favor of letting SQL Server figure out how to accomplish the same task in a high speed set based fashion. I'd rewrite it for you but I'm leaving for work shortly and just don't have the time (although it won't take much). Hopefully, one of the other folks can show you how.
The other thing is you say you need to figure out how to do it in SSIS. The answer is, don't. Instead, convert the code to a stored procedure and call the stored procedure.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2010 at 5:37 am
vani_r14 (9/8/2010)
[font="Tahoma"]I have a function called Split and i would like to know - If can call that function in the where clause of Select Statement. This is a Table Valued Function
My query is as below
Declare @staffno varchar(10);Set @staffno = '156'
Select * from TableName
where [dbo].split(staffno, ';') = @staffno
can any one please help
thanks
Vani[/font]
[/size]
If this is all you are trying to do, then you don't need the split function at all, you need something like the following:
-----------------------------------------------------------
-- create some sample data THIS IS NOT PART OF THE SOLUTION
-----------------------------------------------------------
CREATE TABLE #TableName (TableNameID INT IDENTITY(1,1), staffno VARCHAR(250))
INSERT INTO #TableName (staffno)
SELECT '150;151;152;153;156' UNION ALL --
SELECT '156' UNION ALL --
SELECT '150;151;152;153' UNION ALL
SELECT '150;153;156' UNION ALL --
SELECT '153'
SELECT * FROM #TableName
-----------------------------------------------------------
-- solution
-----------------------------------------------------------
DECLARE @staffno VARCHAR(10)
SET @staffno = '156'
SELECT TableNameID, staffno
FROM #TableName
WHERE ';' + staffno + ';' LIKE '%;' + @staffno + ';%'
Alternatively, if your intention is to normalize out the staffIDs from the string staffno then CROSS APPLY with the iTVF is the way to go - but as Jeff points out, you most certainly do not require a loop in there.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 11, 2010 at 5:18 am
We have a requirement of finding data that has been entered incorrectly into the database by staff members and sending an email to them every fortnight with an attachment indicating which bit of that is incorrect.
Sample data
StaffNo Time
123 40
123; 150 50
100 60
150;140 50
Etc
The idea is that – each staff member gets an attachment of which contains their stats they need to change.
If there are two people who have worked together e.g. 123; 150 have worked together for 50 mins then this row of data will be sent to both 123 and 150 to change what needs to be changed, similarly for others.
The email has to be sent automatically – hence creating the ssis package so a job can be scheduled and this can be done….
Split function - e.g. sample as below - link
1. CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
2. returns @temptable TABLE (items varchar(8000))
3. as
4. begin
5. declare @idx int
6. declare @slice varchar(8000)
7.
8. select @idx = 1
9. if len(@String)<1 or @String is null return
10.
11. while @idx!= 0
12. begin
13. set @idx = charindex(@Delimiter,@String)
14. if @idx!=0
15. set @slice = left(@String,@idx - 1)
16. else
17. set @slice = @String
18.
19. if(len(@slice)>0)
20. insert into @temptable(Items) values(@slice)
21.
22. set @String = right(@String,len(@String) - @idx)
23. if len(@String) = 0 break
24. end
25. return
26. end
I have started to rework on this now…
September 11, 2010 at 1:29 pm
vani_r14 (9/9/2010)
Hi allThanks for all your help in the Forum 🙂
I got it figured out and its working now... If any one needs it in case here is what i did -
Initially I took all the Staff no's in the table and split them up and kept them in another table using the below code -
declare @i int; set @i = 0;
Update TempStaffno
set @i = num = @i+1
declare @C int; declare @d int;
declare @staffno table (staffno varchar(1000))
declare @workerid varchar(200);
set @C = (select distinct min(num) from TempStaffno);
set @d = (select distinct max(num) from TempStaffno);
while (@c < = @d)
begin
set @workerid = (select staffno from TempStaffno where num = @C);
insert into @staffno
select * from dbo.split (rtrim(@workerid), ';')
end
insert into tempstaffno (workerid)
select * from @staffno
I am puzzled by the block of code above:ermm: It seems to me that you are starting off with the table TempStaffno holding the semi-colon separated values in the field staffno. At the end of the process, you have appended the split components into the field workerid in the same table having no relationship with the original data. Surely you want to preserve the relationship so that columns num and staffno match up with the split items field. This can be accomplished using the code snippet below giving you the relational data in the #TempStaffno table.
IF NOT OBJECT_ID('tempdb.dbo.#TempStaffno', 'U') IS NULL DROP TABLE #TempStaffno;
SELECT T.num, T.staffno, LTRIM(Z.items) AS workerid
INTO #TempStaffno
FROM TempStaffno AS T
CROSS APPLY dbo.split(staffno, ';') AS Z;
SELECT * FROM #TempStaffno;
September 12, 2010 at 4:29 pm
Hi
I have changed the workings of this - in an attempt to improvise the whole query -
SELECT distinct staffno, s.items, identity(int, 1, 1) as Cnt
into TempStaffno
FROM Tablename cross apply dbo.split(rtrim(staffno), ';') as s
This way is kind of looking better and works ok - it makes sense as i keep the original data in the staffno column (i.e. the staffno column - is going to have the combinations, the items column will have the staff no split, and cnt will have a number so this could be used in the next part).
September 12, 2010 at 10:14 pm
vani_r14 (9/11/2010)
We have a requirement of finding data that has been entered incorrectly into the database by staff members and sending an email to them every fortnight with an attachment indicating which bit of that is incorrect.Sample data
StaffNo Time
123 40
123; 150 50
100 60
150;140 50
Etc
The idea is that – each staff member gets an attachment of which contains their stats they need to change.
If there are two people who have worked together e.g. 123; 150 have worked together for 50 mins then this row of data will be sent to both 123 and 150 to change what needs to be changed, similarly for others.
The email has to be sent automatically – hence creating the ssis package so a job can be scheduled and this can be done….
Split function - e.g. sample as below - link
1. CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))2. returns @temptable TABLE (items varchar(8000))
3. as
4. begin
5. declare @idx int
6. declare @slice varchar(8000)
7.
8. select @idx = 1
9. if len(@String)<1 or @String is null return
10.
11. while @idx!= 0
12. begin
13. set @idx = charindex(@Delimiter,@String)
14. if @idx!=0
15. set @slice = left(@String,@idx - 1)
16. else
17. set @slice = @String
18.
19. if(len(@slice)>0)
20. insert into @temptable(Items) values(@slice)
21.
22. set @String = right(@String,len(@String) - @idx)
23. if len(@String) = 0 break
24. end
25. return
26. end
I have started to rework on this now…
Heh... oh my word, NO! That's exactly what I was worried about for you. Take that particular fish and throw it back into the sea from whence it came before it kills you! 😉 Forget that you ever saw such a thing.
Here's a little bit of code that will blow that other fish right out of the water when it comes to performance...
CREATE FUNCTION dbo.Split8KT
(@Parameter VARCHAR(8000), @Delimiter VARCHAR(1))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
SUBSTRING(@Parameter,N,CHARINDEX(@Delimiter,@Parameter+@Delimiter,N)-N) AS ItemValue
FROM dbo.Tally
WHERE N BETWEEN 1 AND LEN(@Parameter)
AND SUBSTRING(@Delimiter+@Parameter,N,1) = @Delimiter;
All you need now is a Tally table and to understand how it replaces certain While Loops. See the following URL for how all that works... it WILL change your computational life. 😉
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
September 12, 2010 at 10:33 pm
And if you do a little searching on SSC you will also find info on how to make a dynamic tally table.
September 14, 2010 at 2:01 pm
Hi all
Thanks for the help... I will start working on this shortly... just a bit busy with other thing at the moment
thanks again
vani
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply