Help on using CASE together with UPDATE

Hi
I need some help on how to update some fields with a value based on the
value in another field. I have tried to do this with a CASE statement, but I
haven't really been able to get anywhere near something that works...
If I run the select statement -
"select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
a.zipcode, z.cityname, a.title from user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
where u.userinit='spe' ",
then it gives the records I want to update. What I then want to do, is to
update the field u.userdepartment with a value based on the value of the
field "title". Eg. when the field title has the value "IT" then I'd like to
set userdepartment = 2959028.
I'd think that I can use something like ...userdepartment = CASE title =
'IT' then 2959028... but apparently I need some guidedance on how to do
this.
Can any of you help with this?
Regards
Steen
untested code follows:
Does this select statement return what you are looking for?
SELECT user, title,
userdepartment = CASE WHEN title = 'IT' THEN 2959028
WHEN 'MARKETING' THEN 1
WHEN '...' THEN 2
ELSE NULL END
FROM user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
WHERE u.userinit='spe'
If so, this might be the update statement that you are looking for:
UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
WHEN 'MARKETING' THEN 1
WHEN '...' THEN 2
ELSE NULL END
FROM user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
WHERE u.userinit='spe'
Keith
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need some help on how to update some fields with a value based on the
> value in another field. I have tried to do this with a CASE statement, but
I
> haven't really been able to get anywhere near something that works...
> If I run the select statement -
> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> a.zipcode, z.cityname, a.title from user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> where u.userinit='spe' ",
> then it gives the records I want to update. What I then want to do, is to
> update the field u.userdepartment with a value based on the value of the
> field "title". Eg. when the field title has the value "IT" then I'd like
to
> set userdepartment = 2959028.
> I'd think that I can use something like ...userdepartment = CASE title =
> 'IT' then 2959028... but apparently I need some guidedance on how to do
> this.
> Can any of you help with this?
> Regards
> Steen
>
|||Try Something on these lines:
UPDATE user
SET userdepartment =
CASE title
WHEN 'IT' THEN 2959028
WHEN 'HR' THEN 2959029
ELSE NULL
END
From address a
INNER JOIN Zipcode z ON a.zipcode=z.zipcode
where u.userinit='spe' and u.addressidentold = a.addressident
-- Note: code not tested ...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need some help on how to update some fields with a value based on the
> value in another field. I have tried to do this with a CASE statement, but
I
> haven't really been able to get anywhere near something that works...
> If I run the select statement -
> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> a.zipcode, z.cityname, a.title from user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> where u.userinit='spe' ",
> then it gives the records I want to update. What I then want to do, is to
> update the field u.userdepartment with a value based on the value of the
> field "title". Eg. when the field title has the value "IT" then I'd like
to
> set userdepartment = 2959028.
> I'd think that I can use something like ...userdepartment = CASE title =
> 'IT' then 2959028... but apparently I need some guidedance on how to do
> this.
> Can any of you help with this?
> Regards
> Steen
>
|||Hi
Thanks to both of you - by "combining" your examples I got it working.
Keith - the second line of your example should be
....userdepartment = CASE title When 'IT' then 29... then it works...
It's always a joy to use this newsgroup - no matter what stupid and simple
question being asked, there're always a lot of helpfull answers to us less
"sql-skilled" people......
Thanks
Steen
..
Keith Kratochvil wrote:[vbcol=seagreen]
> untested code follows:
> Does this select statement return what you are looking for?
> SELECT user, title,
> userdepartment = CASE WHEN title = 'IT' THEN 2959028
> WHEN 'MARKETING' THEN 1
> WHEN '...' THEN 2
> ELSE NULL END
> FROM user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> WHERE u.userinit='spe'
> If so, this might be the update statement that you are looking for:
> UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
> WHEN 'MARKETING' THEN 1
> WHEN '...' THEN 2
> ELSE NULL END
> FROM user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> WHERE u.userinit='spe'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
|||There are two ways to do CASE. This is the other method. My revised
example should work correctly:
CASE WHEN title = 'IT' THEN 2959028
WHEN title = 'MARKETING' THEN 1
WHEN title = '...' THEN 2
ELSE NULL END
Keith
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Of9LDvGrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks to both of you - by "combining" your examples I got it working.
> Keith - the second line of your example should be
> ...userdepartment = CASE title When 'IT' then 29... then it works...
> It's always a joy to use this newsgroup - no matter what stupid and simple
> question being asked, there're always a lot of helpfull answers to us less
> "sql-skilled" people......
> Thanks
> Steen
> .
> Keith Kratochvil wrote:
>

Help on joining tables

Hi
I need some advises on how to join several tables and/or use "nested" joins
(if such a thing exist...)
I trying to select some data which involves 5 tables. The relation is as
follows -
Table1 should be joined with table2, table3 and table 4. Then Table 3 should
be joined with Table 5.
I've tried just using a standard "select ... from table1 JOIN Table2...
JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where" clauses,
but that doesn't really work.
I think that the JOIN I'm doing with table 1 and 5 isn't correct, but how do
I tell that I wan't to join table 3 with table5.
I'd be happy if some of you can get me guided in the right direction on how
to do this, since I can't get my head around it.
In BOL it's mentioned that join expressions can be connected with AND or
with OR, but I can't find any examples on how to do it (...and also I'm not
sure if that will solve my problem...)
Regards
SteenSteen
SELECT col.... FROM Table1 INNER JOIN
Table2 ON Table1.key=Table2.key INNER JOIN
Table3 ON Table1.key=Table1.key INNER JOIN
Table4 ON Table1.key=Table1.key INNER JON
Table5 ON Table3.key=Table5.key
WHERE ..................
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
> Hi
> I need some advises on how to join several tables and/or use "nested"
joins
> (if such a thing exist...)
> I trying to select some data which involves 5 tables. The relation is as
> follows -
> Table1 should be joined with table2, table3 and table 4. Then Table 3
should
> be joined with Table 5.
> I've tried just using a standard "select ... from table1 JOIN Table2...
> JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where" clauses,
> but that doesn't really work.
> I think that the JOIN I'm doing with table 1 and 5 isn't correct, but how
do
> I tell that I wan't to join table 3 with table5.
> I'd be happy if some of you can get me guided in the right direction on
how
> to do this, since I can't get my head around it.
> In BOL it's mentioned that join expressions can be connected with AND or
> with OR, but I can't find any examples on how to do it (...and also I'm
not
> sure if that will solve my problem...)
> Regards
> Steen
>|||Sorry,should be
SELECT col.... FROM Table1 INNER JOIN
Table2 ON Table1.key=Table2.key INNER JOIN
Table3 ON Table1.key=Table3.key INNER JOIN
Table4 ON Table1.key=Table4.key INNER JON
Table5 ON Table3.key=Table5.key
WHERE ..................
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OK5EIEueEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Steen
> SELECT col.... FROM Table1 INNER JOIN
> Table2 ON Table1.key=Table2.key INNER JOIN
> Table3 ON Table1.key=Table1.key INNER JOIN
> Table4 ON Table1.key=Table1.key INNER JON
> Table5 ON Table3.key=Table5.key
> WHERE ..................
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
> joins
> should
Table2...[vbcol=seagreen]
clauses,[vbcol=seagreen]
how[vbcol=seagreen]
> do
> how
> not
>|||Thanks Uri...I'll try to work on that
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Sorry,should be
> SELECT col.... FROM Table1 INNER JOIN
> Table2 ON Table1.key=Table2.key INNER JOIN
> Table3 ON Table1.key=Table3.key INNER JOIN
> Table4 ON Table1.key=Table4.key INNER JON
> Table5 ON Table3.key=Table5.key
> WHERE ..................
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OK5EIEueEHA.2544@.TK2MSFTNGP10.phx.gbl...

Help on joining tables

Hi
I need some advises on how to join several tables and/or use "nested" joins
(if such a thing exist...)
I trying to select some data which involves 5 tables. The relation is as
follows -
Table1 should be joined with table2, table3 and table 4. Then Table 3 should
be joined with Table 5.
I've tried just using a standard "select ... from table1 JOIN Table2...
JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where" clauses,
but that doesn't really work.
I think that the JOIN I'm doing with table 1 and 5 isn't correct, but how do
I tell that I wan't to join table 3 with table5.
I'd be happy if some of you can get me guided in the right direction on how
to do this, since I can't get my head around it.
In BOL it's mentioned that join expressions can be connected with AND or
with OR, but I can't find any examples on how to do it (...and also I'm not
sure if that will solve my problem...)
Regards
Steen
Steen
SELECT col.... FROM Table1 INNER JOIN
Table2 ON Table1.key=Table2.key INNER JOIN
Table3 ON Table1.key=Table1.key INNER JOIN
Table4 ON Table1.key=Table1.key INNER JON
Table5 ON Table3.key=Table5.key
WHERE ..................
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
> Hi
> I need some advises on how to join several tables and/or use "nested"
joins
> (if such a thing exist...)
> I trying to select some data which involves 5 tables. The relation is as
> follows -
> Table1 should be joined with table2, table3 and table 4. Then Table 3
should
> be joined with Table 5.
> I've tried just using a standard "select ... from table1 JOIN Table2...
> JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where" clauses,
> but that doesn't really work.
> I think that the JOIN I'm doing with table 1 and 5 isn't correct, but how
do
> I tell that I wan't to join table 3 with table5.
> I'd be happy if some of you can get me guided in the right direction on
how
> to do this, since I can't get my head around it.
> In BOL it's mentioned that join expressions can be connected with AND or
> with OR, but I can't find any examples on how to do it (...and also I'm
not
> sure if that will solve my problem...)
> Regards
> Steen
>
|||Sorry,should be
SELECT col.... FROM Table1 INNER JOIN
Table2 ON Table1.key=Table2.key INNER JOIN
Table3 ON Table1.key=Table3.key INNER JOIN
Table4 ON Table1.key=Table4.key INNER JON
Table5 ON Table3.key=Table5.key
WHERE ..................
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OK5EIEueEHA.2544@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Steen
> SELECT col.... FROM Table1 INNER JOIN
> Table2 ON Table1.key=Table2.key INNER JOIN
> Table3 ON Table1.key=Table1.key INNER JOIN
> Table4 ON Table1.key=Table1.key INNER JON
> Table5 ON Table3.key=Table5.key
> WHERE ..................
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
> joins
> should
Table2...[vbcol=seagreen]
clauses,[vbcol=seagreen]
how
> do
> how
> not
>
|||Thanks Uri...I'll try to work on that
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Sorry,should be
> SELECT col.... FROM Table1 INNER JOIN
> Table2 ON Table1.key=Table2.key INNER JOIN
> Table3 ON Table1.key=Table3.key INNER JOIN
> Table4 ON Table1.key=Table4.key INNER JON
> Table5 ON Table3.key=Table5.key
> WHERE ..................
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OK5EIEueEHA.2544@.TK2MSFTNGP10.phx.gbl...

Help on "splitting up" data in a field

Hi
I'm having a problem finding out how I can split data in one field and
then use the values to match records in another table.
In table 1, I have a field where the values looks like
e.g. "229 231 233 235". What I'd like to do, is to match these 4
numbers with an ID in table 2 to get the values from table2. I.e. I'd
like to split up this one value to 4 values (229, 232, 233,235).
I've tried to use REPLACE to put in a "," between each so I could use it
as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
Has any of you any other suggestions to how it can be done? It's not
always the same number of numbers in the field (e.g. another one could
be "456 29580010" ). The field is a VARCHAR(1000) and there're also
some text strings in it. These seems to be some old crab though and I
don't need these values.
The only "general" thing with the formatting, seems to be that there are
2 spaces between each of the numbers I'd like to get out, so I think I
can use that as a "delimiter".
Anyone who has some hints to this?
Regards
SteenSteen
Take a look at Anith's script
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5,33,229,1,22'
SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
from numbers where substring(','+@.Ids,n,1)=','
AND n < LEN(@.Ids) + 1
drop table Numbers
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm having a problem finding out how I can split data in one field and
> then use the values to match records in another table.
> In table 1, I have a field where the values looks like
> e.g. "229 231 233 235". What I'd like to do, is to match these 4
> numbers with an ID in table 2 to get the values from table2. I.e. I'd like
> to split up this one value to 4 values (229, 232, 233,235).
> I've tried to use REPLACE to put in a "," between each so I could use it
> as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
> Has any of you any other suggestions to how it can be done? It's not
> always the same number of numbers in the field (e.g. another one could be
> "456 29580010" ). The field is a VARCHAR(1000) and there're also some
> text strings in it. These seems to be some old crab though and I don't
> need these values.
> The only "general" thing with the formatting, seems to be that there are 2
> spaces between each of the numbers I'd like to get out, so I think I can
> use that as a "delimiter".
> Anyone who has some hints to this?
> Regards
> Steen
>|||Uri Dimant wrote:
> Steen
> Take a look at Anith's script
> SELECT IDENTITY(INT) "n" INTO Numbers
> FROM sysobjects s1
> CROSS JOIN sysobjects s2
> GO
> DECLARE @.Ids VARCHAR(200)
> SET @.Ids = '5,33,229,1,22'
> SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
> from numbers where substring(','+@.Ids,n,1)=','
> AND n < LEN(@.Ids) + 1
> drop table Numbers
>
>
>
>
>
>
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
>
Thanks Uri
I must admit, that I can't really see the purpose of the script, and
also I can't see how it can be used to solve my problem.
I've tried to see if I could get some ideas from the script, but I can't
really see how I can use it?
Regards
Steen|||In SQL 2000 you have to properly normalize the data - i.e. parse the values
and store them in a new table or redesign the table.
In SQL 2005 you can use Anith's function to parse the values on-the-fly
using CROSS APPLY.
ML
http://milambda.blogspot.com/|||I'm really sorry Steen , by posting Anith's example I did mean to give you
an idea to solve the problem
See if this helps you
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
CREATE TABLE #Source (col1 INT NOT NULL)
CREATE TABLE #Target (col1 INT NOT NULL)
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5 33 229 1 22'
--Inserting the values to the source table
INSERT INTO #Source
SELECT SUBSTRING(@.Ids, n, CHARINDEX(' ', @.Ids + ' ', n) - n)
from numbers where substring(' '+@.Ids,n,1)=' '
AND n < LEN(@.Ids) + 1
SELECT * FROM #Source
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5 33 10 1 22'
--Inserting the values to the Target table
INSERT INTO #Target
SELECT SUBSTRING(@.Ids, n, CHARINDEX(' ', @.Ids + ' ', n) - n)
from numbers where substring(' '+@.Ids,n,1)=' '
AND n < LEN(@.Ids) + 1
SELECT * FROM #Target
-->>> e.g. "229 231 233 235". What I'd like to do, is to match these 4
SELECT * FROM #Source WHERE NOT EXISTS
(SELECT * FROM #Target WHERE #Source.col1=#Target.col1)
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:%233vpwsPHGHA.516@.TK2MSFTNGP15.phx.gbl...
> Uri Dimant wrote:
> Thanks Uri
> I must admit, that I can't really see the purpose of the script, and also
> I can't see how it can be used to solve my problem.
> I've tried to see if I could get some ideas from the script, but I can't
> really see how I can use it?
> Regards
> Steen|||I think this might help you out, but there are problems with the aproach...
For one, the string concatenation leaves you open to SQL injection.
Granted, because you are selecting the values from a table, any malicious
injection code needs to actually be stored in your table, but it is still a
possibility. Second, this assumes you are dealing with numeric values, if
you need character values you will have to add in quotes along with the
commas.
declare @.SelectString varchar(1000)
set @.SelectString = TABLE1.FIELD1
set @.SelectString = replace(@.SelectString,' ',',')
set @.SelectString = 'select fieldlist from table2 where table2.id in (' +
@.SelectString + ')'
EXECUTE sp_executesql @.SelectString
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm having a problem finding out how I can split data in one field and
> then use the values to match records in another table.
> In table 1, I have a field where the values looks like
> e.g. "229 231 233 235". What I'd like to do, is to match these 4
> numbers with an ID in table 2 to get the values from table2. I.e. I'd
> like to split up this one value to 4 values (229, 232, 233,235).
> I've tried to use REPLACE to put in a "," between each so I could use it
> as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
> Has any of you any other suggestions to how it can be done? It's not
> always the same number of numbers in the field (e.g. another one could
> be "456 29580010" ). The field is a VARCHAR(1000) and there're also
> some text strings in it. These seems to be some old crab though and I
> don't need these values.
> The only "general" thing with the formatting, seems to be that there are
> 2 spaces between each of the numbers I'd like to get out, so I think I
> can use that as a "delimiter".
> Anyone who has some hints to this?
> Regards
> Steen
>|||Uri Dimant wrote:
> I'm really sorry Steen , by posting Anith's example I did mean to give you
> an idea to solve the problem
> See if this helps you
> SELECT IDENTITY(INT) "n" INTO Numbers
> FROM sysobjects s1
> CROSS JOIN sysobjects s2
> GO
> CREATE TABLE #Source (col1 INT NOT NULL)
> CREATE TABLE #Target (col1 INT NOT NULL)
> DECLARE @.Ids VARCHAR(200)
> SET @.Ids = '5 33 229 1 22'
> --Inserting the values to the source table
> INSERT INTO #Source
> SELECT SUBSTRING(@.Ids, n, CHARINDEX(' ', @.Ids + ' ', n) - n)
> from numbers where substring(' '+@.Ids,n,1)=' '
> AND n < LEN(@.Ids) + 1
> SELECT * FROM #Source
>
> DECLARE @.Ids VARCHAR(200)
> SET @.Ids = '5 33 10 1 22'
> --Inserting the values to the Target table
> INSERT INTO #Target
> SELECT SUBSTRING(@.Ids, n, CHARINDEX(' ', @.Ids + ' ', n) - n)
> from numbers where substring(' '+@.Ids,n,1)=' '
> AND n < LEN(@.Ids) + 1
>
> SELECT * FROM #Target
>
> -->>> e.g. "229 231 233 235". What I'd like to do, is to match these
4
> SELECT * FROM #Source WHERE NOT EXISTS
> (SELECT * FROM #Target WHERE #Source.col1=#Target.col1)
>
>
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:%233vpwsPHGHA.516@.TK2MSFTNGP15.phx.gbl...
>
Thanks for you input. I'll have to look further at the example. Right
now I still can't figure out how I can use it, but I'll check it out
tomorrow with a "fresh" pair of eyes..:-).
REgards
Steen|||Jim Underwood wrote:
> I think this might help you out, but there are problems with the aproach..
.
> For one, the string concatenation leaves you open to SQL injection.
> Granted, because you are selecting the values from a table, any malicious
> injection code needs to actually be stored in your table, but it is still
a
> possibility. Second, this assumes you are dealing with numeric values, if
> you need character values you will have to add in quotes along with the
> commas.
> declare @.SelectString varchar(1000)
> set @.SelectString = TABLE1.FIELD1
> set @.SelectString = replace(@.SelectString,' ',',')
> set @.SelectString = 'select fieldlist from table2 where table2.id in (' +
> @.SelectString + ')'
> EXECUTE sp_executesql @.SelectString
>
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
>
Hi Jim
The script is only for my own use, so I'm not so worried about
injections. It's just for producing some check lists to a few users.
I'll check out your script to see if it works. I'm having both numeric
and text values in the field, so I'll have to remove the text strings first.
Regards
Steen|||There is a function called charindex() which returns the numeric position of
one string within another string. You can join the two tables using
charindex, so that each row in MyTableA is joined with 0 - many rows in
MyTableB where charindex( .. ) > 0.
select
MyTableA.IDS,
MyTableB.ID
from MyTableA
left join MyTableB
on charindex(' '+MyTableB.ID+' ',' '+MyTableA.IDS+' ') > 0
The issue is that this data model is not properly normalized because it is
storing multiple values in one column:
http://www.agiledata.org/essays/dat...html#Normalize
This presents in at least 3 problems:
1. Accuracy: Can you depend on the format of the delimited values
reliable? The purpose of appending additional spaces before and after the
strings is to insure that:
charindex('999','123 ABC999 456') = 0
2. Performance: A non indexed table scan will probably be used due to
using a function for the join expression
http://www.microsoft.com/technet/pr...s/c0618260.mspx
http://www.sql-server-performance.c...ing_indexes.asp
3. Your queries will be more complex to write.
Let's assume that you have a Customer table and a Discount table.What is
needed is a reference table called CustomerDiscount that associates 0 - many
promotions for each customer.
For example:
CustomerID PromotionID
200 10
200 11
212 10
212 13
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm having a problem finding out how I can split data in one field and
> then use the values to match records in another table.
> In table 1, I have a field where the values looks like
> e.g. "229 231 233 235". What I'd like to do, is to match these 4
> numbers with an ID in table 2 to get the values from table2. I.e. I'd like
> to split up this one value to 4 values (229, 232, 233,235).
> I've tried to use REPLACE to put in a "," between each so I could use it
> as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
> Has any of you any other suggestions to how it can be done? It's not
> always the same number of numbers in the field (e.g. another one could be
> "456 29580010" ). The field is a VARCHAR(1000) and there're also some
> text strings in it. These seems to be some old crab though and I don't
> need these values.
> The only "general" thing with the formatting, seems to be that there are 2
> spaces between each of the numbers I'd like to get out, so I think I can
> use that as a "delimiter".
> Anyone who has some hints to this?
> Regards
> Steen
>|||On Thu, 19 Jan 2006 13:34:07 +0100, Steen Persson (DK) wrote:

>Hi
>I'm having a problem finding out how I can split data in one field and
>then use the values to match records in another table.
>In table 1, I have a field where the values looks like
>e.g. "229 231 233 235". What I'd like to do, is to match these 4
>numbers with an ID in table 2 to get the values from table2. I.e. I'd
>like to split up this one value to 4 values (229, 232, 233,235).
>I've tried to use REPLACE to put in a "," between each so I could use it
>as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
>Has any of you any other suggestions to how it can be done? It's not
>always the same number of numbers in the field (e.g. another one could
>be "456 29580010" ). The field is a VARCHAR(1000) and there're also
>some text strings in it. These seems to be some old crab though and I
>don't need these values.
>The only "general" thing with the formatting, seems to be that there are
>2 spaces between each of the numbers I'd like to get out, so I think I
>can use that as a "delimiter".
>Anyone who has some hints to this?
Hi Steen,
In addition to what others already wrote on this, I'll give you this
link:
http://www.sommarskog.se/arrays-in-sql.html
Also, try to change the design. Arrays really should not be stored in a
single column.
Hugo Kornelis, SQL Server MVP

Help needed?

Hi

I'm a newbie to SQL server..... l'm building a website where companies can save important data. I have a SQL server available but I'm not sure how to store the data. Should I create a new database for every user or should I store everything in the same database and then use a UserId to recognize the data and the user?

The data stored for each user are stored in tables which are exactly the same so all tables could be gathered into one table and then a UserId could tell which records belong to whom.

Hope my english isn't too bad..otherwise just ask me questions and I'll get back A.S.A.P.

Regards

Joachim

1 database will support many users. As you suggested, you would create a user table and each user would have an entry there. The primary key of that table (e.g. : UserId) would be used as a Foreign Key in other tables to identify which user owns the data. e.g. : an orders table would have a userId column to specify which user the order belonged to.

Hope this helps.

|||

hi joachim,

thats not a bad english at all.

regarding your question i suggest you take your time

studying the basic database concept such

normalization, entity integrity, domain integrity, relational integrity and bussiness integrity.

its not easy at first but it will help very much in the long run of your database career

regards

joey

Help needed about databases!

Hi

I'm a newbie to SQL server.... l'm building a website where companies can save important data. I have a SQL server available but I'm not sure how to store the data. Should I create a new database for every user or should I store everything in the same database and then use a UserId to recognize the data and the user? What about the case where I reaches let's say 1000 users in the one user per database case, it would be extremly difficult to have an overview of the databases or what?

The data stored for each user are stored in tables which are exactly the same so all tables could be gathered into one table and then a UserId could tell which records belong to whom.

Hope my english isn't too bad..otherwise just ask me questions and I'll get back A.S.A.P.

Regards

Joachim

You probably should just use a single database. Unless there are overriding issues like you distribute the database to the company. Then you wouldn't want one company to get another companies data. I'm assuming there is a single application where all the companies log in to the same site. If each company has separate sites, then you might actually need separate apps (and databases) for each.

Single app / db is easier to maintain but realize that changes for one then apply to all.

HTH.

help needed

Hi

I've just started learning SQL today. How do i insert values to a table i've created?

thnx

ice

Quote:

Originally Posted by realredice

Hi

I've just started learning SQL today. How do i insert values to a table i've created?

thnx

ice

hi,
just go through all the commands available in SQL server books on line, which is available with all MS SQL server installation, and try 2 learn urself best.. any way the answer is
INSERT INTO tab_name Values('MSSQL', 134)
if u r inserting varchar values enclose data between two single quotes like 'MSSQL"
if it is Numeric values write as it is like 14, 78.9 etc etc

regards,|||hi

thnx 4 the reply. Another q. Is there much difference between MySQL and SQL* Plus?

regards

ice

Help me print report button is not working

Hi
I am using Crystal Reports 8.5 version in VB 6.0. I am viewing reports
using CRviewer . When i click on Print report button in CR viewer controlnothing is happining. I am unable to print the report. please help meout. although with same printer settings other files(.Txt,.xls) get printed.
Thanx
RitikIs it possible for you to post the code over here ?

Help me Deploying rdl Report

Hi

I am new to SQL Reporting Services. I have developed an rdl report in SQL Server Reporting Serices 2005. It works very fine in development environment. But now i want to include this report in an exisitng project running at our client in some remote location. This report 'll be accessed by several clients from different offices.

To view reports locally I have added myself as an administrator on the local Report Server. But how would i authecticate specific users that i want to grant access to the report. I have a database table that stores users that have access to that report.

Also how do I upload these reports with all these users settings on the client's Report Server.

Looking forward for ur good response.

Regards

Asif Hameed

This isn't a complete answer, but here are some things you need to know.

Do the clients who will be accessing reports have windows accounts on the new server in the remote location (or can they have windows accounts)? If so, you can use windows groups to give authority to different folders and reports on the Report Server.

If not, you may be looking at a more custom solution which could include custom security extensions and a front end application.

Hope this points you in the right direction.

|||

Hi

thanx for your solution. I think I must go for some custom security extensions. But could you please explain a little bit more about custom security extensions. I mean how to create and use these in my project.

Another problem is deployment of reports. I am creating reports on my local server. How do I create the setup to copy these reports to client's server, or just manual copy would solve the problem.

Regards,

Asif Hameed

|||

Regarding custom security extensions, I'm not a good resource because I haven't written one, but I'm sure someone on the board has.

Regarding deployment, you can either change your properties on your solution in visual studio to point to your client's server, and deploy or you can browse to the client's http://domain/reports interface and upload the rdl file. If you don't have remote access, you can always email the file to the client or carry it on disk to upload loacally.

Hope that helps.

|||

Well, thats a great idea to email or send the rdl files to user, but the problem is that the user is not so much technical. How could I deploy the reports to his server, while I have no remote access.

Is there any possible way that I can make a setup, like PUBLISH in rdlc reports, that the user will only need to run the setup and the report will be deployed automatically to his server?

|||

I haven't had the need to do so in the past, so I'm just speculating. I believe that you can build deployment packages if you have the Pro version of Visual Studio. I'm assuming that this would include Reporting Services projects.

In addition, there is a reporting services scripting capability so you might be able to put together a zip of files for the client to extract into a designated folder such as c:\temp and then run the script, or double-click a batch file that runs the script.

Again sorry I don't have more. I'm just telling you the routes I would explore in your situation. Someone may come along with a good answer from their experience.

Good luck.

|||Well, thanx for your effort, I ll try your solution. I think it ll work. Thanx once again.|||

I have found an other solution for deploying rdl reports using MSI. Here is the link if anyone else is facing the same problem.

http://blogs.msdn.com/bimusings/archive/2006/03/01/541599.aspx

Hope this will work