Re: Query working finse on Sql Server 6.5 and not working on Sql Server 2000

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 07/15/04 

Date: Thu, 15 Jul 2004 13:30:52 -0400

You are using the old join syntax... You need to convert the query to use
ANSI standard syntax... for outer join

select.... from titles right outer join publishers on titles.pub_id =
publishers.pub_id ( is equal to =*) 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Nick" <picknick75@hotmail.com> wrote in message
news:50c93db9.0407150801.1e1589fe@posting.google.com...
> HI all,
>
>    I have a problem with the following query. It works fine on Sql
> Server 6.5 but in Sql Server 2000 it doesn't work good, it give me out
> more rows than using 6.5 (that gives the right result).
>
> I think the problem is related to the use of left outer join and some
> setting related to ANSI NULLS. Anyone can help me?.....
>
> The query follows:
>
>
> SELECT DISTINCT at_ordini.geazi_codice,
>          at_ordini.codice_tipo_doc,
>          at_ordini.anno,
>          at_ordini.num_ordine,
>          0 as progressivo,
>          at_ordini.data_reg
>
>     FROM at_ordini,
>          at_ordini_dettaglio,
>          ge_sogget
>    WHERE ( at_ordini.num_ordine       *=
> at_ordini_dettaglio.num_ordine ) AND
>          ( at_ordini.geazi_codice     *=
> at_ordini_dettaglio.geazi_codice ) AND
>          ( at_ordini.codice_tipo_doc  *=
> at_ordini_dettaglio.codice_tipo_doc ) AND
>          ( at_ordini.anno             *= at_ordini_dettaglio.anno )
> AND
>          ( at_ordini.cocpia_codice    *= ge_sogget.cocpia_codice ) AND
>          ( at_ordini.intestatario_doc *= ge_sogget.gesog_codice ) AND
>          ( at_ordini_dettaglio.codice_tipo_riga >= @datiporiga ) AND
>          ( at_ordini_dettaglio.codice_tipo_riga <= @atiporiga ) AND
>          ( at_ordini.geazi_codice      = @codazi ) AND
>          ( at_ordini.codice_tipo_doc   = @tipodoc ) AND
>          ( at_ordini.data_reg         >= @dadata AND
>            at_ordini.data_reg         <= @adata ) AND
>          EXISTS ( SELECT *
>                     FROM at_automezzi
>                    WHERE at_automezzi.cod_automezzo =
> at_ordini_dettaglio.cod_automezzo AND
>                          at_automezzi.subvettore = 'S' )
>    ORDER BY at_ordini.data_reg,
>             at_ordini.num_ordine

Relevant Pages

  • Re: CONTAINS performance
    ... How can I predict the scalability of the simple query given that I don't ... but will measure the FTS with multiple clients issuing random FTS queries. ... B on A.bid=B.id left outer join ... the SQL Server query optimizer executes this query in the optimal manner ...
    (microsoft.public.sqlserver.fulltext)
  • Re: *=
    ... > from customers c, orders o, items i ... As Marek said *= is an old syntax for outer join. ... the query returns 5 rows. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: how to improve performance of LEFT JOIN
    ... LEFT OUTER JOIN TableB ... I add the subquery to query every table before 'LEFT JOIN' ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: How to query with both JOIN and aliases in Access?
    ... v.empID LEFT OUTER JOIN FirstDayWorked FROM ... This query performs in SQL Server without any problem. ... LEFT OUTER JOIN (SELECT empID, ...
    (microsoft.public.access.queries)
  • Re: combining inner and outer joins
    ... the query that works for you in Sybase, and the output you're looking for. ... > I've recently switched from Sybase to SQL Server, ... > of an outer join clause. ...
    (microsoft.public.sqlserver.programming)