MySQL: COALESCE pra várias tabelas

Mensagem por **JoséQuintas** » 15 Abr 2021 18:47

  WITH OBJECT cnSQL
      :cSQL := "SELECT IDPRODUTO, IEPRODEP, IEPROSEC, IEPROGRU, IENOME," + ;
         " GREATEST( IEVALCUS, IECUSCON * 1.04, IEULTPRE * 1.04 ) AS VALCUSTO, IETIPO," + ;
         " PRODEPNOME, PROSECNOME, PROGRUNOME," + ;
         " ( 100 + COALESCE( A.PERCA, B.PERCA, C.PERCA, 0 ) ) / 100 * GREATEST( IEVALCUS, IECUSCON * 1.04, IEULTPRE * 1.04 ) AS TABA," + ;
         " ( 100 + COALESCE( A.PERCB, B.PERCB, C.PERCB, 0 ) ) / 100 * GREATEST( IEVALCUS, IECUSCON * 1.04, IEULTPRE * 1.04 ) AS TABB," + ;
         " ( 100 + COALESCE( A.PERCC, B.PERCC, C.PERCC, 0 ) ) / 100 * GREATEST( IEVALCUS, IECUSCON * 1.04, IEULTPRE * 1.04 ) AS TABC," + ;
         " ( 100 + COALESCE( A.PERCD, B.PERCD, C.PERCD, 0 ) ) / 100 * GREATEST( IEVALCUS, IECUSCON * 1.04, IEULTPRE * 1.04 ) AS TABD," + ;
         " ( 100 + COALESCE( A.PERCE, B.PERCE, C.PERCE, 0 ) ) / 100 * GREATEST( IEVALCUS, IECUSCON * 1.04, IEULTPRE * 1.04 ) AS TABE," + ;
         " ( 100 + COALESCE( A.PERCF, B.PERCF, C.PERCF, 0 ) ) / 100 * GREATEST( IEVALCUS, IECUSCON * 1.04, IEULTPRE * 1.04 ) AS TABF" + ;
         " FROM JPITEM" + ;
         " LEFT JOIN JPTABPRODEP ON IDPRODEP = IEPRODEP" + ;
         " LEFT JOIN JPTABPROSEC ON IDPROSEC = IEPROSEC" + ;
         " LEFT JOIN JPTABPROGRU ON IDPROGRU = IEPROGRU" + ;
         " LEFT JOIN JPTABPERCENTUAL AS A ON A.PERCPRODUTO = IDPRODUTO" + ;
         " LEFT JOIN JPTABPERCENTUAL AS B ON B.PERCPRODEP = IEPRODEP" + ;
         " LEFT JOIN JPTABPERCENTUAL AS C ON C.PERCPRODEP = 0 AND C.PERCPRODUTO = 0" 
   :Execute()

O comando é interessante.

Em jptabpercentual, a empresa pode definir percentual de cálculo para um produto específico, para um departamento, ou pra tudo.
a diferença entre eles é conter código de produto ou de departamento.

Faço o relacionamento das três formas de pesquisa, e pego a primeira que aparecer, é pra isso que serve o COALESCE, pegar o primeiro valor que não seja nulo.
se houver percentual para o produto... é a tabela A, relacionando produto
se houver percentual para o departamento... é a tabela B, relacionando departamento
se houver percentual pra tudo zerado... é a tabela C, pegando zerado
e se não houver nenhuma... atribui o zero

Até que dá pra otimizar em DBF:

Código: Selecionar todos

SELECT produto
GOTO TOP
DO WHILE ! Eof()
   SELECT jptabpercentual 
   SEEK Str( produto->departamento, 10 ) + Str( produto->codigo, 10 )
   IF Eof()
      SEEK Str( produto->departamento, 10 ) + Str( 0, 10 )
      IF Eof()
         SEEK Str( 0, 10 ) + Str( 0, 10 )
      ENDIF
   ENDIF
   ? produto->PrecoA * jptabpercentual->PercA
   ? produto->PrecoB * jptabpercentual->PercB
   ? produto->PrecoC * jptabpercentual->PercC
   ? produto->PrecoD * jptabpercentual->PercD
   ? produto->PrecoE * jptabpercentual->PercE
   ? produto->PrecoF * jptabpercentual->PercF
   SELECT produto
   SKIP
ENDDO

Convém destacar:
Se não me engano, aqueles relacionamentos no MySQL podem ser dispensados do comando, caso eles já existam na própria base de dados.
Isso reduziria a necessidade de comandos adicionais na consulta.

E aquele campo calculado, que mencionei em outro post, poderia substituir esta parte:

Código: Selecionar todos

GREATEST( IEVALCUS, IECUSCON * 1.04, IEULTPRE * 1.04 )

É como já comentei: vamos aprendendo, testando, e se valer a pena, colocando em prática.

Nada impede de fazer o select dos produtos, depois o select de cada tabela, e fazer as contas/comparações pelo Harbour.
Ou os selects, e fazer os cálculos pelo Harbour.
Ou pedir pronto.
Ou já deixar relacionado no próprio banco de dados.
Ou usar campo calculado.
Ou talvez até STORED FUNCTION.
Ou até VIEW armazenado no SQL contendo tudo.

Tanto faz, de todos os jeitos funciona.
É cada um encontrar o que acha mais interessante pra fazer, conforme o que conhece, ou conforme a necessidade.
De um jeito ou de outro fica resolvido.

Dependendo da solução, melhor um servidor mais rápido, ou um terminal mais rápido, ou rede mais rápida.

Mas de um modo geral, é 1 segundo pra ter o resultado no MySQL.
Se for muito mais que isso, melhor revisar o uso.

Mensagem por **JoséQuintas** » 15 Abr 2021 18:56

Aproveitando também....

percebi agora.

No DBF, quando Eof() retorna tudo em branco.
No MySQL, retorna NULL, equivalente a NIL, NADA.

Nessas horas o NULL facilita mais do que se fosse testar Eof(), porque dependeria de ALIAS.

ALIAS é coisa de DBF ?
NÃO.

no dbf é arquivo->codigo
no MySQL seria arquivo.codigo, mas só precisa se houver nome repetido, porque se for único o MySQL já sabe aonde pegar.
Se quiser comandos mais simples, é bom ter nomes o mais únicos possíveis, apesar que sempre tem exceção.

No final, parece que tudo fica igual, a gente é que se assusta no começo, mas com o tempo acostuma.

Mensagem por **JoséQuintas** » 15 Abr 2021 20:35

Agora achei que tem muita coisa repetida, até assusta, mas já tive uma idéia pra reduzir, ainda vou testar.

Código: Selecionar todos

         :cSQL := "SELECT IDPRODUTO, IEPRODEP, IEVALCUS, IECUSCON, IEULTPRE," + ;
            " ( COALESCE( A.PERCA, B.PERCA, C.PERCA, 0 ) AS PERCENTALA," + ;
            " ( COALESCE( A.PERCB, B.PERCB, C.PERCB, 0 ) AS PERCENTALB," + ;
            " ( COALESCE( A.PERCC, B.PERCC, C.PERCC, 0 ) AS PERCENTALC," + ;
            " ( COALESCE( A.PERCD, B.PERCD, C.PERCD, 0 ) AS PERCENTALD," + ;
            " ( COALESCE( A.PERCE, B.PERCE, C.PERCE, 0 ) AS PERCENTALE," + ;
            " ( COALESCE( A.PERCF, B.PERCF, C.PERCF, 0 ) AS PERCENTALF," + ;
            " ( COALESCE( A.PERCA, B.PERCA, C.PERCA, 0 ) ) / 100 * GREATEST( IEVALCUS, IECUSCON * 1.04, IEULTPRE * 1.04 ) AS TABA," + ;
            " ( 100 + COALESCE( A.PERCB, B.PERCB, C.PERCB, 0 ) ) / 100 * GREATEST( IEVALCUS, IECUSCON * 1.04, IEULTPRE * 1.04 ) AS TABB," + ;
            " ( 100 + COALESCE( A.PERCC, B.PERCC, C.PERCC, 0 ) ) / 100 * GREATEST( IEVALCUS, IECUSCON * 1.04, IEULTPRE * 1.04 ) AS TABC," + ;
            " ( 100 + COALESCE( A.PERCD, B.PERCD, C.PERCD, 0 ) ) / 100 * GREATEST( IEVALCUS, IECUSCON * 1.04, IEULTPRE * 1.04 ) AS TABD," + ;
            " ( 100 + COALESCE( A.PERCE, B.PERCE, C.PERCE, 0 ) ) / 100 * GREATEST( IEVALCUS, IECUSCON * 1.04, IEULTPRE * 1.04 ) AS TABE," + ;
            " ( 100 + COALESCE( A.PERCF, B.PERCF, C.PERCF, 0 ) ) / 100 * GREATEST( IEVALCUS, IECUSCON * 1.04, IEULTPRE * 1.04 ) AS TABF" + ;
            " FROM JPITEM" + ;
            " LEFT JOIN JPTABPRODEP ON IDPRODEP = IEPRODEP" + ;
            " LEFT JOIN JPTABPROSEC ON IDPROSEC = IEPROSEC" + ;
            " LEFT JOIN JPTABPROGRU ON IDPROGRU = IEPROGRU" + ;
            " LEFT JOIN JPTABPERCENTUAL AS A ON A.PERCPRODUTO = IDPRODUTO" + ;
            " LEFT JOIN JPTABPERCENTUAL AS B ON B.PERCPRODEP = IEPRODEP" + ;
            " LEFT JOIN JPTABPERCENTUAL AS C ON C.PERCPRODEP = 0 AND C.PERCPRODUTO = 0" + ;
            " WHERE IDPRODUTO = " + NumberSQL( nIdProduto )

Mensagem por **JoséQuintas** » 15 Abr 2021 20:58

Modificado, e com as devidas correções.

Código: Selecionar todos

      WITH OBJECT cnSQL
         :cSQL := "SELECT D.*, " + ;
            " ( 100 + PERCENTUALA ) / 100 * VALORBASE AS TABA," + ;
            " ( 100 + PERCENTUALB ) / 100 * VALORBASE AS TABB," + ;
            " ( 100 + PERCENTUALC ) / 100 * VALORBASE AS TABC," + ;
            " ( 100 + PERCENTUALD ) / 100 * VALORBASE AS TABD," + ;
            " ( 100 + PERCENTUALE ) / 100 * VALORBASE AS TABE," + ;
            " ( 100 + PERCENTUALF ) / 100 * VALORBASE AS TABF" + ;
            " FROM (" + ;
               " SELECT IDPRODUTO, IEPRODEP, IEVALCUS, IECUSCON, IEULTPRE," + ;
               " COALESCE( A.PERCA, B.PERCA, C.PERCA, 0 ) AS PERCENTUALA," + ;
               " COALESCE( A.PERCB, B.PERCB, C.PERCB, 0 ) AS PERCENTUALB," + ;
               " COALESCE( A.PERCC, B.PERCC, C.PERCC, 0 ) AS PERCENTUALC," + ;
               " COALESCE( A.PERCD, B.PERCD, C.PERCD, 0 ) AS PERCENTUALD," + ;
               " COALESCE( A.PERCE, B.PERCE, C.PERCE, 0 ) AS PERCENTUALE," + ;
               " COALESCE( A.PERCF, B.PERCF, C.PERCF, 0 ) AS PERCENTUALF," + ;
               " GREATEST( IEVALCUS, IECUSCON * 1.04, IEULTPRE * 1.04 ) AS VALORBASE" + ;
               " FROM JPITEM" + ;
               " LEFT JOIN JPTABPRODEP ON IDPRODEP = IEPRODEP" + ;
               " LEFT JOIN JPTABPROSEC ON IDPROSEC = IEPROSEC" + ;
               " LEFT JOIN JPTABPROGRU ON IDPROGRU = IEPROGRU" + ;
               " LEFT JOIN JPTABPERCENTUAL AS A ON A.PERCPRODUTO = IDPRODUTO" + ;
               " LEFT JOIN JPTABPERCENTUAL AS B ON B.PERCPRODEP = IEPRODEP" + ;
               " LEFT JOIN JPTABPERCENTUAL AS C ON C.PERCPRODEP = 0 AND C.PERCPRODUTO = 0" + ;
               " WHERE IDPRODUTO = " + NumberSQL( nIdProduto ) + ;
               " ) AS D"
         :Execute()

Tem o select que pega informação, e encima desse, outro select que acrescenta os campos calculados.

Não sei dizer ainda se ficou melhor ou pior kkkkk
Parece que ficou um pouco mais limpo e legível.

Mensagem por **JoséQuintas** » 15 Abr 2021 21:10

Assim dá uma visão melhor da mudança.
Fez dieta pra ficar fininho kkkk