not in statement

Hey gang. I have searched this, because i thought i was doing it right. what
i read says i am doing it right, but still getting an error. here is the
statement:

set schedule11 = conn.execute("select distinct(team_name) from teams where
not in (select teama, teamb from schedule where week = '" & var1 & "')")
do while not schedule11.eof
var_bye = schedule11.fields.item("team_name").value
schedule11.movenext
loop
what this SHOULD do, is get the only team_name that doesn't exist in the
teama or teamb fields in the schedule table. but i am getting an error
instead:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near the keyword 'in'.

/team_schedule_current.asp, line 106

this is mssql db

I tried not exists but that didn't work either. it doesn't return a value. i
put response write on the var_bye variable, and it is blank on the output.
so i am guessing that it isn't finding a value. i did a response.write on
the statement itself, and it is correct, showing the current week.

any ideas??

Re: not in statement

Ok, i changed it a little, but still doesn't work.
i changed it to

set schedule11 = conn.execute("select distinct(team_name) from teams where
not in (select teama, teamb from schedule where week = '" & var1 & "')")

because any team_name in the teams table will be distinct anyway.



"Jeff" wrote in message
news:3ImdnSdlLrOwz-XZRVn-pg@adelphia.com...
> Hey gang. I have searched this, because i thought i was doing it right.
> what i read says i am doing it right, but still getting an error. here is
> the statement:
>
> set schedule11 = conn.execute("select distinct(team_name) from teams where
> not in (select teama, teamb from schedule where week = '" & var1 & "')")
> do while not schedule11.eof
> var_bye = schedule11.fields.item("team_name").value
> schedule11.movenext
> loop
> what this SHOULD do, is get the only team_name that doesn't exist in the
> teama or teamb fields in the schedule table. but i am getting an error
> instead:
>
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near the keyword 'in'.
>
> /team_schedule_current.asp, line 106
>
> this is mssql db
>
> I tried not exists but that didn't work either. it doesn't return a value.
> i put response write on the var_bye variable, and it is blank on the
> output. so i am guessing that it isn't finding a value. i did a
> response.write on the statement itself, and it is correct, showing the
> current week.
>
> any ideas??
>
>

Re: not in statement

please ignore... i went in a different direction, and made changes in the
DB..


"Jeff" wrote in message
news:3ImdnSdlLrOwz-XZRVn-pg@adelphia.com...
> Hey gang. I have searched this, because i thought i was doing it right.
> what i read says i am doing it right, but still getting an error. here is
> the statement:
>
> set schedule11 = conn.execute("select distinct(team_name) from teams where
> not in (select teama, teamb from schedule where week = '" & var1 & "')")
> do while not schedule11.eof
> var_bye = schedule11.fields.item("team_name").value
> schedule11.movenext
> loop
> what this SHOULD do, is get the only team_name that doesn't exist in the
> teama or teamb fields in the schedule table. but i am getting an error
> instead:
>
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near the keyword 'in'.
>
> /team_schedule_current.asp, line 106
>
> this is mssql db
>
> I tried not exists but that didn't work either. it doesn't return a value.
> i put response write on the var_bye variable, and it is blank on the
> output. so i am guessing that it isn't finding a value. i did a
> response.write on the statement itself, and it is correct, showing the
> current week.
>
> any ideas??
>
>

Re: not in statement

Jeff wrote:
> Hey gang. I have searched this, because i thought i was doing it
> right. what i read says i am doing it right, but still getting an
> error. here is the statement:
>
> set schedule11 = conn.execute("select distinct(team_name) from teams
> where not in

A comparison, like a sentence, requires both a subject and an object. You
have only supplied the object. Go back to the source you got this from. You
will see the syntax is:

where in

> (select teama, teamb from schedule where week = '" &
> var1 & "')") do while not schedule11.eof
> var_bye = schedule11.fields.item("team_name").value
> schedule11.movenext
> loop
> what this SHOULD do, is get the only team_name that doesn't exist in
> the teama or teamb fields in the schedule table. but i am getting an
> error instead:
>
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near the keyword 'in'.
>
> /team_schedule_current.asp, line 106
>
> this is mssql db
>
> I tried not exists but that didn't work either. it doesn't return a
> value.

It doesn't need to. There are a couple ways to tackle this: outer joins and
WHERE EXISTS. You should test each option in Query Analyzer to see which
provides the best performance.
Try these in Query Analyzer (always test/debug your queries in QA before
attempting to run them in a client application. This leads to the other best
practice of using stored procedures.)

select distinct(team_name) from teams t
left join schedule ta on t.team_name=ta.teama
left join schedule tb on t.team_name=tb.teamb
where not (ta.teama is null or tb.teamb is null)

select distinct(team_name) from teams t
WHERE NOT EXISTS (select * from schedule
where teama=t.team_name) AND
NOT EXISTS (select * from schedule
where teamb=t.team_name)

select distinct(team_name) from teams t
WHERE NOT EXISTS (select * from schedule
where teama=t.team_name OR teamb=t.team_name)


PS. Your practice of using dynamic sql is exposing you to the risk of having
your site attacked by hackers using sql injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

If you are dead-set against using stored procedures, you can still protect
yourself by using parameters via strings containing ODBC parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

If I can convince you to use stored procedures, here is an easy technique
for passing parameters to them without using dynamic sql:
http://tinyurl.com/jyy0



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: not in statement

Jeff wrote:
> please ignore... i went in a different direction, and made changes in
> the DB..
>
too late, but read my message anyways

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: not in statement

Bob Barrows [MVP] wrote:
>
> select distinct(team_name) from teams t
> left join schedule ta on t.team_name=ta.teama
> left join schedule tb on t.team_name=tb.teamb
> where not (ta.teama is null or tb.teamb is null)
>
> select distinct(team_name) from teams t
> WHERE NOT EXISTS (select * from schedule
> where teama=t.team_name) AND
> NOT EXISTS (select * from schedule
> where teamb=t.team_name)
>
> select distinct(team_name) from teams t
> WHERE NOT EXISTS (select * from schedule
> where teama=t.team_name OR teamb=t.team_name)
>
>

Just because I've got a little time on my hands, here's a couple more
options:

select distinct(team_name) from teams t
WHERE NOT EXISTS (select * from
(SELECT teama as team from schedule
union
SELECT teamb from schedule) q
WHERE q.team = t.team_name)

This will probably be the worst performer (the use of IN is not
recommended):
select distinct(team_name) from teams t
WHERE NOT team_name IN (
SELECT teama as team from schedule
union
SELECT teamb from schedule)
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: not in statement

thanks bob as always.
so using stored procedures is that much more efficient that coding it in
asp?



"Bob Barrows [MVP]" wrote in message
news:eaqn$NZgGHA.4892@TK2MSFTNGP02.phx.gbl...
> Jeff wrote:
>> Hey gang. I have searched this, because i thought i was doing it
>> right. what i read says i am doing it right, but still getting an
>> error. here is the statement:
>>
>> set schedule11 = conn.execute("select distinct(team_name) from teams
>> where not in
>
> A comparison, like a sentence, requires both a subject and an object. You
> have only supplied the object. Go back to the source you got this from.
> You will see the syntax is:
>
> where in
>
>> (select teama, teamb from schedule where week = '" &
>> var1 & "')") do while not schedule11.eof
>> var_bye = schedule11.fields.item("team_name").value
>> schedule11.movenext
>> loop
>> what this SHOULD do, is get the only team_name that doesn't exist in
>> the teama or teamb fields in the schedule table. but i am getting an
>> error instead:
>>
>> Microsoft OLE DB Provider for SQL Server error '80040e14'
>> Incorrect syntax near the keyword 'in'.
>>
>> /team_schedule_current.asp, line 106
>>
>> this is mssql db
>>
>> I tried not exists but that didn't work either. it doesn't return a
>> value.
>
> It doesn't need to. There are a couple ways to tackle this: outer joins
> and WHERE EXISTS. You should test each option in Query Analyzer to see
> which provides the best performance.
> Try these in Query Analyzer (always test/debug your queries in QA before
> attempting to run them in a client application. This leads to the other
> best practice of using stored procedures.)
>
> select distinct(team_name) from teams t
> left join schedule ta on t.team_name=ta.teama
> left join schedule tb on t.team_name=tb.teamb
> where not (ta.teama is null or tb.teamb is null)
>
> select distinct(team_name) from teams t
> WHERE NOT EXISTS (select * from schedule
> where teama=t.team_name) AND
> NOT EXISTS (select * from schedule
> where teamb=t.team_name)
>
> select distinct(team_name) from teams t
> WHERE NOT EXISTS (select * from schedule
> where teama=t.team_name OR teamb=t.team_name)
>
>
> PS. Your practice of using dynamic sql is exposing you to the risk of
> having your site attacked by hackers using sql injection:
> http://mvp.unixwiz.net/techtips/sql-injection.html
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>
> If you are dead-set against using stored procedures, you can still protect
> yourself by using parameters via strings containing ODBC parameter
> markers:
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
> If I can convince you to use stored procedures, here is an easy technique
> for passing parameters to them without using dynamic sql:
> http://tinyurl.com/jyy0
>
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: not in statement

Jeff wrote:
> thanks bob as always.
> so using stored procedures is that much more efficient that coding it
> in asp?
>
>

Usually, but not always.
However, the real benefit comes from :
1. encapsulation - when multiple steps need to be done in a database,
especially tasks where a step depends on the outcome of a previous step, it
is much more efficient to do all the steps in a single stored procedure,
rather than sending multiple statements to the database one after the other
2. re-use - do you find yourself creating the same sql statement in multiple
pages? Why not just create a single stored procedure and call that whenever
it is needed? This get even more valuable when you make a change somewhere
that requires a change to the sql statement. Would you rather change it once
in the procedure? Or many times in all the places it is used (granted, if
the change requires a change to the code that handles the results of the sql
statement, you'll probably have to visit all those pages anyways ... )
3. bandwidth conservation - which requires more bandwidth? Sending a long
sql statement (or multiple batched statements) over the wire? Or sending
the name of a stored procedure with the parameter values?
4. SQL 7+ creates and caches query plans for dynamically created sql
statements that meet certain requirements, so the pre-compilation benefit
for stored procedures is not as great as it was in sql 6.5. Some experts do
say the sql query optimizer does a better job of optimizing stored
procedures than dynamic sql statments, but unfortunately I've seen no
benchmarks to support this contention.

As SQL BOL says: bring the processing to the data rather than the data to
the processing

There are those who advise against the use of stored procedures. You should
google Frans Bouma's blog entry on this topic to see the arguments against
them.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: not in statement

very informative Bob. Thanks a bunch for that, and for the code examples
above.


"Jeff" wrote in message
news:JPGdndU5m-0Px-XZRVn-qQ@adelphia.com...
> thanks bob as always.
> so using stored procedures is that much more efficient that coding it in
> asp?
>
>
>
> "Bob Barrows [MVP]" wrote in message
> news:eaqn$NZgGHA.4892@TK2MSFTNGP02.phx.gbl...
>> Jeff wrote:
>>> Hey gang. I have searched this, because i thought i was doing it
>>> right. what i read says i am doing it right, but still getting an
>>> error. here is the statement:
>>>
>>> set schedule11 = conn.execute("select distinct(team_name) from teams
>>> where not in
>>
>> A comparison, like a sentence, requires both a subject and an object. You
>> have only supplied the object. Go back to the source you got this from.
>> You will see the syntax is:
>>
>> where in
>>
>>> (select teama, teamb from schedule where week = '" &
>>> var1 & "')") do while not schedule11.eof
>>> var_bye = schedule11.fields.item("team_name").value
>>> schedule11.movenext
>>> loop
>>> what this SHOULD do, is get the only team_name that doesn't exist in
>>> the teama or teamb fields in the schedule table. but i am getting an
>>> error instead:
>>>
>>> Microsoft OLE DB Provider for SQL Server error '80040e14'
>>> Incorrect syntax near the keyword 'in'.
>>>
>>> /team_schedule_current.asp, line 106
>>>
>>> this is mssql db
>>>
>>> I tried not exists but that didn't work either. it doesn't return a
>>> value.
>>
>> It doesn't need to. There are a couple ways to tackle this: outer joins
>> and WHERE EXISTS. You should test each option in Query Analyzer to see
>> which provides the best performance.
>> Try these in Query Analyzer (always test/debug your queries in QA before
>> attempting to run them in a client application. This leads to the other
>> best practice of using stored procedures.)
>>
>> select distinct(team_name) from teams t
>> left join schedule ta on t.team_name=ta.teama
>> left join schedule tb on t.team_name=tb.teamb
>> where not (ta.teama is null or tb.teamb is null)
>>
>> select distinct(team_name) from teams t
>> WHERE NOT EXISTS (select * from schedule
>> where teama=t.team_name) AND
>> NOT EXISTS (select * from schedule
>> where teamb=t.team_name)
>>
>> select distinct(team_name) from teams t
>> WHERE NOT EXISTS (select * from schedule
>> where teama=t.team_name OR teamb=t.team_name)
>>
>>
>> PS. Your practice of using dynamic sql is exposing you to the risk of
>> having your site attacked by hackers using sql injection:
>> http://mvp.unixwiz.net/techtips/sql-injection.html
>> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>>
>> If you are dead-set against using stored procedures, you can still
>> protect yourself by using parameters via strings containing ODBC
>> parameter markers:
>> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>>
>> If I can convince you to use stored procedures, here is an easy technique
>> for passing parameters to them without using dynamic sql:
>> http://tinyurl.com/jyy0
>>
>>
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so I
>> don't check it very often. If you must reply off-line, then remove the
>> "NO SPAM"
>>
>
>

Re: not in statement

Bob Barrows [MVP] wrote:
> Jeff wrote:
>> thanks bob as always.
>> so using stored procedures is that much more efficient that coding it
>> in asp?
>>
>>
>
> Usually, but not always.
> However, the real benefit comes from :
> 1. encapsulation - when multiple steps need to be done in a database,
> especially tasks where a step depends on the outcome of a previous
> step, it is much more efficient to do all the steps in a single
> stored procedure, rather than sending multiple statements to the
> database one after the other 2. re-use - do you find yourself creating the
> same sql statement in
> multiple pages? Why not just create a single stored procedure and
> call that whenever it is needed? This get even more valuable when you
> make a change somewhere that requires a change to the sql statement.
> Would you rather change it once in the procedure? Or many times in
> all the places it is used (granted, if the change requires a change
> to the code that handles the results of the sql statement, you'll
> probably have to visit all those pages anyways ... ) 3. bandwidth
> conservation - which requires more bandwidth? Sending a
> long sql statement (or multiple batched statements) over the wire? Or
> sending the name of a stored procedure with the parameter values?
> 4. SQL 7+ creates and caches query plans for dynamically created sql
> statements that meet certain requirements, so the pre-compilation
> benefit for stored procedures is not as great as it was in sql 6.5.
> Some experts do say the sql query optimizer does a better job of
> optimizing stored procedures than dynamic sql statments, but
> unfortunately I've seen no benchmarks to support this contention.
>
Oops! I forgot the main benefit:
It forces you to make sure the procedure/statement works before attempting
to run it from a client application. This is a big benefit when problems
occur because you know where debugging needs to occur.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"