[dba-SQLServer] IN() or NOT IN()

It definatly (in sql 2000) only takes list or single column subqueries.
I'm not at my pc right now or I'd try that query on ss2005

On 5/15/07, Stuart McLachlan <stuart at lexacorp.com.pg> wrote:
> Are you sure? I've never seen anything that says that IN() takes
> anything other than a Subquery or a List of expressions.
>
> The SQL Server 2000 that I have here returns:
> "Invalid column name 4-9" if I try that.
>
> On 15 May 2007 at 19:44, kens.programming wrote:
>
> > You shouldn't have to use a comma limited list, just square brackets to
> > designate your sets.
> >
> > IN ([4-9], [A-M])
> > IN ([^1-3], [^N-T])
> > NOT IN ([1-3], [N-T])
> > NOT IN ([^4-9], [^A-M])
> >
> > Ken
> >
> > -----Original Message-----
> > From: dba-sqlserver-bounces at databaseadvisors.com
> > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
> > Sent: Tuesday, May 15, 2007 7:34 PM
> > To: dba-sqlserver at databaseadvisors.com
> > Subject: [dba-SQLServer] IN() or NOT IN()
> >
> > I am trying to process a query where an income field has a set of possible
> > values, 1-9 and A-T.  The client wants values 409 and A-M.  Logically that
> > would be more efficient if it was NOT in(1-3,n-t).  Is it in fact more
> > efficient?  And can ranges like that be specified or do I need to use
> comma
> > delimted lists 1,2,3,n,o,p...?
> >
> > John W. Colby
> > Colby Consulting
> > www.ColbyConsulting.com
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> > No virus found in this incoming message.
> > Checked by AVG Free Edition.
> > Version: 7.5.467 / Virus Database: 269.7.1/805 - Release Date: 5/15/2007
> > 10:47 AM
> >
> >
> > No virus found in this outgoing message.
> > Checked by AVG Free Edition.
> > Version: 7.5.467 / Virus Database: 269.7.1/805 - Release Date: 5/15/2007
> > 10:47 AM
> >
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...