Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 6 of 6 total |
Strange behaviour with = operator |
Wed, Jan 30 2008 1:47 PM | Permanent Link |
DonOctavioDelFlores | 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? |
Wed, Jan 30 2008 2:28 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << 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 |
Wed, Jan 30 2008 2:30 PM | Permanent Link |
DonOctavioDelFlores | 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... |
Wed, Jan 30 2008 2:33 PM | Permanent Link |
DonOctavioDelFlores | 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 ================================================================================ |
Wed, Jan 30 2008 2:57 PM | Permanent Link |
DonOctavioDelFlores | 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 |
Thu, Jan 31 2008 6:41 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |