Hello all
I've got a problem with a custom assembly used in a Report. It works a
little (i see correct values where it is used), but after a while, it
doesn't seem to work anymore (no more correct values, only some 0 with
the same values in parameters)...
Some facts :
the dll is written in vb and is accessing the sql server to return
some values.
it contains the lines
Dim SQLPermission As New
SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
SQLPermission.Assert()
(seems to be important)
the dll is located in correct directories :
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin
and
C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer
the file rssrvpolicy.config has been modified with :
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="RS.Utils_Group"
Description="This code group grants custom assembly OASCL01.dll to
access to database">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="file://C:/Program Files/Microsoft SQL Server/MSSQL/Reporting
Services/ReportServer/bin/OASCL01.dll"/>
</CodeGroup>
the DLL is defined in the report properties.
Why do you think it only work for a little time ? (maybe because I
am constantly modifying the report while working on it?)
what can I do to find what's wrong ?
I tried relaunching the reportserver service but nothing...If assembly is not being changed and works at least once when used from a
report published on report server then most likely the problem has nothing
to do with code access security (btw, it looks right).
There might be a bug in the assembly itself.
Usually it is a bad idea to use static variables. Make sure you properly
close all connections / commands / readers you open.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"marc hugon" <sorry@.nospam.com> wrote in message
news:atdbg11fsi5udafl3h881lki03r0lof3jr@.4ax.com...
> Hello all
> I've got a problem with a custom assembly used in a Report. It works a
> little (i see correct values where it is used), but after a while, it
> doesn't seem to work anymore (no more correct values, only some 0 with
> the same values in parameters)...
> Some facts :
> the dll is written in vb and is accessing the sql server to return
> some values.
> it contains the lines
> Dim SQLPermission As New
> SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
> SQLPermission.Assert()
> (seems to be important)
> the dll is located in correct directories :
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\bin
> and
> C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer
> the file rssrvpolicy.config has been modified with :
> <CodeGroup class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="RS.Utils_Group"
> Description="This code group grants custom assembly OASCL01.dll to
> access to database">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="file://C:/Program Files/Microsoft SQL Server/MSSQL/Reporting
> Services/ReportServer/bin/OASCL01.dll"/>
> </CodeGroup>
> the DLL is defined in the report properties.
> Why do you think it only work for a little time ? (maybe because I
> am constantly modifying the report while working on it?)
> what can I do to find what's wrong ?
> I tried relaunching the reportserver service but nothing...
Showing posts with label values. Show all posts
Showing posts with label values. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
loosing values when load from text file
When loading a table in a data flow from a text file that contains non-null float values, I am seeing erratic and inconsistent results. I am presently using SQL Server Destination in a data flow.
- With low volumnes of data, less that 50,000 rows, no problems
- But with higher volumnes, 2,000,000+ rows, I get different results depending on how I run the package. If I run is directly (right-click and click on Execute), I get the expected result.
But if I use SQL Server Agent to run the package, half of the values are lost and nulls are loaded instead. I have inspected the into text file and there are few rows with null for the column.
Any help would be appreciated!
Greg
>>>- With low volumnes of data, less that 50,000 rows, no problems
Did you have success using both BIDS and SQL Agent for the 50K load?
>>>- But with higher volumnes, 2,000,000+ rows, I get different results depending on how I run the package.
While using SQL Agent did you chose the Command subsystem and use dtexec or the SSIS subsystem?
LOOPING UPDATE
Hi
I just require a bit of guidance on a SQL query I am writing. I am updating
a table with values, and the first field is a TYPE field, I set this to A
and then populate fields 2-15 with a variety of default updates and values
from other tables, I then do a second insert whreeby I set the same TYPE
field to B and update fields 2-5 and then the unique fields 16-20.
At the moment my second update has two issues;
1. It sets EVERY type to B (although it correctly doubles the amount of
entries in the table)
2. The B entries are appended to the bottom of the table, ideally I want the
table structure to be ABABABAB etc
Any help to a SQL newbie appreciated!> 1. It sets EVERY type to B (although it correctly doubles the amount of
> entries in the table)
Sounds like your WHERE clause may be at fault. Could you post a CREATE TABLE
statement and the UPDATE/INSERT statements so that we can reproduce your
problem.
> 2. The B entries are appended to the bottom of the table, ideally I want
the
> table structure to be ABABABAB etc
Tables have no logical ordering. If you want to see the results in a
particular order then use ORDER BY on a SELECT statement when you query the
table. A clustered index orders data in physical storage but not necessarily
when you query the table.
David Portas
SQL Server MVP
--|||Hi David
I have rewritten the part of the routine with a separate WHERE clause on the
type field at the end of the update and this seems to work so thank you.
I have several more loops to write, I will then try the Order by at the end
of the routine.
Thank you very very much for your quick and informative response, it really
is appreciated.
Steve
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:8ZKdncKsYuPgDrHdRVn-uA@.giganews.com...
> Sounds like your WHERE clause may be at fault. Could you post a CREATE
TABLE
> statement and the UPDATE/INSERT statements so that we can reproduce your
> problem.
>
> the
> Tables have no logical ordering. If you want to see the results in a
> particular order then use ORDER BY on a SELECT statement when you query
the
> table. A clustered index orders data in physical storage but not
necessarily
> when you query the table.
> --
> David Portas
> SQL Server MVP
> --
>sql
I just require a bit of guidance on a SQL query I am writing. I am updating
a table with values, and the first field is a TYPE field, I set this to A
and then populate fields 2-15 with a variety of default updates and values
from other tables, I then do a second insert whreeby I set the same TYPE
field to B and update fields 2-5 and then the unique fields 16-20.
At the moment my second update has two issues;
1. It sets EVERY type to B (although it correctly doubles the amount of
entries in the table)
2. The B entries are appended to the bottom of the table, ideally I want the
table structure to be ABABABAB etc
Any help to a SQL newbie appreciated!> 1. It sets EVERY type to B (although it correctly doubles the amount of
> entries in the table)
Sounds like your WHERE clause may be at fault. Could you post a CREATE TABLE
statement and the UPDATE/INSERT statements so that we can reproduce your
problem.
> 2. The B entries are appended to the bottom of the table, ideally I want
the
> table structure to be ABABABAB etc
Tables have no logical ordering. If you want to see the results in a
particular order then use ORDER BY on a SELECT statement when you query the
table. A clustered index orders data in physical storage but not necessarily
when you query the table.
David Portas
SQL Server MVP
--|||Hi David
I have rewritten the part of the routine with a separate WHERE clause on the
type field at the end of the update and this seems to work so thank you.
I have several more loops to write, I will then try the Order by at the end
of the routine.
Thank you very very much for your quick and informative response, it really
is appreciated.
Steve
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:8ZKdncKsYuPgDrHdRVn-uA@.giganews.com...
> Sounds like your WHERE clause may be at fault. Could you post a CREATE
TABLE
> statement and the UPDATE/INSERT statements so that we can reproduce your
> problem.
>
> the
> Tables have no logical ordering. If you want to see the results in a
> particular order then use ORDER BY on a SELECT statement when you query
the
> table. A clustered index orders data in physical storage but not
necessarily
> when you query the table.
> --
> David Portas
> SQL Server MVP
> --
>sql
LOOPING UPDATE
Hi
I just require a bit of guidance on a SQL query I am writing. I am updating
a table with values, and the first field is a TYPE field, I set this to A
and then populate fields 2-15 with a variety of default updates and values
from other tables, I then do a second insert whreeby I set the same TYPE
field to B and update fields 2-5 and then the unique fields 16-20.
At the moment my second update has two issues;
1. It sets EVERY type to B (although it correctly doubles the amount of
entries in the table)
2. The B entries are appended to the bottom of the table, ideally I want the
table structure to be ABABABAB etc
Any help to a SQL newbie appreciated!> 1. It sets EVERY type to B (although it correctly doubles the amount of
> entries in the table)
Sounds like your WHERE clause may be at fault. Could you post a CREATE TABLE
statement and the UPDATE/INSERT statements so that we can reproduce your
problem.
> 2. The B entries are appended to the bottom of the table, ideally I want
the
> table structure to be ABABABAB etc
Tables have no logical ordering. If you want to see the results in a
particular order then use ORDER BY on a SELECT statement when you query the
table. A clustered index orders data in physical storage but not necessarily
when you query the table.
--
David Portas
SQL Server MVP
--|||Hi David
I have rewritten the part of the routine with a separate WHERE clause on the
type field at the end of the update and this seems to work so thank you.
I have several more loops to write, I will then try the Order by at the end
of the routine.
Thank you very very much for your quick and informative response, it really
is appreciated.
Steve
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:8ZKdncKsYuPgDrHdRVn-uA@.giganews.com...
> > 1. It sets EVERY type to B (although it correctly doubles the amount of
> > entries in the table)
> Sounds like your WHERE clause may be at fault. Could you post a CREATE
TABLE
> statement and the UPDATE/INSERT statements so that we can reproduce your
> problem.
> > 2. The B entries are appended to the bottom of the table, ideally I want
> the
> > table structure to be ABABABAB etc
> Tables have no logical ordering. If you want to see the results in a
> particular order then use ORDER BY on a SELECT statement when you query
the
> table. A clustered index orders data in physical storage but not
necessarily
> when you query the table.
> --
> David Portas
> SQL Server MVP
> --
>
I just require a bit of guidance on a SQL query I am writing. I am updating
a table with values, and the first field is a TYPE field, I set this to A
and then populate fields 2-15 with a variety of default updates and values
from other tables, I then do a second insert whreeby I set the same TYPE
field to B and update fields 2-5 and then the unique fields 16-20.
At the moment my second update has two issues;
1. It sets EVERY type to B (although it correctly doubles the amount of
entries in the table)
2. The B entries are appended to the bottom of the table, ideally I want the
table structure to be ABABABAB etc
Any help to a SQL newbie appreciated!> 1. It sets EVERY type to B (although it correctly doubles the amount of
> entries in the table)
Sounds like your WHERE clause may be at fault. Could you post a CREATE TABLE
statement and the UPDATE/INSERT statements so that we can reproduce your
problem.
> 2. The B entries are appended to the bottom of the table, ideally I want
the
> table structure to be ABABABAB etc
Tables have no logical ordering. If you want to see the results in a
particular order then use ORDER BY on a SELECT statement when you query the
table. A clustered index orders data in physical storage but not necessarily
when you query the table.
--
David Portas
SQL Server MVP
--|||Hi David
I have rewritten the part of the routine with a separate WHERE clause on the
type field at the end of the update and this seems to work so thank you.
I have several more loops to write, I will then try the Order by at the end
of the routine.
Thank you very very much for your quick and informative response, it really
is appreciated.
Steve
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:8ZKdncKsYuPgDrHdRVn-uA@.giganews.com...
> > 1. It sets EVERY type to B (although it correctly doubles the amount of
> > entries in the table)
> Sounds like your WHERE clause may be at fault. Could you post a CREATE
TABLE
> statement and the UPDATE/INSERT statements so that we can reproduce your
> problem.
> > 2. The B entries are appended to the bottom of the table, ideally I want
> the
> > table structure to be ABABABAB etc
> Tables have no logical ordering. If you want to see the results in a
> particular order then use ORDER BY on a SELECT statement when you query
the
> table. A clustered index orders data in physical storage but not
necessarily
> when you query the table.
> --
> David Portas
> SQL Server MVP
> --
>
looping through XML with xquery
Hello,
for example:
declare @.xml xml
select @.xml = (select * from table for xml raw, elements)
... now i want to iterate through @.xml and get the values from field ID:
declare @.id int, @.x int, @.y int
select @.x = @.xml.value('data(count(/*))','int')
set @.y = 1
while @.y <= @.x begin
select @.id = @.xml.value('data(/row/ID)[' + cast(@.y as varchar) + ']','int')
set @.y * @.y + 1
end
... this is not working because for value() only string literals are allowed, so how can i do this?
thank you,
Helmut
You are on the right track, but you need to use the sql:variable function to do this. sql:variable gives you access to variables and parameters in scope.
http://msdn2.microsoft.com/en-us/library/ms188254.aspx
so you should be able to write your query as:
select @.id = @.xml.value('data(/row/ID)[sql:variable(@.y)]','int')
This also has the added advantage of avoiding sql or xquery injection.
|||Superb, works perfect!thank you very much,
Helmut
|||SELECT SearchCriteriaXML.value('data(//ColumnName)[sql:variable(@.i)]','varchar(100)')
FROM SearchColumn INNER JOIN SearchCriteria ON SearchColumn.SearchCriteriaId = SearchCriteria.Id
WHERE (SearchCriteria.Id = 1) AND (SearchColumn.Id = 1)
I'm Getting below error for above query
Msg 2225, Level 16, State 1, Line 20
XQuery [SearchCriteria.SearchCriteriaXML.value()]: A string literal was expected
sql
Monday, March 26, 2012
looping through a recordset
Hi
I am a recordset which I would like to extract a field and make a string
from it, by appending values to it.
e.g
PolicyRef Product
C001 M
C001 B
C001 S
C002 N
C002 C
C002 T
Ideally, what I need is the products in one field:
e.g
PolicyRef Product
C001 M/B/S
C002 N/C/T
I am trying to create a Loop construct, but not having much luck.
Any ideas?
Kind Regards
RickyIf you are using SQL Server 2005 you can do this...
(taken from my blog entry:
http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
create table mailing_list (
individual_name nvarchar(100) not null,
list_name nvarchar(10) not null
)
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
C' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
A' )
select distinct
individual_name,
list = substring(
( select '/' + list_name as [text()]
from mailing_list m2
where m2.individual_name = m1.individual_name
for xml path(''), elements )
, 2, 100 )
from mailing_list m1
gives...
alex r List A
joe r List A/List B
tony r List A/List B/List C
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:O6Kep5cmGHA.4716@.TK2MSFTNGP04.phx.gbl...
> Hi
> I am a recordset which I would like to extract a field and make a string
> from it, by appending values to it.
> e.g
> PolicyRef Product
> C001 M
> C001 B
> C001 S
> C002 N
> C002 C
> C002 T
> Ideally, what I need is the products in one field:
> e.g
> PolicyRef Product
> C001 M/B/S
> C002 N/C/T
> I am trying to create a Loop construct, but not having much luck.
> Any ideas?
> Kind Regards
> Ricky
>|||Hi Tony
Thanks for your reply, is there something more dynamic, I've been told to
try and use a WHILE loop and use the ROWCOUNT, but not sure how to implment
this?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%23m4EDAdmGHA.2204@.TK2MSFTNGP03.phx.gbl...
> If you are using SQL Server 2005 you can do this...
> (taken from my blog entry:
> http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
> create table mailing_list (
> individual_name nvarchar(100) not null,
> list_name nvarchar(10) not null
> )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> A' )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> B' )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> C' )
> insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
> A' )
> insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
> B' )
> insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
> A' )
> select distinct
> individual_name,
> list = substring(
> ( select '/' + list_name as [text()]
> from mailing_list m2
> where m2.individual_name = m1.individual_name
> for xml path(''), elements )
> , 2, 100 )
> from mailing_list m1
> gives...
> alex r List A
> joe r List A/List B
> tony r List A/List B/List C
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:O6Kep5cmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>|||What version of SQL Server are you using? If you are using 2005 then use the
FOR XML below because the WHILE loops are iterative whereas the FOR XML is
set orientated so will perform signifcantly better.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:Oe%23bOEdmGHA.5040@.TK2MSFTNGP04.phx.gbl...
> Hi Tony
> Thanks for your reply, is there something more dynamic, I've been told to
> try and use a WHILE loop and use the ROWCOUNT, but not sure how to
> implment
> this?
> Kind Regards
> Ricky
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:%23m4EDAdmGHA.2204@.TK2MSFTNGP03.phx.gbl...
> SQL
>|||I'm using SS2K..
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eTkAmQdmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server are you using? If you are using 2005 then use
the
> FOR XML below because the WHILE loops are iterative whereas the FOR XML is
> set orientated so will perform signifcantly better.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:Oe%23bOEdmGHA.5040@.TK2MSFTNGP04.phx.gbl...
to
'List
'List
a
>|||Ok, here you go....
The basis of this is that you don't have that many Product's so you can
dynamically build the SQL using a cursor, so the performance comes from
orders of magnitude, for instance - far more performant to run around a
cursor once for just 5 rows then to run round a cursor once for every single
policy ref!
I'll leave you with figuring out how to get rid of the '/' on the end of the
string, should just be substring and datalength - if you get stuck then post
back.
declare cur cursor for
select distinct Product
from SourceData
declare @.product char(1)
declare @.sql_case varchar(8000)
set @.sql_case = ''
open cur
fetch next from cur into @.product
while @.@.fetch_status = 0
begin
set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
end +
'case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = ''' + @.Product +
''' ) then '''+ @.Product + '/'' else '''' end'
fetch next from cur into @.product
end
deallocate cur
declare @.sql varchar(8000)
set @.sql = '
select PolicyRef,
Concat = ' + @.sql_case + '
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
order by PolicyRef'
print @.sql
exec( @.sql )
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
> I'm using SS2K..
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eTkAmQdmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> the
> SQL
> to
> 'List
> 'List
> a
>|||Ok, so rather than leave the job half done...
declare cur cursor for
select distinct Product
from SourceData
declare @.product char(1)
declare @.sql_case varchar(8000)
set @.sql_case = ''
open cur
fetch next from cur into @.product
while @.@.fetch_status = 0
begin
set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
end +
'case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = ''' + @.Product +
''' ) then '''+ @.Product + '/'' else '''' end'
fetch next from cur into @.product
end
deallocate cur
declare @.sql varchar(8000)
set @.sql = '
select PolicyRef,
Concat = substring( Concat, 1, len( Concat ) - 1 )
from (
select PolicyRef,
Concat = ' + @.sql_case + '
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
) as c
order by PolicyRef'
print @.sql
exec( @.sql )
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Ok, here you go....
> The basis of this is that you don't have that many Product's so you can
> dynamically build the SQL using a cursor, so the performance comes from
> orders of magnitude, for instance - far more performant to run around a
> cursor once for just 5 rows then to run round a cursor once for every
> single policy ref!
> I'll leave you with figuring out how to get rid of the '/' on the end of
> the string, should just be substring and datalength - if you get stuck
> then post back.
> declare cur cursor for
> select distinct Product
> from SourceData
> declare @.product char(1)
> declare @.sql_case varchar(8000)
> set @.sql_case = ''
> open cur
> fetch next from cur into @.product
> while @.@.fetch_status = 0
> begin
> set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
> end +
> 'case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = ''' + @.Product +
> ''' ) then '''+ @.Product + '/'' else '''' end'
> fetch next from cur into @.product
> end
> deallocate cur
> declare @.sql varchar(8000)
> set @.sql = '
> select PolicyRef,
> Concat = ' + @.sql_case + '
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> order by PolicyRef'
> print @.sql
> exec( @.sql )
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
>|||Hi Tony
Thank you for the posting, will there be a performance issue, if this is run
for many different policies?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Ok, here you go....
> The basis of this is that you don't have that many Product's so you can
> dynamically build the SQL using a cursor, so the performance comes from
> orders of magnitude, for instance - far more performant to run around a
> cursor once for just 5 rows then to run round a cursor once for every
single
> policy ref!
> I'll leave you with figuring out how to get rid of the '/' on the end of
the
> string, should just be substring and datalength - if you get stuck then
post
> back.
> declare cur cursor for
> select distinct Product
> from SourceData
> declare @.product char(1)
> declare @.sql_case varchar(8000)
> set @.sql_case = ''
> open cur
> fetch next from cur into @.product
> while @.@.fetch_status = 0
> begin
> set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
> end +
> 'case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = ''' + @.Product +
> ''' ) then '''+ @.Product + '/'' else '''' end'
> fetch next from cur into @.product
> end
> deallocate cur
> declare @.sql varchar(8000)
> set @.sql = '
> select PolicyRef,
> Concat = ' + @.sql_case + '
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> order by PolicyRef'
> print @.sql
> exec( @.sql )
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
use
a
told
http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
from
>|||The actual SQL will produce this which is what does the work...
How many Products do you have?
This SQL will beat cursor or looping by orders of magnitude - try it.
Was the DDL you posted accurate to your own system, if not then best post
the DDL (including indexes) and I'll check to see if you'll get a good
plan...
select PolicyRef,
Concat = substring( Concat, 1, len( Concat ) - 1 )
from (
select PolicyRef,
Concat = case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'B' ) then 'B/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'C' ) then 'C/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'M' ) then 'M/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'N' ) then 'N/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'S' ) then 'S/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'T' ) then 'T/' else
'' end
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
) as c
order by PolicyRef
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:%23D2jhldmGHA.492@.TK2MSFTNGP05.phx.gbl...
> Hi Tony
> Thank you for the posting, will there be a performance issue, if this is
> run
> for many different policies?
> Kind Regards
> Ricky
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> single
> the
> post
> SQL
> use
> a
> told
> http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
> from
>|||Hi Tony
The amount of products in a policy can range from anywhere to 1 (default) to
about 5?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%237tx4pdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
> The actual SQL will produce this which is what does the work...
> How many Products do you have?
> This SQL will beat cursor or looping by orders of magnitude - try it.
> Was the DDL you posted accurate to your own system, if not then best post
> the DDL (including indexes) and I'll check to see if you'll get a good
> plan...
> select PolicyRef,
> Concat = substring( Concat, 1, len( Concat ) - 1 )
> from (
> select PolicyRef,
> Concat = case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'B' ) then 'B/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'C' ) then 'C/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'M' ) then 'M/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'N' ) then 'N/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'S' ) then 'S/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'T' ) then 'T/'
else
> '' end
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> ) as c
> order by PolicyRef
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:%23D2jhldmGHA.492@.TK2MSFTNGP05.phx.gbl...
of
'+'
a
from
r',
r',
a
luck.
>
I am a recordset which I would like to extract a field and make a string
from it, by appending values to it.
e.g
PolicyRef Product
C001 M
C001 B
C001 S
C002 N
C002 C
C002 T
Ideally, what I need is the products in one field:
e.g
PolicyRef Product
C001 M/B/S
C002 N/C/T
I am trying to create a Loop construct, but not having much luck.
Any ideas?
Kind Regards
RickyIf you are using SQL Server 2005 you can do this...
(taken from my blog entry:
http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
create table mailing_list (
individual_name nvarchar(100) not null,
list_name nvarchar(10) not null
)
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
C' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
A' )
select distinct
individual_name,
list = substring(
( select '/' + list_name as [text()]
from mailing_list m2
where m2.individual_name = m1.individual_name
for xml path(''), elements )
, 2, 100 )
from mailing_list m1
gives...
alex r List A
joe r List A/List B
tony r List A/List B/List C
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:O6Kep5cmGHA.4716@.TK2MSFTNGP04.phx.gbl...
> Hi
> I am a recordset which I would like to extract a field and make a string
> from it, by appending values to it.
> e.g
> PolicyRef Product
> C001 M
> C001 B
> C001 S
> C002 N
> C002 C
> C002 T
> Ideally, what I need is the products in one field:
> e.g
> PolicyRef Product
> C001 M/B/S
> C002 N/C/T
> I am trying to create a Loop construct, but not having much luck.
> Any ideas?
> Kind Regards
> Ricky
>|||Hi Tony
Thanks for your reply, is there something more dynamic, I've been told to
try and use a WHILE loop and use the ROWCOUNT, but not sure how to implment
this?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%23m4EDAdmGHA.2204@.TK2MSFTNGP03.phx.gbl...
> If you are using SQL Server 2005 you can do this...
> (taken from my blog entry:
> http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
> create table mailing_list (
> individual_name nvarchar(100) not null,
> list_name nvarchar(10) not null
> )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> A' )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> B' )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> C' )
> insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
> A' )
> insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
> B' )
> insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
> A' )
> select distinct
> individual_name,
> list = substring(
> ( select '/' + list_name as [text()]
> from mailing_list m2
> where m2.individual_name = m1.individual_name
> for xml path(''), elements )
> , 2, 100 )
> from mailing_list m1
> gives...
> alex r List A
> joe r List A/List B
> tony r List A/List B/List C
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:O6Kep5cmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>|||What version of SQL Server are you using? If you are using 2005 then use the
FOR XML below because the WHILE loops are iterative whereas the FOR XML is
set orientated so will perform signifcantly better.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:Oe%23bOEdmGHA.5040@.TK2MSFTNGP04.phx.gbl...
> Hi Tony
> Thanks for your reply, is there something more dynamic, I've been told to
> try and use a WHILE loop and use the ROWCOUNT, but not sure how to
> implment
> this?
> Kind Regards
> Ricky
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:%23m4EDAdmGHA.2204@.TK2MSFTNGP03.phx.gbl...
> SQL
>|||I'm using SS2K..
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eTkAmQdmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server are you using? If you are using 2005 then use
the
> FOR XML below because the WHILE loops are iterative whereas the FOR XML is
> set orientated so will perform signifcantly better.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:Oe%23bOEdmGHA.5040@.TK2MSFTNGP04.phx.gbl...
to
'List
'List
a
>|||Ok, here you go....
The basis of this is that you don't have that many Product's so you can
dynamically build the SQL using a cursor, so the performance comes from
orders of magnitude, for instance - far more performant to run around a
cursor once for just 5 rows then to run round a cursor once for every single
policy ref!
I'll leave you with figuring out how to get rid of the '/' on the end of the
string, should just be substring and datalength - if you get stuck then post
back.
declare cur cursor for
select distinct Product
from SourceData
declare @.product char(1)
declare @.sql_case varchar(8000)
set @.sql_case = ''
open cur
fetch next from cur into @.product
while @.@.fetch_status = 0
begin
set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
end +
'case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = ''' + @.Product +
''' ) then '''+ @.Product + '/'' else '''' end'
fetch next from cur into @.product
end
deallocate cur
declare @.sql varchar(8000)
set @.sql = '
select PolicyRef,
Concat = ' + @.sql_case + '
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
order by PolicyRef'
print @.sql
exec( @.sql )
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
> I'm using SS2K..
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eTkAmQdmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> the
> SQL
> to
> 'List
> 'List
> a
>|||Ok, so rather than leave the job half done...
declare cur cursor for
select distinct Product
from SourceData
declare @.product char(1)
declare @.sql_case varchar(8000)
set @.sql_case = ''
open cur
fetch next from cur into @.product
while @.@.fetch_status = 0
begin
set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
end +
'case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = ''' + @.Product +
''' ) then '''+ @.Product + '/'' else '''' end'
fetch next from cur into @.product
end
deallocate cur
declare @.sql varchar(8000)
set @.sql = '
select PolicyRef,
Concat = substring( Concat, 1, len( Concat ) - 1 )
from (
select PolicyRef,
Concat = ' + @.sql_case + '
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
) as c
order by PolicyRef'
print @.sql
exec( @.sql )
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Ok, here you go....
> The basis of this is that you don't have that many Product's so you can
> dynamically build the SQL using a cursor, so the performance comes from
> orders of magnitude, for instance - far more performant to run around a
> cursor once for just 5 rows then to run round a cursor once for every
> single policy ref!
> I'll leave you with figuring out how to get rid of the '/' on the end of
> the string, should just be substring and datalength - if you get stuck
> then post back.
> declare cur cursor for
> select distinct Product
> from SourceData
> declare @.product char(1)
> declare @.sql_case varchar(8000)
> set @.sql_case = ''
> open cur
> fetch next from cur into @.product
> while @.@.fetch_status = 0
> begin
> set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
> end +
> 'case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = ''' + @.Product +
> ''' ) then '''+ @.Product + '/'' else '''' end'
> fetch next from cur into @.product
> end
> deallocate cur
> declare @.sql varchar(8000)
> set @.sql = '
> select PolicyRef,
> Concat = ' + @.sql_case + '
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> order by PolicyRef'
> print @.sql
> exec( @.sql )
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
>|||Hi Tony
Thank you for the posting, will there be a performance issue, if this is run
for many different policies?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Ok, here you go....
> The basis of this is that you don't have that many Product's so you can
> dynamically build the SQL using a cursor, so the performance comes from
> orders of magnitude, for instance - far more performant to run around a
> cursor once for just 5 rows then to run round a cursor once for every
single
> policy ref!
> I'll leave you with figuring out how to get rid of the '/' on the end of
the
> string, should just be substring and datalength - if you get stuck then
post
> back.
> declare cur cursor for
> select distinct Product
> from SourceData
> declare @.product char(1)
> declare @.sql_case varchar(8000)
> set @.sql_case = ''
> open cur
> fetch next from cur into @.product
> while @.@.fetch_status = 0
> begin
> set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
> end +
> 'case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = ''' + @.Product +
> ''' ) then '''+ @.Product + '/'' else '''' end'
> fetch next from cur into @.product
> end
> deallocate cur
> declare @.sql varchar(8000)
> set @.sql = '
> select PolicyRef,
> Concat = ' + @.sql_case + '
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> order by PolicyRef'
> print @.sql
> exec( @.sql )
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
use
a
told
http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
from
>|||The actual SQL will produce this which is what does the work...
How many Products do you have?
This SQL will beat cursor or looping by orders of magnitude - try it.
Was the DDL you posted accurate to your own system, if not then best post
the DDL (including indexes) and I'll check to see if you'll get a good
plan...
select PolicyRef,
Concat = substring( Concat, 1, len( Concat ) - 1 )
from (
select PolicyRef,
Concat = case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'B' ) then 'B/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'C' ) then 'C/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'M' ) then 'M/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'N' ) then 'N/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'S' ) then 'S/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'T' ) then 'T/' else
'' end
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
) as c
order by PolicyRef
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:%23D2jhldmGHA.492@.TK2MSFTNGP05.phx.gbl...
> Hi Tony
> Thank you for the posting, will there be a performance issue, if this is
> run
> for many different policies?
> Kind Regards
> Ricky
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> single
> the
> post
> SQL
> use
> a
> told
> http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
> from
>|||Hi Tony
The amount of products in a policy can range from anywhere to 1 (default) to
about 5?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%237tx4pdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
> The actual SQL will produce this which is what does the work...
> How many Products do you have?
> This SQL will beat cursor or looping by orders of magnitude - try it.
> Was the DDL you posted accurate to your own system, if not then best post
> the DDL (including indexes) and I'll check to see if you'll get a good
> plan...
> select PolicyRef,
> Concat = substring( Concat, 1, len( Concat ) - 1 )
> from (
> select PolicyRef,
> Concat = case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'B' ) then 'B/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'C' ) then 'C/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'M' ) then 'M/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'N' ) then 'N/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'S' ) then 'S/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'T' ) then 'T/'
else
> '' end
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> ) as c
> order by PolicyRef
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:%23D2jhldmGHA.492@.TK2MSFTNGP05.phx.gbl...
of
'+'
a
from
r',
r',
a
luck.
>
Looping through a list of values
Hi all
How do i loop through a list of values returned by a select statement using TSQL. maybe using a while statement.
cheers
james :)In T-SQL? Even heart of a cursor?|||The only way that i could think of doing this is perhaps to use the 'IN' keyword within the 'WHERE' CLAUSE|||Originally posted by DoktorBlue
In T-SQL? Even heart of a cursor?
i'll have to look that up tonight. Never used cursors before.
thanx dok|||With a cursor, you can loop through a recordset, where the cursor references to one record at a time. The best way of understanding is to look for an example.
See DECLARE CURSOR (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp) for the syntax and soem examples in SQL Server 2000. Version 7 is similar.|||Originally posted by nano_electronix
Hi all
How do i loop through a list of values returned by a select statement using TSQL. maybe using a while statement.
cheers
james :)
Yep.. the only way to do a while loop is by using cursor.. If there is another much better way, I'd like to know too..|||Just save result of query to temp table with IDENTITY column and make loop by this field...
Good luck|||give us more details, maybe there is no need to do the loop at all.|||Hi all
I did it... woohoo, thanx for all your help guys.
This is probably the longest TSQL i've ever written, 200+ lines and most importantly it works.
Special thanx to dok for pointing out CURSOR as a looping solution, it works magics.
Cheers
James
PS: Here is the single store procedure implementing the application logic i intended. (just to show off) hahahahah :D
CREATE PROCEDURE GenerateTimesheets
(
@.JobID decimal
)
AS
BEGIN
-- Constants
DECLARE @.AllocatedTime decimal
-- Used for looping through recordset with CURSORs
DECLARE @.AccumulatedTime decimal -- Accumulated time spent on a particular job
DECLARE @.SpentTime decimal -- Time spent on each subtask of a job
DECLARE @.TimesheetDate datetime
DECLARE @.JobUpdateID decimal
DECLARE @.TimesheetID decimal
DECLARE @.TimesheetType int
-- Initialize variables
SELECT @.AllocatedTime = allocatedTime
FROM Job WHERE jobID = @.JobID
SET @.AccumulatedTime = 0
SET @.TimesheetType = 1
-- Define CURSOR for each distinct day
-- Because timesheets are generated on a daily basis.
DECLARE date_cursor CURSOR FOR
SELECT DISTINCT CAST( CONVERT(varchar(10), dateSubmitted, 120) as datetime)
FROM jobUpdate WHERE jobUpdate.jobID = @.JobID
OPEN date_cursor
FETCH NEXT FROM date_cursor
INTO @.TimesheetDate
-- Create timesheets for each day
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Create a new timesheet for each day
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
-- Return the new timesheetID for reference
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType
-- Create new cursor to loop through jobupdates for a particular day
DECLARE jobupdate_cursor CURSOR FOR
SELECT spentTime, jobUpdateID
FROM jobUpdate
WHERE @.TimesheetDate = CAST(CONVERT(varchar(10), dateSubmitted, 120) as datetime)
AND jobID = @.JobID
OPEN jobupdate_cursor
FETCH NEXT FROM jobupdate_cursor
INTO @.SpentTime, @.JobUpdateID
-- Loop through and process all jobupdates for the day
-- creating new timesheets for CT and TA if necessary
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.AccumulatedTime = @.AccumulatedTime + @.SpentTime
------------
-- if currently in standard time phase
------------
if @.TimesheetType = 1
BEGIN
IF @.AccumulatedTime >= @.AllocatedTime + 2
BEGIN
-- Create new timesheet approval timesheet
SET @.TimesheetType = 3
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType
-- Update the current job with timesheet reference id
END
ELSE IF @.AccumulatedTime >= @.AllocatedTime
BEGIN
-- Create new completion time timesheet
SET @.TimesheetType = 2
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
ELSE
BEGIN
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
END
-------------
-- if currently in completion time phase
-------------
ELSE IF @.TimesheetType = 2
BEGIN
IF @.AccumulatedTime >= @.AllocatedTime + 2
BEGIN
-- Create new timesheet approval timesheet
SET @.TimesheetType = 3
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
ELSE
BEGIN
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
END
--------------
-- if currently in timesheet approval phase
--------------
ELSE IF @.TimesheetType = 3
BEGIN
-- Update the current job with the timesheet refernce id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
FETCH NEXT FROM jobupdate_cursor
INTO @.SpentTime, @.JobUpdateID
END
CLOSE jobupdate_cursor
DEALLOCATE jobupdate_cursor
FETCH NEXT FROM date_cursor
INTO @.TimesheetDate
END
CLOSE date_cursor
DEALLOCATE date_cursor
END|||Hi James, very impressive! You are a fast learner.|||Originally posted by DoktorBlue
Hi James, very impressive! You are a fast learner.
Thanx dok !!!
I was wondering if you also know stuffs on Oracle as I will be working with oracle very soon, next monday actually. I guess I will asking questions in the Oracle forum, but it would be great if you are a Oracle expert too.
You have been very helpful, thanx again.
Cheers
James|||I think you have to be careful with cursors as they are not the most efficient methods available. In fact most problems can be solved without the need for cursors.
If you provide us with what you're trying to do, maybe someone will provide you with a set based solutions instead.|||I think James is quickly-learning poor programming habits. I can't believe this code does what he thinks it is doing, there are so many oportunities for errors. Why is TimeSheetType not reset to 1 when processing starts for a new day? I can't believe you need nested cursors to do whatever it is you are trying to do.
I can't figure out the logic from the code, and that at least is going to cause problems for whoever comes along and has to revise or debug it.
Add my vote to rdjabarov's and Crespo-n00b's that there ought to be a better solution.
blindman|||everyone learns from mistakes, won't take long ;-)|||Why so negative? James just took a step into the cursor world, and you should be glad with it. And everybody, which is claiming to know a better solution than that, what James showed us, is invited to do a proposal. But just saying, that there ought to be a better solution, is too cheap, because this is a TRUE statement in almost all cases.|||Taking a step into the cursor world is too often a step in the wrong direction. Cursors are too often a crutch used by procedural programmers. James needs to learn set-based processing, and their are a lot of individuals on this forum who would be happy to help him.
You may be impressed by volumes of code, but I'm impressed by short, elegant code that is simple, easily understood, well commented and readily debugged.
Inside every large program is a small program screaming to get out.
blindman|||Why don't you let it out, I'm curious to see whether you can add practise to you theory.|||Well, I've managed to shorten a lot of your code, and I and other members will help James if he asks.
I'll help you if you ask, too.
blindman|||You should go into politics. I challanged you to come with an elegant solution, but you didn't probably even understand James' problem. So, what's your point? Desperately referring to other threads? Hiding behind "other members"? The only one, who really helped James, was me.
About cursors: they are a usual mean to express functionality, and they are not bad at advance. It up to the user to use or abuse this functionality, like you can also easily write a "query from hell". The main point about cursors is, that the user takes responsibility of the execution plan from the Analyzer.
Blindman, if you want to respond, please do me a favour and focus on the issue of this thread, not other threads, not aiming at me, just show your short elegant solution, which would be a real contribution.|||Originally posted by DoktorBlue
Why so negative? James just took a step into the cursor world, and you should be glad with it. And everybody, which is claiming to know a better solution than that, what James showed us, is invited to do a proposal. But just saying, that there ought to be a better solution, is too cheap, because this is a TRUE statement in almost all cases.
What you say is true to a certain extent. There are view few cases where Cursors are needed and I believe that if people paid more attention to database concepts they would invariably find a set based solution to most of their SQL problems.
As the previous poster stated, it is hard to follow the logic of the cursor, and to make matters more complicated he has also used a nested cursor!
What we need is some CREATE TABLE statements and a few example records and I'm almost 100% sure that one of us here will come up with a much more efficient set based solution.
My 2 cents.|||I am also totally in agreement with blindman .
I would rather not use cursors ... let alone nested cursors , if there was an alternative way.
You should go into politics. I challanged you to come with an elegant solution, but you didn't probably even understand James' problem. So, what's your point? Desperately referring to other threads? Hiding behind "other members"? The only one, who really helped James, was me.
well , DoktorBlue , whats all the fighting for ... we are all here to share our knowledge .. dosent matter who is helping whom ... till the other guy gets the solution. Whats the problem in a little friendly discussion.|||I agree with DoktorBlue that you shouldn't continually jump onto threads and say that someone's solution is bad without offering a solution. The anti-cursor evangelists have done the same to me. I asked for a solution but they didn't provide one.
I'm actually a junior TSQL guy but even I can see that maybe the outer loop of this guy's code could be a WHILE loop looping through days (most timesheets don't skip days in the middle) but it would appear some folks are too scared to risk their lofty positions by actually suggesting some code. You'd think that this would be a simple enough of an example that they could put something out there....nope.
How do you get those stars under your name anyway...|||One more thing ...
If it is really a growing problem that cursors are often used inappropriately (because newbies are procedural rather than focused on how datasets work or for whatever reason), why not write a quick article and post it on the web (if it doesn't exist already). The article would show "practical" examples of common mistakes and how to avoid them.
That way, when you see samples of inappropriate cursor usage in these forums you can just tell the poster "You may benefit from this article" and give'em the URL. I could personally benefit from this article right now.
It's cool that folks exist out there like you guys who know the theories of what is good and bad but if nobody can explain in practical terms when they apply then...
Sorry for the rant.|||bill_dev,
I and other members have offered to provide more assistance with this problem, but we need more details about the requirements. We are not going to spend our time developing solutions (for free) without requirements. That is just chasing shadows.
Truth is, I have spent a lot of time looking over James' code, and as I stated quite a while back the first problem is that it is difficult to figure out what the code is supposed to be doing. Readability is important, especially if you have ever inherited a project, or even had to debug or modify something written six or twelve months before.
Cursors are crutches. They are slow, and awkward, and if you use them too much or too often they get painful. But like crutches, sometimes they are absolutely necessary and nothing else will do.
blindman|||I have to say I am in the anti-cursor faction. If you have a cursor on your system, try running it with task manager running, and watch the cpu graph. Now imagine that cursor being run by 5 or 6 other people at the same time. Truth is cursors are not scalable.
Another reason I am against cursors is that it promotes putting business logic directly on the database. If you know you are only going to have a very few records in these tables (under say 1000), then ok, the cursor can be used if there is no other way. But if you think, or even dream you will be going to hundreds of users, then the business logic has to come off the database. This is a job for application servers. Think about it this way. You can have many application servers, but you are stuck with one database.
To top it off, in many companies, you are not only stuck with one database server, but you have to share that server with all the other applications' databases. The performance on these servers is generally equal to the worst performing application.
As far as improvements in this particular procedure go, the only thing I have so far is to use select @.@.identity after the timesheet inserts, rather than select the new identity right off the table. If you have a million timesheets, you will see the performance degrade. If you have no index on the fields in the where clause, you guarantee that you have performance problems. Just things to watch for.
And, yes, I am still thinking about ways to eliminate the cursor entirely. So far, my best bet is to use a perl or VB script to do the actual logic.|||Originally posted by snail
Just save result of query to temp table with IDENTITY column and make loop by this field...
Good luck
snail's suggestion is good. Using a temp table is simpler than a cursor.
Here is a complete example:
-- Update area codes for people in the 415 area code
-- Set area code to 234
USE pubs
GO
-- Show values before update
SELECT au_id, phone
FROM authors
ORDER BY au_id
DECLARE @.error int
DECLARE @.authorCt int
DECLARE @.loopCt int
DECLARE @.authorsToUpdate TABLE (
EntryID integer IDENTITY(1,1),
au_id varchar(11),
phone char(12)
)
INSERT INTO @.authorsToUpdate (
au_id,
phone
)
SELECT
au_id,
REPLACE(phone, '415 ', '234 ')
FROM authors
WHERE phone LIKE '415 %'
SELECT
@.error = @.@.error,
@.authorCt = @.@.rowcount
-- Error check
IF @.authorCt > 0 BEGIN
SET @.loopCt = 0
BEGIN TRAN
WHILE @.loopCt < @.authorCt BEGIN
SET @.loopCt = @.loopCt + 1
UPDATE a
SET a.phone = u.phone
FROM authors AS a
JOIN @.authorsToUpdate AS u
ON a.au_id = u.au_id
WHERE u.EntryID = @.loopCt
-- Error check
END
-- Show values after update
SELECT au_id, phone
FROM authors
ORDER BY au_id
ROLLBACK TRAN
-- COMMIT TRAN
END
GO|||Cool. Very useful...Thanks|||Finally somebody with more than a story, but a proposal. Bravo!|||Thanx all for all your feedbacks.
I haven't looked this up for the last 2 days, so i missed all the discussions.
I think the suggestion of using temporary table is probably the way to get rid of cursor for certain cases. For the logic that i am trying to implement, using cursor is probably necessary and the most intuitive, but because the small amout of records that i have to process, there is no real performance issue for me.
The program logic that i tried to implement is to basically generate timesheets from jobupdate entries entered by developers. Basically how our company works is like follows.
1. Client submits a request
2. Project manager receives request and allocate Jobs to differnt developers. Each job has an allocated time.
3. Developers will update their job justifying what they did everytime they finish a task for that job, these jobupdates will include the time spent on this task
when ever a job is completed a number of timesheets would be generated from all the jobupdates submitted . for this particular company, they want to generate timesheets on a per job per day basis, what's more, all the jobupdates that exceeds the allocated time will be put into a differnt timesheet. so normally there would only be 1 timesheet per day when all the jobupdates submitted for a particular job are within allocated time. once allocated time is exceeded, there could be a maximum of 3 different timesheets generated for a particular day. Thus the reason that i have to process each entry individual and thus the need for cursor.
Because of this business logic, there is almost no way of avoiding cursor.
Cheers
James
PS: I love you guys, I have learnt a lot from dok and blindman and a number of other people since i joined. dbforums rocks because of you!! :)
How do i loop through a list of values returned by a select statement using TSQL. maybe using a while statement.
cheers
james :)In T-SQL? Even heart of a cursor?|||The only way that i could think of doing this is perhaps to use the 'IN' keyword within the 'WHERE' CLAUSE|||Originally posted by DoktorBlue
In T-SQL? Even heart of a cursor?
i'll have to look that up tonight. Never used cursors before.
thanx dok|||With a cursor, you can loop through a recordset, where the cursor references to one record at a time. The best way of understanding is to look for an example.
See DECLARE CURSOR (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp) for the syntax and soem examples in SQL Server 2000. Version 7 is similar.|||Originally posted by nano_electronix
Hi all
How do i loop through a list of values returned by a select statement using TSQL. maybe using a while statement.
cheers
james :)
Yep.. the only way to do a while loop is by using cursor.. If there is another much better way, I'd like to know too..|||Just save result of query to temp table with IDENTITY column and make loop by this field...
Good luck|||give us more details, maybe there is no need to do the loop at all.|||Hi all
I did it... woohoo, thanx for all your help guys.
This is probably the longest TSQL i've ever written, 200+ lines and most importantly it works.
Special thanx to dok for pointing out CURSOR as a looping solution, it works magics.
Cheers
James
PS: Here is the single store procedure implementing the application logic i intended. (just to show off) hahahahah :D
CREATE PROCEDURE GenerateTimesheets
(
@.JobID decimal
)
AS
BEGIN
-- Constants
DECLARE @.AllocatedTime decimal
-- Used for looping through recordset with CURSORs
DECLARE @.AccumulatedTime decimal -- Accumulated time spent on a particular job
DECLARE @.SpentTime decimal -- Time spent on each subtask of a job
DECLARE @.TimesheetDate datetime
DECLARE @.JobUpdateID decimal
DECLARE @.TimesheetID decimal
DECLARE @.TimesheetType int
-- Initialize variables
SELECT @.AllocatedTime = allocatedTime
FROM Job WHERE jobID = @.JobID
SET @.AccumulatedTime = 0
SET @.TimesheetType = 1
-- Define CURSOR for each distinct day
-- Because timesheets are generated on a daily basis.
DECLARE date_cursor CURSOR FOR
SELECT DISTINCT CAST( CONVERT(varchar(10), dateSubmitted, 120) as datetime)
FROM jobUpdate WHERE jobUpdate.jobID = @.JobID
OPEN date_cursor
FETCH NEXT FROM date_cursor
INTO @.TimesheetDate
-- Create timesheets for each day
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Create a new timesheet for each day
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
-- Return the new timesheetID for reference
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType
-- Create new cursor to loop through jobupdates for a particular day
DECLARE jobupdate_cursor CURSOR FOR
SELECT spentTime, jobUpdateID
FROM jobUpdate
WHERE @.TimesheetDate = CAST(CONVERT(varchar(10), dateSubmitted, 120) as datetime)
AND jobID = @.JobID
OPEN jobupdate_cursor
FETCH NEXT FROM jobupdate_cursor
INTO @.SpentTime, @.JobUpdateID
-- Loop through and process all jobupdates for the day
-- creating new timesheets for CT and TA if necessary
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.AccumulatedTime = @.AccumulatedTime + @.SpentTime
------------
-- if currently in standard time phase
------------
if @.TimesheetType = 1
BEGIN
IF @.AccumulatedTime >= @.AllocatedTime + 2
BEGIN
-- Create new timesheet approval timesheet
SET @.TimesheetType = 3
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType
-- Update the current job with timesheet reference id
END
ELSE IF @.AccumulatedTime >= @.AllocatedTime
BEGIN
-- Create new completion time timesheet
SET @.TimesheetType = 2
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
ELSE
BEGIN
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
END
-------------
-- if currently in completion time phase
-------------
ELSE IF @.TimesheetType = 2
BEGIN
IF @.AccumulatedTime >= @.AllocatedTime + 2
BEGIN
-- Create new timesheet approval timesheet
SET @.TimesheetType = 3
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
ELSE
BEGIN
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
END
--------------
-- if currently in timesheet approval phase
--------------
ELSE IF @.TimesheetType = 3
BEGIN
-- Update the current job with the timesheet refernce id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
FETCH NEXT FROM jobupdate_cursor
INTO @.SpentTime, @.JobUpdateID
END
CLOSE jobupdate_cursor
DEALLOCATE jobupdate_cursor
FETCH NEXT FROM date_cursor
INTO @.TimesheetDate
END
CLOSE date_cursor
DEALLOCATE date_cursor
END|||Hi James, very impressive! You are a fast learner.|||Originally posted by DoktorBlue
Hi James, very impressive! You are a fast learner.
Thanx dok !!!
I was wondering if you also know stuffs on Oracle as I will be working with oracle very soon, next monday actually. I guess I will asking questions in the Oracle forum, but it would be great if you are a Oracle expert too.
You have been very helpful, thanx again.
Cheers
James|||I think you have to be careful with cursors as they are not the most efficient methods available. In fact most problems can be solved without the need for cursors.
If you provide us with what you're trying to do, maybe someone will provide you with a set based solutions instead.|||I think James is quickly-learning poor programming habits. I can't believe this code does what he thinks it is doing, there are so many oportunities for errors. Why is TimeSheetType not reset to 1 when processing starts for a new day? I can't believe you need nested cursors to do whatever it is you are trying to do.
I can't figure out the logic from the code, and that at least is going to cause problems for whoever comes along and has to revise or debug it.
Add my vote to rdjabarov's and Crespo-n00b's that there ought to be a better solution.
blindman|||everyone learns from mistakes, won't take long ;-)|||Why so negative? James just took a step into the cursor world, and you should be glad with it. And everybody, which is claiming to know a better solution than that, what James showed us, is invited to do a proposal. But just saying, that there ought to be a better solution, is too cheap, because this is a TRUE statement in almost all cases.|||Taking a step into the cursor world is too often a step in the wrong direction. Cursors are too often a crutch used by procedural programmers. James needs to learn set-based processing, and their are a lot of individuals on this forum who would be happy to help him.
You may be impressed by volumes of code, but I'm impressed by short, elegant code that is simple, easily understood, well commented and readily debugged.
Inside every large program is a small program screaming to get out.
blindman|||Why don't you let it out, I'm curious to see whether you can add practise to you theory.|||Well, I've managed to shorten a lot of your code, and I and other members will help James if he asks.
I'll help you if you ask, too.
blindman|||You should go into politics. I challanged you to come with an elegant solution, but you didn't probably even understand James' problem. So, what's your point? Desperately referring to other threads? Hiding behind "other members"? The only one, who really helped James, was me.
About cursors: they are a usual mean to express functionality, and they are not bad at advance. It up to the user to use or abuse this functionality, like you can also easily write a "query from hell". The main point about cursors is, that the user takes responsibility of the execution plan from the Analyzer.
Blindman, if you want to respond, please do me a favour and focus on the issue of this thread, not other threads, not aiming at me, just show your short elegant solution, which would be a real contribution.|||Originally posted by DoktorBlue
Why so negative? James just took a step into the cursor world, and you should be glad with it. And everybody, which is claiming to know a better solution than that, what James showed us, is invited to do a proposal. But just saying, that there ought to be a better solution, is too cheap, because this is a TRUE statement in almost all cases.
What you say is true to a certain extent. There are view few cases where Cursors are needed and I believe that if people paid more attention to database concepts they would invariably find a set based solution to most of their SQL problems.
As the previous poster stated, it is hard to follow the logic of the cursor, and to make matters more complicated he has also used a nested cursor!
What we need is some CREATE TABLE statements and a few example records and I'm almost 100% sure that one of us here will come up with a much more efficient set based solution.
My 2 cents.|||I am also totally in agreement with blindman .
I would rather not use cursors ... let alone nested cursors , if there was an alternative way.
You should go into politics. I challanged you to come with an elegant solution, but you didn't probably even understand James' problem. So, what's your point? Desperately referring to other threads? Hiding behind "other members"? The only one, who really helped James, was me.
well , DoktorBlue , whats all the fighting for ... we are all here to share our knowledge .. dosent matter who is helping whom ... till the other guy gets the solution. Whats the problem in a little friendly discussion.|||I agree with DoktorBlue that you shouldn't continually jump onto threads and say that someone's solution is bad without offering a solution. The anti-cursor evangelists have done the same to me. I asked for a solution but they didn't provide one.
I'm actually a junior TSQL guy but even I can see that maybe the outer loop of this guy's code could be a WHILE loop looping through days (most timesheets don't skip days in the middle) but it would appear some folks are too scared to risk their lofty positions by actually suggesting some code. You'd think that this would be a simple enough of an example that they could put something out there....nope.
How do you get those stars under your name anyway...|||One more thing ...
If it is really a growing problem that cursors are often used inappropriately (because newbies are procedural rather than focused on how datasets work or for whatever reason), why not write a quick article and post it on the web (if it doesn't exist already). The article would show "practical" examples of common mistakes and how to avoid them.
That way, when you see samples of inappropriate cursor usage in these forums you can just tell the poster "You may benefit from this article" and give'em the URL. I could personally benefit from this article right now.
It's cool that folks exist out there like you guys who know the theories of what is good and bad but if nobody can explain in practical terms when they apply then...
Sorry for the rant.|||bill_dev,
I and other members have offered to provide more assistance with this problem, but we need more details about the requirements. We are not going to spend our time developing solutions (for free) without requirements. That is just chasing shadows.
Truth is, I have spent a lot of time looking over James' code, and as I stated quite a while back the first problem is that it is difficult to figure out what the code is supposed to be doing. Readability is important, especially if you have ever inherited a project, or even had to debug or modify something written six or twelve months before.
Cursors are crutches. They are slow, and awkward, and if you use them too much or too often they get painful. But like crutches, sometimes they are absolutely necessary and nothing else will do.
blindman|||I have to say I am in the anti-cursor faction. If you have a cursor on your system, try running it with task manager running, and watch the cpu graph. Now imagine that cursor being run by 5 or 6 other people at the same time. Truth is cursors are not scalable.
Another reason I am against cursors is that it promotes putting business logic directly on the database. If you know you are only going to have a very few records in these tables (under say 1000), then ok, the cursor can be used if there is no other way. But if you think, or even dream you will be going to hundreds of users, then the business logic has to come off the database. This is a job for application servers. Think about it this way. You can have many application servers, but you are stuck with one database.
To top it off, in many companies, you are not only stuck with one database server, but you have to share that server with all the other applications' databases. The performance on these servers is generally equal to the worst performing application.
As far as improvements in this particular procedure go, the only thing I have so far is to use select @.@.identity after the timesheet inserts, rather than select the new identity right off the table. If you have a million timesheets, you will see the performance degrade. If you have no index on the fields in the where clause, you guarantee that you have performance problems. Just things to watch for.
And, yes, I am still thinking about ways to eliminate the cursor entirely. So far, my best bet is to use a perl or VB script to do the actual logic.|||Originally posted by snail
Just save result of query to temp table with IDENTITY column and make loop by this field...
Good luck
snail's suggestion is good. Using a temp table is simpler than a cursor.
Here is a complete example:
-- Update area codes for people in the 415 area code
-- Set area code to 234
USE pubs
GO
-- Show values before update
SELECT au_id, phone
FROM authors
ORDER BY au_id
DECLARE @.error int
DECLARE @.authorCt int
DECLARE @.loopCt int
DECLARE @.authorsToUpdate TABLE (
EntryID integer IDENTITY(1,1),
au_id varchar(11),
phone char(12)
)
INSERT INTO @.authorsToUpdate (
au_id,
phone
)
SELECT
au_id,
REPLACE(phone, '415 ', '234 ')
FROM authors
WHERE phone LIKE '415 %'
SELECT
@.error = @.@.error,
@.authorCt = @.@.rowcount
-- Error check
IF @.authorCt > 0 BEGIN
SET @.loopCt = 0
BEGIN TRAN
WHILE @.loopCt < @.authorCt BEGIN
SET @.loopCt = @.loopCt + 1
UPDATE a
SET a.phone = u.phone
FROM authors AS a
JOIN @.authorsToUpdate AS u
ON a.au_id = u.au_id
WHERE u.EntryID = @.loopCt
-- Error check
END
-- Show values after update
SELECT au_id, phone
FROM authors
ORDER BY au_id
ROLLBACK TRAN
-- COMMIT TRAN
END
GO|||Cool. Very useful...Thanks|||Finally somebody with more than a story, but a proposal. Bravo!|||Thanx all for all your feedbacks.
I haven't looked this up for the last 2 days, so i missed all the discussions.
I think the suggestion of using temporary table is probably the way to get rid of cursor for certain cases. For the logic that i am trying to implement, using cursor is probably necessary and the most intuitive, but because the small amout of records that i have to process, there is no real performance issue for me.
The program logic that i tried to implement is to basically generate timesheets from jobupdate entries entered by developers. Basically how our company works is like follows.
1. Client submits a request
2. Project manager receives request and allocate Jobs to differnt developers. Each job has an allocated time.
3. Developers will update their job justifying what they did everytime they finish a task for that job, these jobupdates will include the time spent on this task
when ever a job is completed a number of timesheets would be generated from all the jobupdates submitted . for this particular company, they want to generate timesheets on a per job per day basis, what's more, all the jobupdates that exceeds the allocated time will be put into a differnt timesheet. so normally there would only be 1 timesheet per day when all the jobupdates submitted for a particular job are within allocated time. once allocated time is exceeded, there could be a maximum of 3 different timesheets generated for a particular day. Thus the reason that i have to process each entry individual and thus the need for cursor.
Because of this business logic, there is almost no way of avoiding cursor.
Cheers
James
PS: I love you guys, I have learnt a lot from dok and blindman and a number of other people since i joined. dbforums rocks because of you!! :)
Wednesday, March 21, 2012
loop through a datagrid
Hi,
No I don't want to loop through my dataset ;) I want to loop through my
datagrid to retrieve the values in each cells, but I don't know how to
do that ?
Can someone help ?
thxNo idea - you'll probably get a better response in an ASP.NET group:
No I don't want to loop through my dataset ;) I want to loop through my
datagrid to retrieve the values in each cells, but I don't know how to
do that ?
Can someone help ?
thxNo idea - you'll probably get a better response in an ASP.NET group:
microsoft.public.dotnet.framework.aspnet.datagridc ontrol
Simon
Loooping through SELECT values in SQL
Hi,
Terri|||my mistake, the multiple values selected are from the same column (multiple rows)|||That's OK. you can still use the same method:
Environment - VB.NET, ASP.NET, SQL Server 2000.
In a SQL User-Defined Function, I am selecting a row which returns multiple values. I need to construct one single string out of those returned values. To do that, I am using CUROSR.
Now, CURSOR is expensive operation. If there are 1000 users at a time, it will consume lot of resources.
Is there a way, I can construct this String without using CURSORs??
Please advice. Thanks
PankajYou should be able to do something like this to concatenate the columns together:
DECLARE @.myResult VARCHAR(8000)
SET @.myResult = ''
SELECT
@.myResult = @.myResult + myColumn1 + myColumn2 + myColumn3 + myColumn4
FROM
myTable
Terri|||my mistake, the multiple values selected are from the same column (multiple rows)|||That's OK. you can still use the same method:
DECLARE @.myResult VARCHAR(8000)SET @.myResult = ''
SELECT
@.myResult = @.myResult + myColumn1
FROM
myTable
Terri|||Even though the described SQL works this not a supported T-SQL approach. As per the documentation deom BOL on Select statement :
:: If the SELECT statement returns more than one value, the variable is assigned the last value returned.::
And such an approach is dangerous and can be removed in future builds or service packs. Donot rely on them.
Check out a thread posted by Umachander (MVP, SQL Server) on the same at the public.sqlserver.programmming newsgroup. http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e7dcU%23jiAHA.2088%40tkmsftngp03
There are many other approaches to the same problem. Some of the solutions have been outlined at : http://www.extremeexperts.com/sql/articles/IterateTSQLResult.aspxsql
Monday, March 19, 2012
lookup values in another table
alright, I'm sure this is a simple solution, but I really don't know much about T-SQL. I have two tables, [temp], and [SectionChanges]. I am using SQL 2005 SP2. Here are the schemas:
Code Snippet
[dbo].[temp](
[Section] [smallint] NOT NULL,
[Mnemonic] [nvarchar](50) NOT NULL,
[Test Name] [nvarchar](200) NOT NULL,
[Date] [smalldatetime] NOT NULL,
[Patient Type] [smallint] NOT NULL,
[Nurse Unit] [nvarchar](20) NULL,
[Client#] [int] NOT NULL,
[Item_Qty] [smallint] NOT NULL,
[InputDate] [smalldatetime] NOT NULL,
[Bill_Item_ID] [int] NOT NULL,
[Charge_Item_ID] [int] NOT NULL
)
[dbo].[SectionChanges](
[Mnemonic] [nvarchar](50) NOT NULL,
[Test Name] [nvarchar](200) NOT NULL,
[Section] [smallint] NOT NULL
)
The data I want to use is:
Code Snippet
SELECT Mnemonic, [Test Name], Section, Bill_Item_ID
FROM temp
WHERE (Section = 0) OR
(Section >= 18)
I want to match the two tables on Mnemonic AND [Test Name] and then update the temp table with the value of section from SectionChanges
Thank you all.
Try this:
Code Snippet
UPDATE t
SET Section = sc.Section
FROM temp t
INNER JOIN SectionChanges sc
ON t.Mnemonic = sc.Mnemonic
AND t.[Test Name] = sc.[Test Name]
WHERE (Section = 0) OR
(Section >= 18)
|||Thanks much!|||I'm sorry, it worked once, but now it isn't working any more. I now get the error:
Column or expression 'Section' cannot be updated.
This is the exact query I was using when it worked before
Code Snippet
UPDATE t
SET Section = sc.Section
FROM temp t
INNER JOIN SectionChanges sc
ON t.Mnemonic = sc.Mnemonic
AND t.[Test Name] = sc.[Test Name]
WHERE (t.Section = 0) OR
(t.Section >= 17)
Have you changed anything (ie, table definition, etc.) since the first time you ran it?
No changes to the update code?
|||no
|||alright I got it to work again but I'm baffled. The result changes depending on the query window i enter it. In Management studio, if i right click a table and click open table, then open up the query text and replace it with the update query, it gives that error. If i right click the table and goto script table as update and then paste the query, it works fine. It also doesnt work in a SQL task in SSIS.|||I had to change the where clause to add the alias to the Section column name (I had just copied it from your code before)
but this works:
Code Snippet
create table [dbo].[temp](
[Section] [smallint] NOT NULL,
[Mnemonic] [nvarchar](50) NOT NULL,
[Test Name] [nvarchar](200) NOT NULL,
[Date] [smalldatetime] NOT NULL,
[Patient Type] [smallint] NOT NULL,
[Nurse Unit] [nvarchar](20) NULL,
[Client#] [int] NOT NULL,
[Item_Qty] [smallint] NOT NULL,
[InputDate] [smalldatetime] NOT NULL,
[Bill_Item_ID] [int] NOT NULL,
[Charge_Item_ID] [int] NOT NULL
)
create table [dbo].[SectionChanges](
[Mnemonic] [nvarchar](50) NOT NULL,
[Test Name] [nvarchar](200) NOT NULL,
[Section] [smallint] NOT NULL
)
insert into dbo.temp values (0, 'code1', 'test1', getdate(), 0, '1', 1, 1, getdate(), 0, 0)
insert into dbo.temp values (0, 'code2', 'test2', getdate(), 0, '1', 1, 1, getdate(), 0, 0)
insert into dbo.temp values (0, 'code3', 'test3', getdate(), 0, '1', 1, 1, getdate(), 0, 0)
insert into dbo.SectionChanges values('code1', 'test1', 100)
insert into dbo.SectionChanges values('code2', 'test2', 200)
insert into dbo.SectionChanges values('code3', 'test3', 300)
UPDATE t
SET Section = sc.Section
FROM temp t
INNER JOIN SectionChanges sc
ON t.Mnemonic = sc.Mnemonic
AND t.[Test Name] = sc.[Test Name]
WHERE (sc.Section = 0) OR
(sc.Section >= 18)
select *
from temp
|||When I right clicked on [temp] and chose "Open Table" and then replaced the SQL with the update code, it gave me the same error that you received.
I noticed though that SSMS modified the script by inserting "CROSS APPLY t" before the WHERE clause.
Removing this cross apply gets rid of the problem.
(I've never used the SQL window on an OPEN TABLE to do anything like this, I usually use "New Query"...so I have no idea why SSMS decided to change what was pasted in the window.)
As for the SSIS, can you tell me what about it isn't working? Is there an error message? What all is going on in and around the SQL Task causing the problem. Please post anything can about it.
|||Thank you very much for your help, I found out that SSIS was just a dumb mistake on my part. It's really strange that that query window gives different results and different errors. This was 1000 times more efficent than what I was trying to do before in SSIS.
Lookup value query joining two tables
David:
It is not a join as keys are not present in both the tables; between
will just restrict the values for test_val. However, the following
solution has been proposed by Joe Celko, which works. Thanks to
everybody who contibuted. David and Joe, thanks so much.
UPDATE T1
SET factor
= (SELECT T2.factor
FROM T2
WHERE T1.test_val BETWEEN T2.from_val AND T2.to_val)
WHERE EXISTS
(SELECT *
FROM T2
WHERE T1.test_val BETWEEN T2.from_val AND T2.to_val);
--CELKO--> It is not a join as keys are not present in both the tables
Keys are not a requirement for a join. Any boolean expression (including the
BETWEEN predicate) can be used as the basis of a join between two tables.
That's with or without keys or corresponding columns in both tables.
--
David Portas
SQL Server MVP
--
Lookup transformation problem
Hello all,
I needed to lookup some table values based on a join of two fields...
I've configured the lookup transform to get the values via a SQL statement to minimize loading time.
However, when creating the relationships between the input columns and the lookup columns I receive following error:
input column [BATCH_ID] has a datatype which cannot be joined on
I've checked both input and lookup columns, both are of type DT_R8... Both columns in the different tables do have the same datatypes
Any idea how to solve this problem?
Thanks in advance
DT_R8 is a floating point data type, it is an approximate value which means it cannot be joined on. Try a decimal data type instead.|||Hello,
Thanks for your reply but how can I change datatypes fetched directly from a SQL statement in the lookup query?
|||lookup supplied many functions can cast datatype
i think you also can use convert or cast in sql statements
|||Hello,
I've used Dataconversion and SQL functions to perform the requested conversions...
works fine however when executing the package it fails when loading the cache of the lookup components...
this is the message I get from the progress window:
[GET HOLDING_ID [2998]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E07 Description: "ORA-01722: invalid number ".
and
next line:
[GET HOLDING_ID [2998]] Error: OLE DB error occurred while populating internal cache. Check SQLCommand and SqlCommandParam properties.
However, I never changed the SQL command of the transform in the advanced tab...
Any Idea?
Thanks in advance
lookup to check values in 13 columns with same values
hi,
it is my first post on this forum, please be patient if i miss any important bit of information.
i am transporting data from a legacy system into mssql 2k5 using SSIS.
among those column of a dataset there are 13 columns, all necessary for operational reasons, that i need to ensure data consistance.
i believe i could do this check using the lookup data flow item, but surely there must be a way to do it in a more streamlined fashion.
since column names contain numbers to distinguish the version, eg; col01, col02, col03 .. col13.
i thought i could include the lookup within a loop and use a couple of variables to do this trick, but since i have not done it before i am asking for some sort of guidance from a guru among you folks.
please let me know if further clarification is necessary.
regards,
nicolas
If you are trying to confirm that the 13 values for each row in the source match the corresponding row in the destination, then a lookup is the way to go. Using a single lookup with the 13 columns all mapped makes sense. Trying to do something dynamic is not going to work, and even if it did this would mean 13 individual lookup calls, so probably a lot slower due to the extra overhead.
Another method when you have lots of columns to compare is to use a hash or checksum across the columns. You can compute this checksum in the pipeline on the incoming source, and compare that against the checksum stored in the destination. This means the lookup is only one column, so for wide column data and lots of them it will be faster due to the reduced data being transferred, and it is easier to just select columns for checksum generation compared to mapping in the lookup. This does mean that your destination needs to be expaned to store the checksum.
A checksum transform is available here (http://www.sqlis.com/default.aspx?21) or you can implement your own in the Script Component.
|||darren,i understand your point about looking through 13 columns been slow, but i am not certain if i understand how to hash all those columns to compare their values.
for clarification, i need to map or rather insure that those values in all 13 columns will comply with a foreign key constraint, thus i am ensuring that only values listed in my reference table exist in those columns.
would this scenario allow me to use hash for comparing values?
many thanks|||
I may have misunderstood this. If the 13 columns are the key for one table, then the hash concept will work. the hash serves as a compund key for those 13 columns,. If they are 13 different reference tables, then i messed up, you woudl use 13 lookups, one to check each table, probably what you meant first time around. There is no shortcut for these 13 lookups if that is what you require.
I digress a bit, but a good strategy can be to just load the data and let it fail on the FK violation. Set the component to re-direct any failed rows to the error output. You may then need to do your lookups or whatever you want to do when you have missing reference data. This can be much faster than lookups for every row. If 100 rows are loaded, but only 1 is a new key, the error output will get 1 row. This means 1 lookup (fix work) as opposed to 100.
|||Hi Darren,
In the example - you have assumed that the base tables contain the Foreign Key column values - What if we need to lookup on a secondary table, Obtain the primary key from the secondary table and populate them in the base table column.
Is it beneficial to handle these relations in SQL JOIN Statements while fetching records from the source tables rather than doing the lookup using LookUP Components in the SSIS pipeline (which I believe works at record level - meaning a bulk lookup is impossible.)
Please share your comments.
Thanks,
Loonysan
since i have similar situation with 4 other dataset, i would like to probe this idea further as well as maek sure that i clarify it so we understand this situation properly.
this source dataset that i extracted from our legacy data source has these 13 columns (col1..col13) among many.
in on our destination system, i created a table (lookup table) which will enable me to map and transform legacy codes into relevant new codes in our destination table.
the dataset is intented to be inserted into a table that has 13 foreign keys (one for each of those 13 columns) which references another table, thus ensuring that only new codes will be inserted in any of those 13 columns.
having said that, you believe that i should join the lookup table, try to insert into the destination table & catch any error?
many thanks
Lookup Tables And Joins
In a table I'm storing as integers recCreatedBy and recModifiedBy values; I have a lookup table I'm doing an INNER JOIN with to pull a string value for an Alias 'CreatedBy'. I'm trying to Alias a 'ModifiedBy' from the same lookup table in the query. Any help would be greatly appreciated.
SELECT tblStaffPhysicians.docIndex, tblStaffPhysicians.docFullName, tblUsers.userLogin AS CreatedBy
FROM tblStaffPhysicians INNER JOIN
tblUsers ON tblStaffPhysicians.recCreatedBy = tblUsers.userIndexIf I understand you correctly, the SQL would look like this:
SELECT tblStaffPhysicians.docIndex, tblStaffPhysicians.docFullName, tblUsers.userLogin AS CreatedBy
FROM tblStaffPhysicians INNER JOIN
tblUsers ON tblStaffPhysicians.recCreatedBy = tblUsers.userIndexIf I understand you correctly, the SQL would look like this:
SELECT tblStaffPhysicians.docIndex, tblStaffPhysicians.docFullName, Created.userLogin AS CreatedBy, Modified.userLogin AS ModifiedBy
FROM tblStaffPhysicians INNER JOIN
tblUsers Created ON tblStaffPhysicians.recCreatedBy = Created.userIndex
LEFT OUTER JOIN tblUsers Modified ON tblStaffPhysicians.recModifiedBy = Modified.userIndex
I am assuming recModifiedBy could be NULL. If that is incorrect, you could just do an inner join.
Hope that helps!!
BobP|||Thank you so much for your help; it looks like your suggestion is the solution. Thanks again.
Lookup tables - one or many
So i am designing a new database that currently has several tables 'look up tables' that are used just to limit the values of columns in other tables.
my question is what is the best way to do this?
1. multiple tables - one for each set of values (ex: JobType, Position, PayGrade)
2. One large table that holds all the lookup values - has a 'Category' field to group them
3. put constraints on the columns of the tables that are 'looking up' and get rid of the lookup tables.
thanks
lucas
Lucas, well you want lookup values in a table of SOME sort, so option #3 would be out for me. Much better design to have dynamic data changes, then hard-coding constraints.
Now the real choice... probably get half of the people using one way and the other half, the other... A lot of this depends on thigns like... what is going in your lookup tables. If it's a case where you have a relatively small # of values, and they are basically all the same (an ID, Code, Name Desc), then I prefer 2 tables, the Lookup and the LookupCategory tables. Doing it that way, the main issue is that you want a constraint back to the Lookup table, so you have to either have a 2 column PK there that you FK back to, or could use triggers to maintain the "FK" constraint, ehhh.. or just key back to the single PK column and have checks in place that they are FK'ing back to one of the values for the specific Category....
But if it's a small amount of Lookup tables, or they have more attributes then ID,Code,Name,Desc... then separate tables per Lookup works ok. If you start having 50, 70, 100+ of these 5 row type tables, that gets annoying.... but a lot of people can't deal with the object-oriented stlye, so it all depends on the developers and whoever will touch the tables, to see which of those 2 options to go with.... You'll probably get more people want to use one table per lookup, than the generic approach.... Bruce
|||Right now i have about 20 small lookup tables (just one field) with around 10 records in each.i'm the developer as well and i am just planing on using these lookup tables to populate drop down lists in the application.
i'm starting to think i should have one large lookup table with a category table; like this:
LookUp
LookUpId (pk)
Category (fk)
Value
LookUpCategory
Category (pk)
look good?
|||
In that 2 table method, I'd have the columns for ID, Code, Name and Desc.... not just the value, at least some description field, if not NAME and DESC fields. On the Category, same thing, an ID, Code, Name, Desc.... If you just have a Category, unless it's a clear varchar code, then you won't know what it is exactly, especailly if teh model grows to 30,40,50 lookups... and, since you're FKing back to this table, might be better to call it tTypeCode and tTypeCategory... then your base tables would have fields called like propertyTypeID ( as opposed to propertyLookupID)... just clearer to call them type then lookups...
Other columns you MAY want to have in the type table are a Seq# and a defaultFlag....
FKing back to the Type table, is ok like that, but you could have a phoneTypeID value be stored that is really an ID for a propertyTypeID, unless you put some check in place to enforce the selection of type ID's by category...
Bruce
|||If you just want to validate that a column
only contains a defined set of values don't need to do things like populate drop downs the set of valid values do not changeMonday, March 12, 2012
Lookup table, extracting values
I need some help with the following...
My data source has some columns I have to 'translate' first and then insert into my destination table.
Example Source data:
key size height
1 'Small' 'Tall'
2 'Big' 'Short'
has to become
1 'Y' 'P'
2 'N' 'D'
I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest
Example:
col_name vl_source vl_dest
size 'Small' 'Y'
size 'Big' 'N'
height 'Tall' 'P'
... and so on, I believe you get the point
How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?You either need to do it in your source when you do the query, or do n lookups or embed the information in a derived column component.
Unfortunately there is no Multi lookup component.
|||
Then in each lookup, you'll filter your SQL query against "col_name". So for exampe, "select vl_source, vl_dest from conversion_table where col_name = 'size'")
|||
You can unpivot your data as well and run it through one lookup.
So your data:
key size height
1 'Small' 'Tall'
2 'Big' 'Short'
Would become:
key column value
1 'size' 'Small'
1 'height' 'Tall'
2 'size' 'Big'
2 'height' 'Short'
This would allow you to use one lookup table. Then you'd need to pivot your data back, using the returned values from the lookup.
Takes more time to setup, but it could be worth it for you.
My data source has some columns I have to 'translate' first and then insert into my destination table.
Example Source data:
key size height
1 'Small' 'Tall'
2 'Big' 'Short'
has to become
1 'Y' 'P'
2 'N' 'D'
I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest
Example:
col_name vl_source vl_dest
size 'Small' 'Y'
size 'Big' 'N'
height 'Tall' 'P'
... and so on, I believe you get the point
How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?You either need to do it in your source when you do the query, or do n lookups or embed the information in a derived column component.
Unfortunately there is no Multi lookup component.
|||
what do you exactly mean by 'embed the information in a derived column component'? Could you elaborate?
Thank you
He's saying that you could either do it in source extract query like this
Code Blockselect
....
,
case
when col1='small' then 's'
when col1='medium' then 'm'
end
from
....
or embeded in the derived column component like this
Code Blockcol1==""small"" ? ""s"" : col1 || col1==""medium"" ? ""m"" : col1
Then in each lookup, you'll filter your SQL query against "col_name". So for exampe, "select vl_source, vl_dest from conversion_table where col_name = 'size'")
|||
Thank you, Phil, that's exactly what I've done.
It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...
Sara4 wrote:
Thank you, Phil, that's exactly what I've done.
It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...
Per the example above, you can do CASE logic inside the derived column, but then it'll be harder to maintain that your table approach.
Anthony Martin wrote:
or embeded in the derived column component like this
Code Blockcol1==""small"" ? ""s"" : col1 || col1==""medium"" ? ""m"" : col1
Thank you for the explanation. We need to keep our decodifying dynamic (in the future small may become smallx and some new values could be entered - storing the values in a derived column would mean modifying the SSIS too).
It seems that for now the only (quick) solution is creating as many lookups as there are columns that need to be decodified.
Thank you all!
You can unpivot your data as well and run it through one lookup.
So your data:
key size height
1 'Small' 'Tall'
2 'Big' 'Short'
Would become:
key column value
1 'size' 'Small'
1 'height' 'Tall'
2 'size' 'Big'
2 'height' 'Short'
This would allow you to use one lookup table. Then you'd need to pivot your data back, using the returned values from the lookup.
Takes more time to setup, but it could be worth it for you.
Lookup table, extracting values
I need some help with the following...
My data source has some columns I have to 'translate' first and then insert into my destination table.
Example Source data:
key size height
1 'Small' 'Tall'
2 'Big' 'Short'
has to become
1 'Y' 'P'
2 'N' 'D'
I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest
Example:
col_name vl_source vl_dest
size 'Small' 'Y'
size 'Big' 'N'
height 'Tall' 'P'
... and so on, I believe you get the point
How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?
You either need to do it in your source when you do the query, or do n lookups or embed the information in a derived column component.
Unfortunately there is no Multi lookup component.
|||what do you exactly mean by 'embed the information in a derived column component'? Could you elaborate?
Thank you
|||He's saying that you could either do it in source extract query like this
Code Block
select
....
,
case
when col1='small' then 's'
when col1='medium' then 'm'
end
from
....
or embeded in the derived column component like this
Code Block
col1=="small" ? "s" : col1 || col1=="medium" ? "m" : col1
|||You can use multiple lookup transformations in a row to get what you need. Create that physical table and then it'll work. Add a lookup for each column you want to translate.Then in each lookup, you'll filter your SQL query against "col_name". So for exampe, "select vl_source, vl_dest from conversion_table where col_name = 'size'")|||
Thank you, Phil, that's exactly what I've done.
It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...
|||
Sara4 wrote:
Thank you, Phil, that's exactly what I've done.
It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...
Per the example above, you can do CASE logic inside the derived column, but then it'll be harder to maintain that your table approach.|||
or embeded in the derived column component like this
Code Block
col1=="small" ? "s" : col1 || col1=="medium" ? "m" : col1
Thank you for the explanation. We need to keep our decodifying dynamic (in the future small may become smallx and some new values could be entered - storing the values in a derived column would mean modifying the SSIS too).
It seems that for now the only (quick) solution is creating as many lookups as there are columns that need to be decodified.
Thank you all!
|||Sara,You can unpivot your data as well and run it through one lookup.
So your data:
key size height
1 'Small' 'Tall'
2 'Big' 'Short'
Would become:
key column value
1 'size' 'Small'
1 'height' 'Tall'
2 'size' 'Big'
2 'height' 'Short'
Takes more time to setup, but it could be worth it for you.|||
I'll try it out and let you know how it went!
Lookup table, extracting values
I need some help with the following...
My data source has some columns I have to 'translate' first and then insert into my destination table.
Example Source data:
key size height
1 'Small' 'Tall'
2 'Big' 'Short'
has to become
1 'Y' 'P'
2 'N' 'D'
I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest
Example:
col_name vl_source vl_dest
size 'Small' 'Y'
size 'Big' 'N'
height 'Tall' 'P'
... and so on, I believe you get the point
How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?
You either need to do it in your source when you do the query, or do n lookups or embed the information in a derived column component.
Unfortunately there is no Multi lookup component.
|||what do you exactly mean by 'embed the information in a derived column component'? Could you elaborate?
Thank you
|||He's saying that you could either do it in source extract query like this
Code Block
select
....
,
case
when col1='small' then 's'
when col1='medium' then 'm'
end
from
....
or embeded in the derived column component like this
Code Block
col1=="small" ? "s" : col1 || col1=="medium" ? "m" : col1
|||You can use multiple lookup transformations in a row to get what you need. Create that physical table and then it'll work. Add a lookup for each column you want to translate.Then in each lookup, you'll filter your SQL query against "col_name". So for exampe, "select vl_source, vl_dest from conversion_table where col_name = 'size'")|||
Thank you, Phil, that's exactly what I've done.
It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...
|||
Thank you, Phil, that's exactly what I've done.
It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...
Per the example above, you can do CASE logic inside the derived column, but then it'll be harder to maintain that your table approach.|||
or embeded in the derived column component like this
Code Block
col1=="small" ? "s" : col1 || col1=="medium" ? "m" : col1
Thank you for the explanation. We need to keep our decodifying dynamic (in the future small may become smallx and some new values could be entered - storing the values in a derived column would mean modifying the SSIS too).
It seems that for now the only (quick) solution is creating as many lookups as there are columns that need to be decodified.
Thank you all!
|||Sara,You can unpivot your data as well and run it through one lookup.
So your data:
key size height
1 'Small' 'Tall'
2 'Big' 'Short'
Would become:
key column value
1 'size' 'Small'
1 'height' 'Tall'
2 'size' 'Big'
2 'height' 'Short'
Takes more time to setup, but it could be worth it for you.|||
I'll try it out and let you know how it went!
Lookup on a range
I want to check to see if my document number (let's say, 11) is in between two values: from a table in my database, I have a min value and a max value; how can I check to see if the number is between the min and the max?
Thanks!
Jim Work
In the control flow, you may want to issue an execute sql task with the query being: select min(value) as minvalue, max(value) as maxvalue from table.
Then map the results to two variables.
Then, in the data flow, you can compare input to the two variables using a conditional split, derived column, etc...
Lookup including looking up on null values possible?
In order to insert datekey values in I lookup datekey in the datedimension table. I join on the 'Date' column that contains dates. The datedimension contains one record for 'unknown date' for which the value of the 'Date' column is null.
The behavior that I desire from my lookup transformation is that for input records with a date the corresponding datekey from the datedimension is looked up and for records with date = null, the datekey for 'unknown date' is looked up.
The first part works well but the looking up on null fails, apparently because you can not say null == null. Does anyone know whether there is a setting in the lookup transformation to include null lookups?
Thnx,
HenkThe lookup transform can not do this. You would need to put a derived column in the flow and if the value is NULL then set it to the appropriate 'unknown date' value.
Thanks,|||Thanks Matt.|||In fact it can and it is quite easy! I found out in the documentation:
"A Lookup transformation that has been configured to use partial or no caching will fail if a lookup operation matches columns that contain null values, unless you manually update the SQL statement to include an OR ISNULL(ColumnName) condition. If full precaching is used, the lookup operation succeeds."
|||So by selecting the full precaching option for the lookup, you eliminate the need to modify the SQL with the ISNULL function?|||While this can work as described I would recommend against it and is, therefore, why I didn't mention it. You need to be careful if you do lookups in this way because unless you guarrantee that there is only one such value you will get the first one lookup happens to find with no warning.Full precaching will not work because the cache is fully charged and doesn't issue the SQL statement again. The reason why partial or no cache works is because the SQL statement is issued if a match isn't found and will return success due to the ISNULL statement as long as there is a NULL in the table.
There are too many ifs and caveats to make this a good solution, IMHO.
Thanks,
Subscribe to:
Posts (Atom)