Weird select statement ?

[RSMEINER]

I have a table with a column called Schedule. It's setup as varchar(120).
Schedule can contain several items at once like 'MO','WE','FR'.

I am trying to do a select like the following
but I can't get any results back.
I'm using MS Sql 2000.

SELECT * from tblMail
where 'MO' IN (Schedule)

Is this possible ?

Thanks.

Randy
http://members.aol.com/rsmeiner

[David Portas]

Your design is wrong in relational terms. Don't ever put delimited lists
into a column. You should normalize your schedules into a separate table:

CREATE TABLE MailSchedules (mailid INTEGER NOT NULL REFERENCES tblMail
(mailid) /* Assumed key for tblMail */, mailschedule CHAR(2) NOT NULL CHECK
(mailschedule IN ('MO','WE','FR')), PRIMARY KEY (mailid, mailschedule))

INSERT INTO MailSchedules (mailid, mailschedule) VALUES (123,'MO')
INSERT INTO MailSchedules (mailid, mailschedule) VALUES (123,'WE')
INSERT INTO MailSchedules (mailid, mailschedule) VALUES (456,'WE')

Now your query is much easier and more efficient:

SELECT M.*
FROM tblMail AS M
JOIN MailSchedules AS S
ON M.mailid = S.mailid AND S.mailschedule = 'MO'

Also, don't use SELECT * in production code. List the column names, that way
your code should be easier to maintain when the table structures change.

Hope this helps.

--
David Portas
SQL Server MVP
--

[Simon Hayes]

"RSMEINER" <rs******@aol.comcrap> wrote in message
news:20***************************@mb-m25.aol.com...

I have a table with a column called Schedule. It's setup as varchar(120).
Schedule can contain several items at once like 'MO','WE','FR'.

I am trying to do a select like the following
but I can't get any results back.
I'm using MS Sql 2000.

SELECT * from tblMail
where 'MO' IN (Schedule)

Is this possible ?

Thanks.

Randy
http://members.aol.com/rsmeiner

Here is one way:

SELECT *
FROM dbo.tblMail
WHERE Schedule LIKE '%MO%'

Or if the values are in single quotes in the table:

WHERE Schedule LIKE '%''MO''%'

The fact that you have repeating values in a single column suggests the data
model may be incorrect - you may want to review your table structure and see
if you can normalize it more fully, as it will make things easier.

Simon

[Simon Hayes]

"RSMEINER" <rs******@aol.comcrap> wrote in message
news:20***************************@mb-m25.aol.com...

I have a table with a column called Schedule. It's setup as varchar(120).
Schedule can contain several items at once like 'MO','WE','FR'.

I am trying to do a select like the following
but I can't get any results back.
I'm using MS Sql 2000.

SELECT * from tblMail
where 'MO' IN (Schedule)

Is this possible ?

Thanks.

Randy
http://members.aol.com/rsmeiner

Here is one way:

SELECT *
FROM dbo.tblMail
WHERE Schedule LIKE '%MO%'

Or if the values are in single quotes in the table:

WHERE Schedule LIKE '%''MO''%'

The fact that you have repeating values in a single column suggests the data
model may be incorrect - you may want to review your table structure and see
if you can normalize it more fully, as it will make things easier.

Simon

[RSMEINER]

>Your design is wrong in relational terms. Don't ever put delimited lists

into a column. You should normalize your schedules into a separate table:

Well, sure. This is more of a why the hell doesn't it work question
rather then a database or table design issue. Just something I was
trying out and just wanted to make it work.

But thanks for the lesson.

Randy
http://members.aol.com/rsmeiner

[RSMEINER]

>Your design is wrong in relational terms. Don't ever put delimited lists

into a column. You should normalize your schedules into a separate table:

Well, sure. This is more of a why the hell doesn't it work question
rather then a database or table design issue. Just something I was
trying out and just wanted to make it work.

But thanks for the lesson.

Randy
http://members.aol.com/rsmeiner

[RSMEINER]

>Here is one way:

SELECT *
FROM dbo.tblMail
WHERE Schedule LIKE '%MO%'

Or if the values are in single quotes in the table:

WHERE Schedule LIKE '%''MO''%'

The fact that you have repeating values in a single column suggests the data
model may be incorrect - you may want to review your table structure and see
if you can normalize it more fully, as it will make things easier.

Simon

Thanks, got that LIKE to work earlier

Select * from tblMail
where charindex('MO', schedule) > 0

works also.

But I just want to know why the IN doesn't work. It's maddening.
Randy
http://members.aol.com/rsmeiner

[Gert-Jan Strik]

RSMEINER wrote:
<snip>

But I just want to know why the IN doesn't work. It's maddening.

Randy
http://members.aol.com/rsmeiner

IN doesn't work, because the column schedule holds one value (it's a
scalar), even if it's value is '''MO'',''WE'',''FR'''. IN will do exact
match comparisons.

If you google "Dynamic SQL" you will get a link to Erland Sommerskog's
site which explains how you can use dynamic SQL to make this thing work.
Remember that SQL implementations use a 'simple' one pass compiler.
Using dynamic SQL you can basically create a two pass compiler. The
first pass will change

WHERE 'MO' IN ('''MO'',''WE'',''FR''')

to

WHERE 'MO' IN ('MO','WE','FR')

The second pass will be the actual query the way you intended it.

Of course, this all becomes very messy very fast, and it is therefore
not recommended. But I guess others have already mentioned that :-)

Hope this helps,
Gert-Jan

--
(Please reply only to the newsgroup)

[RSMEINER]

>Here is one way:

SELECT *
FROM dbo.tblMail
WHERE Schedule LIKE '%MO%'

Or if the values are in single quotes in the table:

WHERE Schedule LIKE '%''MO''%'

The fact that you have repeating values in a single column suggests the data
model may be incorrect - you may want to review your table structure and see
if you can normalize it more fully, as it will make things easier.

Simon

Thanks, got that LIKE to work earlier

Select * from tblMail
where charindex('MO', schedule) > 0

works also.

But I just want to know why the IN doesn't work. It's maddening.
Randy
http://members.aol.com/rsmeiner

[Gert-Jan Strik]

RSMEINER wrote:
<snip>

But I just want to know why the IN doesn't work. It's maddening.

Randy
http://members.aol.com/rsmeiner

IN doesn't work, because the column schedule holds one value (it's a
scalar), even if it's value is '''MO'',''WE'',''FR'''. IN will do exact
match comparisons.

If you google "Dynamic SQL" you will get a link to Erland Sommerskog's
site which explains how you can use dynamic SQL to make this thing work.
Remember that SQL implementations use a 'simple' one pass compiler.
Using dynamic SQL you can basically create a two pass compiler. The
first pass will change

WHERE 'MO' IN ('''MO'',''WE'',''FR''')

to

WHERE 'MO' IN ('MO','WE','FR')

The second pass will be the actual query the way you intended it.

Of course, this all becomes very messy very fast, and it is therefore
not recommended. But I guess others have already mentioned that :-)

Hope this helps,
Gert-Jan

--
(Please reply only to the newsgroup)

[RSMEINER]

>If you google "Dynamic SQL" you will get a link to Erland Sommerskog's

site which explains how you can use dynamic SQL to make this thing work.
Remember that SQL implementations use a 'simple' one pass compiler.
Using dynamic SQL you can basically create a two pass compiler. The
first pass will change

WHERE 'MO' IN ('''MO'',''WE'',''FR''')

to

WHERE 'MO' IN ('MO','WE','FR')

The second pass will be the actual query the way you intended it.

Of course, this all becomes very messy very fast, and it is therefore
not recommended. But I guess others have already mentioned that :-)

Hope this helps,
Gert-Jan

You mean something like this ?

declare @cmd VARCHAR(1024)
declare @Schedule VARCHAR(120)
SET @cmd = ''
SET @Schedule = '''A'''
SET @schedule = @Schedule + ',' + '''B'''
SET @schedule = @Schedule + ',' + '''C'''
PRINT @Schedule

Set @cmd = 'SELECT ' + '''Y''' +
' WHERE ' + '''A''' +
' IN ('+ @Schedule + ')'
Exec (@cmd)

This works. I'm just a stubborn old fart.

Thanks.

Randy
http://members.aol.com/rsmeiner

[RSMEINER]

>If you google "Dynamic SQL" you will get a link to Erland Sommerskog's

site which explains how you can use dynamic SQL to make this thing work.
Remember that SQL implementations use a 'simple' one pass compiler.
Using dynamic SQL you can basically create a two pass compiler. The
first pass will change

WHERE 'MO' IN ('''MO'',''WE'',''FR''')

to

WHERE 'MO' IN ('MO','WE','FR')

The second pass will be the actual query the way you intended it.

Of course, this all becomes very messy very fast, and it is therefore
not recommended. But I guess others have already mentioned that :-)

Hope this helps,
Gert-Jan

You mean something like this ?

declare @cmd VARCHAR(1024)
declare @Schedule VARCHAR(120)
SET @cmd = ''
SET @Schedule = '''A'''
SET @schedule = @Schedule + ',' + '''B'''
SET @schedule = @Schedule + ',' + '''C'''
PRINT @Schedule

Set @cmd = 'SELECT ' + '''Y''' +
' WHERE ' + '''A''' +
' IN ('+ @Schedule + ')'
Exec (@cmd)

This works. I'm just a stubborn old fart.

Thanks.

Randy
http://members.aol.com/rsmeiner

[-P-]

It doesn't work because you're confusing a string containing commas with an array whose items happen to be separated by
commas.

If you type the query out longhand, you'll see that you're passing an array of values to the IN operator
SELECT foo
FROM bar
WHERE bar.foobar IN ('a', 'b', 'c') ;

'a', 'b', 'c' is a tuple with three entries. You separate those entries with a comma in standard SQL syntax.

Now, when you referred to the column "SCHEDULE" in your query, you were passing a single string value, 14 characters in
length, which happened to contain commas in positions 4 and 9. Not the same thing at all....

--
Paul Horan
Buffalo, NY

"RSMEINER" <rs******@aol.comcrap> wrote in message news:20***************************@mb-m20.aol.com...

Here is one way:

SELECT *
FROM dbo.tblMail
WHERE Schedule LIKE '%MO%'

Or if the values are in single quotes in the table:

WHERE Schedule LIKE '%''MO''%'

The fact that you have repeating values in a single column suggests the data
model may be incorrect - you may want to review your table structure and see
if you can normalize it more fully, as it will make things easier.

Simon

Thanks, got that LIKE to work earlier

Select * from tblMail
where charindex('MO', schedule) > 0

works also.

But I just want to know why the IN doesn't work. It's maddening.
Randy
http://members.aol.com/rsmeiner

[-P-]

It doesn't work because you're confusing a string containing commas with an array whose items happen to be separated by
commas.

If you type the query out longhand, you'll see that you're passing an array of values to the IN operator
SELECT foo
FROM bar
WHERE bar.foobar IN ('a', 'b', 'c') ;

'a', 'b', 'c' is a tuple with three entries. You separate those entries with a comma in standard SQL syntax.

Now, when you referred to the column "SCHEDULE" in your query, you were passing a single string value, 14 characters in
length, which happened to contain commas in positions 4 and 9. Not the same thing at all....

--
Paul Horan
Buffalo, NY

"RSMEINER" <rs******@aol.comcrap> wrote in message news:20***************************@mb-m20.aol.com...

Here is one way:

SELECT *
FROM dbo.tblMail
WHERE Schedule LIKE '%MO%'

Or if the values are in single quotes in the table:

WHERE Schedule LIKE '%''MO''%'

The fact that you have repeating values in a single column suggests the data
model may be incorrect - you may want to review your table structure and see
if you can normalize it more fully, as it will make things easier.

Simon

Thanks, got that LIKE to work earlier

Select * from tblMail
where charindex('MO', schedule) > 0

works also.

But I just want to know why the IN doesn't work. It's maddening.
Randy
http://members.aol.com/rsmeiner

[RSMEINER]

>It doesn't work because you're confusing a string containing commas with an

array whose items happen to be separated by
commas.

If you type the query out longhand, you'll see that you're passing an array
of values to the IN operator
SELECT foo
FROM bar
WHERE bar.foobar IN ('a', 'b', 'c') ;

'a', 'b', 'c' is a tuple with three entries. You separate those entries with
a comma in standard SQL syntax.

Now, when you referred to the column "SCHEDULE" in your query, you were
passing a single string value, 14 characters in
length, which happened to contain commas in positions 4 and 9. Not the same
thing at all....

--
Paul Horan

Some days it just doesn't pay to play with code.
This is one of those days.

Thanks.
Randy
http://members.aol.com/rsmeiner

[RSMEINER]

>It doesn't work because you're confusing a string containing commas with an

array whose items happen to be separated by
commas.

If you type the query out longhand, you'll see that you're passing an array
of values to the IN operator
SELECT foo
FROM bar
WHERE bar.foobar IN ('a', 'b', 'c') ;

'a', 'b', 'c' is a tuple with three entries. You separate those entries with
a comma in standard SQL syntax.

Now, when you referred to the column "SCHEDULE" in your query, you were
passing a single string value, 14 characters in
length, which happened to contain commas in positions 4 and 9. Not the same
thing at all....

--
Paul Horan

Some days it just doesn't pay to play with code.
This is one of those days.

Thanks.
Randy
http://members.aol.com/rsmeiner