Function to seperate comma delimited db field into new table

Hi All,
I hope someone can help, i have taken on a project from someone and
have been given their db to work from, one thing they have done which i
know is bad design is seperate a lit of locations into one field with
commas, i.e 1,5,8,9. I now want to create a proper relationship by
adding a new relation table..
So instead of :
JobID LocationID
1 1,2,3
I will have in a new table:
JobID LocationID
1 1
1 2
1 3
Is there a function i can use or create to iterate through these old
values and insert them into my new table, the table is already to big
to do it manually, i only need to run it once so performance isnt an
issue..
Could someone point me in the right direction of where to search or a
start as i although i know a bit about sql this is above my head at the
mo..
Cheers
Anthonyhttp://www.aspfaq.com/2248
<anthonykallay@.hotmail.com> wrote in message
news:1133789661.982222.291240@.o13g2000cwo.googlegroups.com...
> Hi All,
> I hope someone can help, i have taken on a project from someone and
> have been given their db to work from, one thing they have done which i
> know is bad design is seperate a lit of locations into one field with
> commas, i.e 1,5,8,9. I now want to create a proper relationship by
> adding a new relation table..
> So instead of :
> JobID LocationID
> 1 1,2,3
> I will have in a new table:
> JobID LocationID
> 1 1
> 1 2
> 1 3
> Is there a function i can use or create to iterate through these old
> values and insert them into my new table, the table is already to big
> to do it manually, i only need to run it once so performance isnt an
> issue..
> Could someone point me in the right direction of where to search or a
> start as i although i know a bit about sql this is above my head at the
> mo..
> Cheers
> Anthony
>|||My personal favourite (by Dejan Sarka):
http://solidqualitylearning.com/blo.../10/22/200.aspx
ML
http://milambda.blogspot.com/|||Another possible option...
http://searchsqlserver.techtarget.c...1074960,00.html
"anthonykallay@.hotmail.com" wrote:

> Hi All,
> I hope someone can help, i have taken on a project from someone and
> have been given their db to work from, one thing they have done which i
> know is bad design is seperate a lit of locations into one field with
> commas, i.e 1,5,8,9. I now want to create a proper relationship by
> adding a new relation table..
> So instead of :
> JobID LocationID
> 1 1,2,3
> I will have in a new table:
> JobID LocationID
> 1 1
> 1 2
> 1 3
> Is there a function i can use or create to iterate through these old
> values and insert them into my new table, the table is already to big
> to do it manually, i only need to run it once so performance isnt an
> issue..
> Could someone point me in the right direction of where to search or a
> start as i although i know a bit about sql this is above my head at the
> mo..
> Cheers
> Anthony
>

function to return multi-selected values?

Is there a function to return the multi-selected parm values in a comma
delimited string? Using RS2005. dataset1 is the source of a parameter I'll
call STORE. I have this defined as a multi-select parm. Getting error
(cannot add multi value query parameter 'STORE' for dataset 'dataset2'
because it is not supported by the data extension) on trying to select parms
that are needed for my next dataset query unless I map the parameter for
dataset2 to an expression like this STORE = Parameters!STORE.Value(0) + "," +
Parameters!STORE.Value(1)
I cant do that in reality though - I was just seeing if it would work. I
need a function that I can map the parameter to which will feed to dataset2
all the STORE values chosen (similar to above). Why does it say that it is
not supported when I can type in comma delimited values in that parameter
field and run the report? Thanks in advanceIf I understand you have this...
Parameter 1, STORE, multi-select. Source is from query (dataset1) -
something like SELECT STORE_NAME FROM STORE.
dataset2 feeds the data in your report. It has to have a where clause
based on the values selected from the STORE parameter.
You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
are using a supported database: SQL Server or... i think... Oracle).
If you are using another dbms (we use sybase for some of our reports,
and we get that error) you have to be creative...
In that case, for dataset2, I have done the following.
="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
Join(Parameters!STORE.Value, "','") & "'"
There are single quotes in there to put them around each value in STORE
(assuming it is a string). If they are integers, you can lose the
single quotes.
Good luck,
Regards,
Dan|||Thanks Dan,
Actually I am using db2 version 8.1 (database lives on a unix box). You
understood correctly that I have dataset1 which provides the selections for
the parm STORE. The parm is then mapped to a query parm that is needed for
dataset2 which is a stored procedure (no sql to manipulate there). Any ideas
there? Again ... thanks
"Dan" wrote:
> If I understand you have this...
> Parameter 1, STORE, multi-select. Source is from query (dataset1) -
> something like SELECT STORE_NAME FROM STORE.
> dataset2 feeds the data in your report. It has to have a where clause
> based on the values selected from the STORE parameter.
> You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
> are using a supported database: SQL Server or... i think... Oracle).
> If you are using another dbms (we use sybase for some of our reports,
> and we get that error) you have to be creative...
> In that case, for dataset2, I have done the following.
> ="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
> Join(Parameters!STORE.Value, "','") & "'"
> There are single quotes in there to put them around each value in STORE
> (assuming it is a string). If they are integers, you can lose the
> single quotes.
> Good luck,
> Regards,
> Dan
>|||When you pass a multi-select parameter to a stored procedure you are sending
a comma separated string of values. Why your stored procedure is having
trouble with it I don't know.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:DED795B5-D458-481F-9AC5-8CF2B1D07B05@.microsoft.com...
> Thanks Dan,
> Actually I am using db2 version 8.1 (database lives on a unix box). You
> understood correctly that I have dataset1 which provides the selections
> for
> the parm STORE. The parm is then mapped to a query parm that is needed
> for
> dataset2 which is a stored procedure (no sql to manipulate there). Any
> ideas
> there? Again ... thanks
> "Dan" wrote:
>> If I understand you have this...
>> Parameter 1, STORE, multi-select. Source is from query (dataset1) -
>> something like SELECT STORE_NAME FROM STORE.
>> dataset2 feeds the data in your report. It has to have a where clause
>> based on the values selected from the STORE parameter.
>> You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
>> are using a supported database: SQL Server or... i think... Oracle).
>> If you are using another dbms (we use sybase for some of our reports,
>> and we get that error) you have to be creative...
>> In that case, for dataset2, I have done the following.
>> ="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
>> Join(Parameters!STORE.Value, "','") & "'"
>> There are single quotes in there to put them around each value in STORE
>> (assuming it is a string). If they are integers, you can lose the
>> single quotes.
>> Good luck,
>> Regards,
>> Dan
>>|||I dont know either. If I take the multi-value off of that parm and type in 2
comma separated values then it works just fine. Something about the
multi-value it doesnt like or I am setting up wrong. This is db2 v8.1 ...
the stored proc works to accept comma-separated values when typed in ... just
not when selected as multi-value ... I cant see what I would be doing wrong?
"Bruce L-C [MVP]" wrote:
> When you pass a multi-select parameter to a stored procedure you are sending
> a comma separated string of values. Why your stored procedure is having
> trouble with it I don't know.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:DED795B5-D458-481F-9AC5-8CF2B1D07B05@.microsoft.com...
> > Thanks Dan,
> > Actually I am using db2 version 8.1 (database lives on a unix box). You
> > understood correctly that I have dataset1 which provides the selections
> > for
> > the parm STORE. The parm is then mapped to a query parm that is needed
> > for
> > dataset2 which is a stored procedure (no sql to manipulate there). Any
> > ideas
> > there? Again ... thanks
> >
> > "Dan" wrote:
> >
> >> If I understand you have this...
> >>
> >> Parameter 1, STORE, multi-select. Source is from query (dataset1) -
> >> something like SELECT STORE_NAME FROM STORE.
> >>
> >> dataset2 feeds the data in your report. It has to have a where clause
> >> based on the values selected from the STORE parameter.
> >>
> >> You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
> >> are using a supported database: SQL Server or... i think... Oracle).
> >>
> >> If you are using another dbms (we use sybase for some of our reports,
> >> and we get that error) you have to be creative...
> >>
> >> In that case, for dataset2, I have done the following.
> >>
> >> ="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
> >> Join(Parameters!STORE.Value, "','") & "'"
> >>
> >> There are single quotes in there to put them around each value in STORE
> >> (assuming it is a string). If they are integers, you can lose the
> >> single quotes.
> >>
> >> Good luck,
> >>
> >> Regards,
> >>
> >> Dan
> >>
> >>
>
>|||Bruce may be better versed in some of this than I am, but from what I
understand, a comma separated list is only sent through to the dbms
through a data extension that supports multi-value parameters. I don't
believe db2/odbc does.
What does the text from your dataset 2 look like?
If you changed it to something like the following, it should work.
="exec storedprocname '" & Join(Parameters!STORE.Value, "','") & "'"