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]

Re: Weird select statement ?

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]

Re: Weird select statement ?


"RSMEINER" <rsmeiner@aol.c omcrap> wrote in message
news:2004041215 0517.03449.0000 0203@mb-m25.aol.com...[color=blue]
> 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[/color]

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]

Re: Weird select statement ?


"RSMEINER" <rsmeiner@aol.c omcrap> wrote in message
news:2004041215 0517.03449.0000 0203@mb-m25.aol.com...[color=blue]
> 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[/color]

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]

Re: Weird select statement ?

>Your design is wrong in relational terms. Don't ever put delimited lists[color=blue]
>into a column. You should normalize your schedules into a separate table:[/color]

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]

Re: Weird select statement ?

>Your design is wrong in relational terms. Don't ever put delimited lists[color=blue]
>into a column. You should normalize your schedules into a separate table:[/color]

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]

Re: Weird select statement ?

>Here is one way:[color=blue]
>
>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[/color]

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]

Re: Weird select statement ?

RSMEINER wrote:
<snip>[color=blue]
> But I just want to know why the IN doesn't work. It's maddening.
>
> Randy
> http://members.aol.com/rsmeiner[/color]

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]

Re: Weird select statement ?

>Here is one way:[color=blue]
>
>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[/color]

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]

Re: Weird select statement ?

RSMEINER wrote:
<snip>[color=blue]
> But I just want to know why the IN doesn't work. It's maddening.
>
> Randy
> http://members.aol.com/rsmeiner[/color]

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]

Re: Weird select statement ?

>If you google "Dynamic SQL" you will get a link to Erland Sommerskog's[color=blue]
>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
>[/color]

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]

Re: Weird select statement ?

>If you google "Dynamic SQL" you will get a link to Erland Sommerskog's[color=blue]
>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
>[/color]

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-]

Re: Weird select statement ?

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" <rsmeiner@aol.c omcrap> wrote in message news:2004041217 0148.17478.0000 0221@mb-m20.aol.com...[color=blue][color=green]
> >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[/color]
>
> 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[/color]

[-P-]

Re: Weird select statement ?

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" <rsmeiner@aol.c omcrap> wrote in message news:2004041217 0148.17478.0000 0221@mb-m20.aol.com...[color=blue][color=green]
> >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[/color]
>
> 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[/color]

[RSMEINER]

Re: Weird select statement ?

>It doesn't work because you're confusing a string containing commas with an[color=blue]
>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[/color]

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

Thanks.


Randy

http://members.aol.com/rsmeiner