FreeTDS Development Group

[brian AT bruns.com]

All this misses the point of course, which is that
FreeTDS is oblivious to using DISTINCT or any other SQL
construct.  All it care about are the number and types
of columns/rows coming back.  This problem if it lies
within FreeTDS should be reproducible with the simple
"SELECT * from table1 where Target_ID=:C" (:C being
some constant normally returned from the subquery.

Perhaps some action within the application has left the
TDS connection in a bizarre state.  See if you can get
this to reproduce on a smaller scale maybe? (that is,
cut the code down to the barest essentials while having
it still fail).

Brian

On Thu, 08 Jan 2004 11:18:42 -0800, Michael Peppler
wrote:

> 
> On Thu, 2004-01-08 at 11:08, Lowden, James K wrote:
> > > From: Steven Orton [mailto:sorton9999 AT usa.net]
> > > Sent: January 8, 2004 1:57 PM
> > > 
> > >   I seem to be having problems with the following
> query:
> > > 
> > >   SELECT * FROM table1 WHERE Target_ID=(SELECT
> DISTINCT 
> > > Target_ID FROM table2
> > > WHERE PlatformTypeID=62 AND db_ctx_id=3)
> > 
> > It's not a good idea to rely on a subquery returning
> only one row to a point
> > evaluation.  How do you expect the server to cope if
> it returns two
> > (distinct) Target_IDs?  
> > 
> > I would do:
> > 
> > SELECT * FROM table1 
> > WHERE Target_ID in (
> >     SELECT DISTINCT Target_ID 
> >     FROM table2
> >     WHERE PlatformTypeID=62 
> >     AND db_ctx_id=3
> >     )
> 
> In general using a join rather than a sub-query gets
> better performance:
> 
> select * 
>   from table1 t1
>      , table2 t2
>  where t1.TargetID = t2.TargetID
>    and t2.PlatformTypeID = 62
>    and t2.db_ctx_id = 3
> 
> Michael
> -- 
> Michael Peppler                              Data
> Migrations, Inc.
> mpeppler AT peppler.org                
> http://www.mbay.net/~mpeppler
> Sybase T-SQL/OpenClient/OpenServer/C/Perl developer
> available for short or 
> long term contract positions -
> http://www.mbay.net/~mpeppler/resume.html
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds