Clipper On Line • Ver Tópico - Tentando localizar o erro

Tentando localizar o erro

Discussão sobre SQL

Moderador: Moderadores

 

Tentando localizar o erro

Mensagempor JoséQuintas » 20 Mar 2020 11:50

À primeira vista tudo certo, mas não vai.

 Erro executando comando:-2147217900 [ma-3.1.6][5.7.12-log]Unknown column 'ULTIMOPRECO.ESVALOR' in 'field list'  

 
SELECT LANCAMENTOS.*, ULTIMOPRECO.ESVALOR
FROM
   ( SELECT ESDATLAN, ESTIPLAN, ESQTDE, ESVALOR, ESCFOP, JPTRANSA.TRREACAO AS REACAO, 
     JPITEM.IEQTDCOM AS QTDCOM, JPESTOQUE.ESPRODUTO AS PRODUTO, JPITEM.IEPRODEP AS DEPTO,
     JPESTOQUE.ESPRODUTO, ULTIMOPRECO.ESVALOR AS ULTIMOPRECO
     FROM JPESTOQUE 
     LEFT JOIN JPTRANSA ON JPESTOQUE.ESTRANSA=JPTRANSA.IDTRANSA 
     LEFT JOIN JPITEM ON JPITEM.IDPRODUTO = JPESTOQUE.ESPRODUTO
     WHERE NOT SUBSTR( ESCFOP, 3 ) IN ( '905', '663', '664', '906' )
    ) AS LANCAMENTOS
    LEFT JOIN
       ( SELECT ESPRODUTO, ESVALOR FROM JPESTOQUE
         WHERE IDESTOQUE IN (
                             SELECT ID
                             FROM ( SELECT MAX( IDESTOQUE ), ESPRODUTO FROM JPESTOQUE 
                                    LEFT JOIN JPTRANSA ON JPESTOQUE.ESTRANSA=JPTRANSA.IDTRANSA 
                                    LEFT JOIN JPITEM ON JPITEM.IDPRODUTO = JPESTOQUE.ESPRODUTO
                                    WHERE NOT SUBSTR( ESCFOP, 3 ) IN ( '905', '663', '664', '906' )
                                    AND ESTIPLAN = '2' AND NOT TRREACAO LIKE '%DEV%'
                                    GROUP BY ESPRODUTO
                                  ) AS IDLIST
                           )
       ) AS ULTIMOPRECO
ON LANCAMENTOS.ESPRODUTO = ULTIMOPRECO.ESPRODUTO
ORDER BY ESPRODUTO, ESDATLAN, ESTIPLAN; 
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18008
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Tentando localizar o erro

Mensagempor JoséQuintas » 20 Mar 2020 12:09

ALIAS não resolveu

 Erro executando comando:-2147217900 [ma-3.1.6][5.7.12-log]Unknown column 'ULTIMOPRECO.ESVALOR' in 'field list'  

SELECT LANCAMENTOS.*, ULTIMOPRECO.ESVALOR
FROM ( SELECT ESDATLAN, ESTIPLAN, ESQTDE, ESVALOR, ESCFOP, JPTRANSA.TRREACAO AS REACAO, 
          JPITEM.IEQTDCOM AS QTDCOM, A.ESPRODUTO AS PRODUTO, JPITEM.IEPRODEP AS DEPTO,
          A.ESPRODUTO, ULTIMOPRECO.ESVALOR AS ULTIMOVALOR
       FROM JPESTOQUE AS A
       LEFT JOIN JPTRANSA ON JPTRANSA.IDTRANSA = A.ESTRANSA
       LEFT JOIN JPITEM ON JPITEM.IDPRODUTO = A.ESPRODUTO
       WHERE NOT SUBSTR( ESCFOP, 3 ) IN ( '905', '663', '664', '906' )
     ) AS LANCAMENTOS
LEFT JOIN ( SELECT ESPRODUTO, ESVALOR
            FROM JPESTOQUE AS B
            WHERE IDESTOQUE IN ( SELECT ID
                                 FROM ( SELECT MAX( IDESTOQUE ) AS ID, ESPRODUTO
                                        FROM JPESTOQUE AS C
                                        LEFT JOIN JPTRANSA AS T ON T.IDTRANSA = C.ESTRANSA
                                        LEFT JOIN JPITEM AS I ON I.IDPRODUTO = C.ESPRODUTO
                                        WHERE NOT SUBSTR( C.ESCFOP, 3 ) IN ( '905', '663', '664', '906' )
                                           AND C.ESTIPLAN = '2' AND NOT T.TRREACAO LIKE '%DEV%'
                                        GROUP BY ESPRODUTO
                                      ) AS IDLIST
                               ) 
          ) AS ULTIMOPRECO ON LANCAMENTOS.ESPRODUTO = ULTIMOPRECO.ESPRODUTO
ORDER BY ESPRODUTO, ESDATLAN, ESTIPLAN; 
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18008
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Tentando localizar o erro

Mensagempor JoséQuintas » 20 Mar 2020 12:38

Simplifiquei, mas ficou demorada, cancelo antes de terminar.


SELECT ESDATLAN, JPESTOQUE.ESPRODUTO, ESTIPLAN, ESQTDE, ESCFOP, JPTRANSA.TRREACAO AS REACAO, 
       JPITEM.IEQTDCOM AS QTDCOM, JPITEM.IEPRODEP AS DEPTO, ULTIMOPRECO.ESVALOR
FROM JPESTOQUE
LEFT JOIN JPTRANSA ON JPTRANSA.IDTRANSA = JPESTOQUE.ESTRANSA
LEFT JOIN JPITEM ON JPITEM.IDPRODUTO = JPESTOQUE.ESPRODUTO
LEFT JOIN ( SELECT B.ESPRODUTO, B.ESVALOR
            FROM JPESTOQUE AS B
            WHERE B.IDESTOQUE IN ( SELECT ID
                                 FROM ( SELECT MAX( C.IDESTOQUE ) AS ID, C.ESPRODUTO
                                        FROM JPESTOQUE AS C
                                        LEFT JOIN JPTRANSA AS T ON T.IDTRANSA = C.ESTRANSA
                                        WHERE NOT SUBSTR( C.ESCFOP, 3 ) IN ( '905', '663', '664', '906' )
                                           AND C.ESTIPLAN = '2' AND NOT T.TRREACAO LIKE '%DEV%'
                                        GROUP BY C.ESPRODUTO
                                      ) AS IDLIST
                                ) 
         ) AS ULTIMOPRECO
ON JPESTOQUE.ESPRODUTO = ULTIMOPRECO.ESPRODUTO
WHERE NOT SUBSTR( JPESTOQUE.ESCFOP, 3 ) IN ( '905', '663', '664', '906' )
ORDER BY ESPRODUTO, ESDATLAN, ESTIPLAN; 


Basicamente é:

pego os lançamentos de estoque (JPESTOQUE) com os últimos preços de compra (ULTIMOPRECO)

Para o último preço, pego a lista com os maiores IDs de cada produto (IDLIST), e os respectivos lançamentos (ULTIMOPRECO)

Cada um individual é rápido, relacionar códigos entre os dois deveria ser rápido mas não está sendo.
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18008
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Tentando localizar o erro

Mensagempor JoséQuintas » 20 Mar 2020 12:43

Alguma idéia sobre qual pode ser o problema?
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18008
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Tentando localizar o erro

Mensagempor JoséQuintas » 20 Mar 2020 12:55

Cada parte isolada leva 12 segundos.

preco2.png


preco1.png
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18008
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Tentando localizar o erro

Mensagempor JoséQuintas » 20 Mar 2020 12:58

E as duas juntas, até cancelo porque demora muito.

preco3.png
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18008
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Tentando localizar o erro

Mensagempor JoséQuintas » 20 Mar 2020 13:11

Antes que comentem do left join.... já que o inner join é o mais comum:

There are different types of joins available in SQL: INNER JOIN: returns rows when there is a match in both tables. LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table. RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.


Como as informações vieram de DBF, e não uso relacionamento pré-existente, o que entendo é:
INNER JOIN - só vai mostrar lançamento de estoque com produto cadastrado
LEFT JOIN - vai mostrar lançamento mesmo que não exista produto cadastrado
RIGHT JOIN - vai mostrar até produtos sem lançamento (que não se aplica neste caso)

É por isso que tenho preferido usar o LEFT JOIN, pra virem todos os lançamentos, mesmo se nào existir cadastro.
Então... LEFT JOIN NÃO é o problema.
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18008
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Tentando localizar o erro

Mensagempor asimoes » 20 Mar 2020 14:12

Faz um select acima englobando o select e chama as colunas que você quer.

select alias.col1, alias.col2
(
select....

)

Essa coluna que você quer não é vista no select principal porque ela é da subquery, mas se você fizer um select por cima de tudo deve funcionar.
â–ºHarbour 3.x | Minigui xx-x | HwGuiâ—„
Pense nas possibilidades abstraia as dificuldades.
Não corrigir nossas falhas é o mesmo que cometer novos erros.
A imaginação é mais importante que o conhecimento. (Albert Einstein)
Avatar de usuário

asimoes
Colaborador

Colaborador
 
Mensagens: 4919
Data de registro: 26 Abr 2007 16:48
Cidade/Estado: RIO DE JANEIRO-RJ
Curtiu: 341 vezes
Mens.Curtidas: 258 vezes

Tentando localizar o erro

Mensagempor bencz » 20 Mar 2020 14:17

José, boa tarde!

Primeiramente, veja isso: http://www.pctoledo.com.br/forum/viewtopic.php?f=42&t=24040&start=15#p139372

Depois:
[*]Crie o relacionamento entre as tabelas
[*]Crie os indices
[*]Utilize um profiler para mostrar o que está consumindo mais tempo... isso varia de banco para banco...

Você já sabe que uma query em especial consome 12segudos+ para ser executada..., ao analisar a query, que é bastante simples por sinal, vejo o seguinte problema...
Falta de índice...

ALguns ajustes na query:

SELECT 
   <APELIDO TABELA>.ESDATLAN,
   <APELIDO TABELA>.ESPORDUTO,
   <APELIDO TABELA>.ESTIPLAN,
   <APELIDO TABELA>.ESQTDE,
   <APELIDO TABELA>.ESCFPOP,
   JPTRANSA.TRREACAO AS REACAO,
   JPITEM.IEQTDCOM AS QTDCOM,
   JPITEM.IEPRODEP AS DEPTO
FROM JPESTOQUE <INSIRA AQUI APELIDO JPESTOQUE>
LEFT JOIN JPTRANSA <INSIRA AQUI UM APELIDO PARA JPTRANSA> ON(<APELIDO JPTRANSA>.IDTRANSA = <APELIDO JPESTOQUE>.ESTRANSA
LEFT JOIN JPITEM <INSIRA AQUI UM APELIDO PARA JPITEM> ON(<APELIDO JPITEM>.IDPRODUTO = <APELIDO JPESTOQUE>.ESPRODUTO
WHERE LEFT(<APELIDO JPESTOQUE>.ESCFOP, 3) NOT IN( .... )
ORDER BY <APELIDO TABELA>.ESPRODUTO, <APELIDO TABELA>.ESDATLAN, <APELIDO TABELA>.ESTIPLAN;


Verifique se os campos "<APELIDO JPTRANSA>.IDTRANSA" e "<APELIDO JPESTOQUE>.ESTRANSA" são do mesmo tipo e verifique a mesma coisa para o outro join

Eu criaria os seguintes indices para teste:
CREATE INDEX JPESTOQUE_IDX ON JPESTOQUE (ESTRANSA, ESPORDUTO, ESDATLAN, ESTIPLAN, ESQTDE, ESCFPOP, ESCFOP);
CREATE INDEX JPTRANSA_IDX ON JPTRANSA (IDTRANSA, TRREACAO);
CREATE INDEX JPITEM_IDX ON JPITEM (IDPRODUTO, IEPRODEP);


Obs: da proxima vez colque a Query aqui, ao invés de postar foto da tela do gerenciador do banco de dados....
Imagem
Avatar de usuário

bencz
Usuário Nível 4

Usuário Nível 4
 
Mensagens: 517
Data de registro: 28 Abr 2012 17:36
Curtiu: 6 vezes
Mens.Curtidas: 34 vezes

Tentando localizar o erro

Mensagempor asimoes » 20 Mar 2020 14:19

Corrigindo...

select alias.col1, alias.col2 from
(
select....

)
â–ºHarbour 3.x | Minigui xx-x | HwGuiâ—„
Pense nas possibilidades abstraia as dificuldades.
Não corrigir nossas falhas é o mesmo que cometer novos erros.
A imaginação é mais importante que o conhecimento. (Albert Einstein)
Avatar de usuário

asimoes
Colaborador

Colaborador
 
Mensagens: 4919
Data de registro: 26 Abr 2007 16:48
Cidade/Estado: RIO DE JANEIRO-RJ
Curtiu: 341 vezes
Mens.Curtidas: 258 vezes

Tentando localizar o erro

Mensagempor bencz » 20 Mar 2020 14:26

Por experiencia própria... uma vez estava montando um sistema e tinha que fazer um select bem simples... erá entre uma tabela com 10 registros e outra com 1000 registros..., eu tinha que trazer varias informações nesse select... e as vezes, a query demorava até 5 minutos para ser executada... bastou criar os índices e pronto... retorno instantâneo...
Não adianta nada estar utilizando o banco de dados mais porreta do mundo, nos melhores servidores do mundo, se a estrutura do banco de dadas está mal projetada e construída e querys não otimizadas com a correta criação dos índices....
Bom, um é consequência do outro... se o banco tá mal projetado, consequentemente, as querys também estarão... falo isso por experiencia dos mais bizarros projetos nos quais já trabalhei e migrei....

>-------
Um outro detalhe importante, as tabelas TEM QUE TER CHAVE PRIMARIA!!!!!!!!!!!, tabela sem chave primaria, dificulta a busca para os SGDB deixando o processo mais lento....
Imagem
Avatar de usuário

bencz
Usuário Nível 4

Usuário Nível 4
 
Mensagens: 517
Data de registro: 28 Abr 2012 17:36
Curtiu: 6 vezes
Mens.Curtidas: 34 vezes

Tentando localizar o erro

Mensagempor asimoes » 20 Mar 2020 14:29

Eu tentaria o uso do
where exists (select alias.col from alias where alias.col = principal.col )

       WHERE  
            APC.D_DESLIG IS NULL AND 
            APC.D_FALECI IS NULL AND 
            EXISTS 
            ( SELECT 
                 ADM.MATRICULA 
              FROM 
                 ADMSAUDE ADM 
              WHERE 
                 ADM.MATRICULA = APC.CODIGO 
              ORDER BY 
                 ADM.MATRICULA 
              LIMIT 1 
             ) 
â–ºHarbour 3.x | Minigui xx-x | HwGuiâ—„
Pense nas possibilidades abstraia as dificuldades.
Não corrigir nossas falhas é o mesmo que cometer novos erros.
A imaginação é mais importante que o conhecimento. (Albert Einstein)
Avatar de usuário

asimoes
Colaborador

Colaborador
 
Mensagens: 4919
Data de registro: 26 Abr 2007 16:48
Cidade/Estado: RIO DE JANEIRO-RJ
Curtiu: 341 vezes
Mens.Curtidas: 258 vezes

Tentando localizar o erro

Mensagempor bencz » 20 Mar 2020 14:33

o EXISTS e NOT EXISTS também é uma boa alternativa...
Uma alternativa para fazer o "IN" mais rápido é:

Where <campo> NOT IN ( SELECT '603', '903', .. )
Imagem
Avatar de usuário

bencz
Usuário Nível 4

Usuário Nível 4
 
Mensagens: 517
Data de registro: 28 Abr 2012 17:36
Curtiu: 6 vezes
Mens.Curtidas: 34 vezes

Tentando localizar o erro

Mensagempor JoséQuintas » 20 Mar 2020 15:20

bencz escreveu:Obs: da proxima vez colque a Query aqui, ao invés de postar foto da tela do gerenciador do banco de dados....


Na verdade a primeira mensagem é a query, é que já tentei tantas modificações, que chega uma hora que deixa de ser o teste original.
O tempo de 12 segundos, acho que não é da query, porque no log mostra milésimos de segundo.

O detalhe é: o relacionamento é feito em memória, e não diretamente com a tabela do MySQL, então nesse left join final nem entra índice.
No outro ajustado pode ser.

TODAS as minhas tabelas tem chave primária.
E o estoque com certeza tem índice por produto.

Basicamente as subqueries são rápidas, mas quando junta as duas, uma relacionada com a outra, é onde vém o problema.

A segunda query retorna apenas um registro por produto, com código de produto e valor.
a primeira são os lançamentos.
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18008
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Tentando localizar o erro

Mensagempor bencz » 20 Mar 2020 17:55

A diferença de criar um simples indice...

Imagem

Eu instalei o MariaDB no meu desktop e rodei um programa que fiz que migra o bando de dados, do esse meu programa migra entra MS-SQL server, PostgreSQL, MYSQL, MariaDB, DB2/400 e DB/Z... só escolher a origem e o destino... o programa se vira em portar a estrutura, índices e o conteúdo das tabelas...

Rodei o profiler e vi a falta de um índice, em uma tabela com 4 registros, que é a tabela "FYSCO_SituacaoProcuracoes"... ao criar o índice o tempo de execução da query caiu em 4ms....
Imagem
Avatar de usuário

bencz
Usuário Nível 4

Usuário Nível 4
 
Mensagens: 517
Data de registro: 28 Abr 2012 17:36
Curtiu: 6 vezes
Mens.Curtidas: 34 vezes

Próximo



Retornar para SQL

Quem está online

Usuários vendo este fórum: Nenhum usuário registrado online e 7 visitantes


Ola Amigo, espero que meu site e forum tem lhe beneficiado, com exemplos e dicas de programacao.
Entao divulgue o link da Doacao abaixo para seus amigos e redes sociais ou faça uma doacao para o site forum...
MUITO OBRIGADO PELA SUA DOACAO!
Faça uma doação para o forum
cron
v
Olá visitante, seja bem-vindo ao Fórum Clipper On Line!
Efetue o seu login ou faça o seu Registro