I searched through some threads and didn´t  find nothing similar, so here it goes...

this simple sql works...

select imv.matricula,
      rua.desc,
      bai.desc

from imovel imv

inner join  Rua          rua  on (rua.id    = imv.rua)
inner join  Bairro       bai  on (bai.id     = imv.bairro)

------

But... i had a bigger sql, where the = operator dont work (it returns a cross join) and i
must use 'in' for that to work...

  from "...\Esc\capa_esc.dat" cap

  inner join        "..\esc_arq.dat"                  esc  on (esc.codigo    =
cap.esc_escreve)
  left  join          "..\Esc\cap2_esc.dat"          cap2 on (cap2.capa     = cap.capa)
  left  outer join "..\Esc\cusrj_es.dat"            crj  on (crj.capa      = cap.capa)
  left  outer join "..\Esc\imv_esc.dat"            ime  on (ime.capa      = cap.capa)
  inner join       "..\Imoveis\Imovel.dat"        imv  on (imv.id  = ime.imovel)
  inner join       "..\Imoveis\Tipo_Rua.dat"     tru  on (tru.di    = imv.tipo_rua)
  inner join       "..\Imoveis\Rua.dat"            rua  on (rua.id    in (imv.rua))
  inner join       "..\Imoveis\Bairro.dat"         bai  on (bai.id    in (imv.bairro))

the problem only occurs with the tables rua and bairro

and i wonder why?????

or i missed something?

<< But... i had a bigger sql, where the = operator dont work (it returns a
cross join) and i must use 'in' for that to work... >>

What version of DBISAM are you using ?

If version 4, could you post the query plans for both versions of the same
query (= vs. IN) ?  I suspect that the joins are being re-ordered in a way
that causes an invalid result.

--
Tim Young
Elevate Software
www.elevatesoft.com

complementing,

just the first row is "cross joined"... in all ocasions

but after some tests i found that...

if the last table have an 'in', then it works

inner join       "...\Imoveis\Rua.dat"      rua  on (rua.codigo    = imv.rua)
inner join       "...\Imoveis\Bairro.dat"   bai  on (bai.codigo    in (imv.bairro))

but... if the last table have a '=' then it "cross-join"

inner join       "...\Imoveis\Rua.dat"      rua  on (rua.codigo    = imv.rua)
inner join       "...\Imoveis\Bairro.dat"   bai  on (bai.codigo    = imv.bairro)

and it "cross-join" both tables, lets say that one conain 2 records and another 6, then it
repeats 8 times...

always the last table...

Version 4.00

query just with '='

================================================================================
SQL statement (Executed with 4.25 Build 4)
================================================================================

  select --<Tag Ato>
         'RE'                   as TipoAto,
--          selo.Selo              as Selo,        
ime.imovel as imovel,
         cap.Capa               as Registro,
         cap.Data_Lavratura     as DataAto,
         esc.CPF                as Escrevente,
         esc.Nome               as NomeEscrevente,
         'CPF'                  as TipoDocumentoEscrevente,
         cap.Data_Abertura      as DataAtoRegistro,
         crj.TipoCobranca       as TipoCobranca,
         /**/
         crj.Custas11           as FETJ,         
         crj.Custas15           as FUNDPERJ,
         crj.Custas14           as FUNPERJ,
         crj.Custas12           as ValorMutua,
         crj.Custas13           as ValorAcoterj, --???????


         crj.Custas1    +
         crj.Custas2    +
         crj.Custas3    +
         crj.Custas4    +
         crj.Custas5    +
         crj.Custas6    +
         crj.Custas7    +
         crj.Custas8    +
         crj.Custas9    +
         crj.Custas10   +
         crj.Custas11   +
         crj.Custas12   +
         crj.Custas13   +
         crj.Custas14   +
         crj.Custas15           as ValorTotal, -- os CustasXX devem ser os mesmos da
parte de cima

         cap.Folha_Inicial      as FolhaInicial,
         cap.Folha_Final        as FolhaFinal,
         cap.Livro              as Livro,
         cap.Capa               as ATO,         
                                -- CodAtoLivroAdic????

         --<Tag Escritura>
         if (cap.Vl_Declarado = 0 then 'N' else 'S') as ValorDeclarado,
         if (cap.Imoveis      = '1' then 'S' else 'N') as Imovel,
                                 --SubClasseNatureza??????
         cap2.Local_Pratica      as LocalPratica,
                                 --PorProcuracao??????
         cap.Vl_Declarado        as ValorEscritura,
         --</Tag Escritura>

         --<DadosProcuracao>
         --?????????? Verificar onde esta a referencia para a procuracao
         --</DadosProcuracao>
         
          --<Bem>
                                --TipoRecolhimento?????
         imv.Localizacao        as TipoImovel,
         imv.Planta             as Local,
         imv.Iptu_Incra         as NumeroIncra,
         imv.Area               as Area,
                                --Denominacao????

         tru.Descricao+' '+
         rua.Descricao+', '+
         imv.numero+', '+
         imv.complemento        as Logradouro,
         bai.descricao          as Bairro,
--          cid.descricao          as Municipio,
         imv.Uf                 as Uf

         --</Bem>          

        



         --</Tag Ato>

  from "c:\Ansata\Tbl\Dat\Esc\capa_esc.dat" cap

  inner join       "c:\Ansata\Dat\Base\esc_arq.dat"     esc  on (esc.codigo    =
cap.esc_escreve)
  left  join       "c:\Ansata\Tbl\Dat\Esc\cap2_esc.dat" cap2 on (cap2.capa     =
cap.capa)
  left  outer join "c:\Ansata\Tbl\Dat\Esc\cusrj_es.dat" crj  on (crj.capa      =
cap.capa)
  left  outer join "c:\Ansata\Tbl\Dat\Esc\imv_esc.dat"  ime  on (ime.capa      =
cap.capa)
  inner join       "c:\Ansata\Dat\Imoveis\Imovel.dat"   imv  on (imv.auto_inc  =
ime.imovel)
  inner join       "c:\Ansata\Dat\Imoveis\Tipo_Rua.dat" tru  on (tru.codigo    =
imv.tipo_rua)
  inner join       "c:\Ansata\Dat\Imoveis\Rua.dat"      rua  on (rua.codigo    =
imv.rua)
  inner join       "c:\Ansata\Dat\Imoveis\Bairro.dat"   bai  on (bai.codigo    =
imv.bairro)
  inner join       "c:\Ansata\Dat\Imoveis\Cidade.dat"   cid  on (cid.codigo    =
imv.cidade)

order by registro

Tables Involved
---------------

capa_esc (cap) table opened shared, has 3759 rows
esc_arq (esc) table opened shared, has 25 rows
cap2_esc (cap2) table opened shared, has 1 rows
cusrj_es (crj) table opened shared, has 7 rows
imv_esc (ime) table opened shared, has 1770 rows
Imovel (imv) table opened shared, has 21 rows
Tipo_Rua (tru) table opened shared, has 2 rows
Rua (rua) table opened shared, has 13 rows
Bairro (bai) table opened shared, has 7 rows
Cidade (cid) table opened shared, has 2 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

Result set will be ordered by the following column(s) using a case-sensitive
temporary index:

Registro ASC

Join Ordering
-------------

The driver table is the capa_esc table (cap)

The capa_esc table (cap) is joined to the esc_arq table (esc) with the INNER
JOIN expression:

cap.esc_escreve = esc.codigo

The capa_esc table (cap) is joined to the cap2_esc table (cap2) with the LEFT
OUTER JOIN expression:

cap.capa = cap2.capa

The capa_esc table (cap) is joined to the cusrj_es table (crj) with the LEFT
OUTER JOIN expression:

cap.capa = crj.capa

The capa_esc table (cap) is joined to the imv_esc table (ime) with the LEFT
OUTER JOIN expression:

cap.capa = ime.capa

The imv_esc table (ime) is joined to the Imovel table (imv) with the INNER JOIN
expression:

ime.imovel = imv.auto_inc

The Imovel table (imv) is joined to the Tipo_Rua table (tru) with the INNER
JOIN expression:

imv.tipo_rua = tru.codigo

The Imovel table (imv) is joined to the Rua table (rua) with the INNER JOIN
expression:

imv.rua = rua.codigo

The Imovel table (imv) is joined to the Bairro table (bai) with the INNER JOIN
expression:

imv.bairro = bai.codigo

The Imovel table (imv) is joined to the Cidade table (cid) with the INNER JOIN
expression:

imv.cidade = cid.codigo

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

The joins are already in optimal order and cannot be optimized any further

Join Execution
--------------

Costs ARE NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the
optimizer to consider costs when optimizing this join

The expression:

cap.esc_escreve = esc.codigo

is OPTIMIZED

The expression:

cap.capa = cap2.capa

is OPTIMIZED

The expression:

cap.capa = crj.capa

is OPTIMIZED

The expression:

cap.capa = ime.capa

is OPTIMIZED

The expression:

ime.imovel = imv.auto_inc

is OPTIMIZED

The expression:

imv.tipo_rua = tru.codigo

is OPTIMIZED

The expression:

imv.rua = rua.codigo

is OPTIMIZED

The expression:

imv.bairro = bai.codigo

is OPTIMIZED

The expression:

imv.cidade = cid.codigo

is OPTIMIZED

================================================================================
>>>>> 3752 rows affected in 5,954 seconds
================================================================================


and with 'in' on the final table...

================================================================================
SQL statement (Executed with 4.25 Build 4)
================================================================================

  select --<Tag Ato>
         'RE'                   as TipoAto,
--          selo.Selo              as Selo,        
ime.imovel as imovel,
         cap.Capa               as Registro,
         cap.Data_Lavratura     as DataAto,
         esc.CPF                as Escrevente,
         esc.Nome               as NomeEscrevente,
         'CPF'                  as TipoDocumentoEscrevente,
         cap.Data_Abertura      as DataAtoRegistro,
         crj.TipoCobranca       as TipoCobranca,
         /**/
         crj.Custas11           as FETJ,         
         crj.Custas15           as FUNDPERJ,
         crj.Custas14           as FUNPERJ,
         crj.Custas12           as ValorMutua,
         crj.Custas13           as ValorAcoterj, --???????


         crj.Custas1    +
         crj.Custas2    +
         crj.Custas3    +
         crj.Custas4    +
         crj.Custas5    +
         crj.Custas6    +
         crj.Custas7    +
         crj.Custas8    +
         crj.Custas9    +
         crj.Custas10   +
         crj.Custas11   +
         crj.Custas12   +
         crj.Custas13   +
         crj.Custas14   +
         crj.Custas15           as ValorTotal, -- os CustasXX devem ser os mesmos da
parte de cima

         cap.Folha_Inicial      as FolhaInicial,
         cap.Folha_Final        as FolhaFinal,
         cap.Livro              as Livro,
         cap.Capa               as ATO,         
                                -- CodAtoLivroAdic????

         --<Tag Escritura>
         if (cap.Vl_Declarado = 0 then 'N' else 'S') as ValorDeclarado,
         if (cap.Imoveis      = '1' then 'S' else 'N') as Imovel,
                                 --SubClasseNatureza??????
         cap2.Local_Pratica      as LocalPratica,
                                 --PorProcuracao??????
         cap.Vl_Declarado        as ValorEscritura,
         --</Tag Escritura>

         --<DadosProcuracao>
         --?????????? Verificar onde esta a referencia para a procuracao
         --</DadosProcuracao>
         
          --<Bem>
                                --TipoRecolhimento?????
         imv.Localizacao        as TipoImovel,
         imv.Planta             as Local,
         imv.Iptu_Incra         as NumeroIncra,
         imv.Area               as Area,
                                --Denominacao????

         tru.Descricao+' '+
         rua.Descricao+', '+
         imv.numero+', '+
         imv.complemento        as Logradouro,
         bai.descricao          as Bairro,
--          cid.descricao          as Municipio,
         imv.Uf                 as Uf

         --</Bem>          

        



         --</Tag Ato>

  from "c:\Ansata\Tbl\Dat\Esc\capa_esc.dat" cap

  inner join       "c:\Ansata\Dat\Base\esc_arq.dat"     esc  on (esc.codigo    =
cap.esc_escreve)
  left  join       "c:\Ansata\Tbl\Dat\Esc\cap2_esc.dat" cap2 on (cap2.capa     =
cap.capa)
  left  outer join "c:\Ansata\Tbl\Dat\Esc\cusrj_es.dat" crj  on (crj.capa      =
cap.capa)
  left  outer join "c:\Ansata\Tbl\Dat\Esc\imv_esc.dat"  ime  on (ime.capa      =
cap.capa)
  inner join       "c:\Ansata\Dat\Imoveis\Imovel.dat"   imv  on (imv.auto_inc  =
ime.imovel)
  inner join       "c:\Ansata\Dat\Imoveis\Tipo_Rua.dat" tru  on (tru.codigo    =
imv.tipo_rua)
  inner join       "c:\Ansata\Dat\Imoveis\Rua.dat"      rua  on (rua.codigo    =
imv.rua)
  inner join       "c:\Ansata\Dat\Imoveis\Bairro.dat"   bai  on (bai.codigo    =
imv.bairro)
  inner join       "c:\Ansata\Dat\Imoveis\Cidade.dat"   cid  on (cid.codigo    in
(imv.cidade))

order by registro

Tables Involved
---------------

capa_esc (cap) table opened shared, has 3759 rows
esc_arq (esc) table opened shared, has 25 rows
cap2_esc (cap2) table opened shared, has 1 rows
cusrj_es (crj) table opened shared, has 7 rows
imv_esc (ime) table opened shared, has 1770 rows
Imovel (imv) table opened shared, has 21 rows
Tipo_Rua (tru) table opened shared, has 2 rows
Rua (rua) table opened shared, has 13 rows
Bairro (bai) table opened shared, has 7 rows
Cidade (cid) table opened shared, has 2 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

Result set will be ordered by the following column(s) using a case-sensitive
temporary index:

Registro ASC

Join Ordering
-------------

The driver table is the capa_esc table (cap)

The capa_esc table (cap) is joined to the esc_arq table (esc) with the INNER
JOIN expression:

cap.esc_escreve = esc.codigo

The capa_esc table (cap) is joined to the cap2_esc table (cap2) with the LEFT
OUTER JOIN expression:

cap.capa = cap2.capa

The capa_esc table (cap) is joined to the cusrj_es table (crj) with the LEFT
OUTER JOIN expression:

cap.capa = crj.capa

The capa_esc table (cap) is joined to the imv_esc table (ime) with the LEFT
OUTER JOIN expression:

cap.capa = ime.capa

The imv_esc table (ime) is joined to the Imovel table (imv) with the INNER JOIN
expression:

ime.imovel = imv.auto_inc

The Imovel table (imv) is joined to the Tipo_Rua table (tru) with the INNER
JOIN expression:

imv.tipo_rua = tru.codigo

The Imovel table (imv) is joined to the Rua table (rua) with the INNER JOIN
expression:

imv.rua = rua.codigo

The Imovel table (imv) is joined to the Bairro table (bai) with the INNER JOIN
expression:

imv.bairro = bai.codigo

The Imovel table (imv) is joined to the Cidade table (cid) with the INNER JOIN
expression:

cid.codigo in (imv.cidade)

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

The joins are already in optimal order and cannot be optimized any further

Join Execution
--------------

Costs ARE NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the
optimizer to consider costs when optimizing this join

The expression:

cap.esc_escreve = esc.codigo

is OPTIMIZED

The expression:

cap.capa = cap2.capa

is OPTIMIZED

The expression:

cap.capa = crj.capa

is OPTIMIZED

The expression:

cap.capa = ime.capa

is OPTIMIZED

The expression:

ime.imovel = imv.auto_inc

is OPTIMIZED

The expression:

imv.tipo_rua = tru.codigo

is OPTIMIZED

The expression:

imv.rua = rua.codigo

is OPTIMIZED

The expression:

imv.bairro = bai.codigo

is OPTIMIZED

The expression:

cid.codigo in (imv.cidade)

is UN-OPTIMIZED

================================================================================
>>>>> 3554 rows affected in 1,67 seconds
================================================================================

after more tests...

i have a total of 9 tables joining, but if i reduce them to 6 everything works great. As i
add more tables the problem arises...

but still - with more then 6 tables - if i had the in on the final, it works

Thanks.

Would it be possible to get the tables that you're using ?  If so, please
email them to me in a .zip file to my email address
(timyoung@elevatesoft.com).

--
Tim Young
Elevate Software
www.elevatesoft.com