Como fazer em SQL

Mensagem por **JoséQuintas** » 25 Jun 2020 12:37

Aqui tem atualização pra SQL e DBF ao mesmo tempo.
Problema: conforme o número de depósito, atualiza um saldo diferente, pode ser somar ou tirar, e precisa evitar erro de divisão por zero.

Código: Selecionar todos

STATIC FUNCTION UpdateJPESTOQUE( nIdEstoque, nSomaTira )

   LOCAL nSelect := Select(), cVarDep, cnSQL := ADOClass():New( AppConexao() )

   WITH OBJECT cnSQL
      :cSQL := "SELECT * FROM JPESTOQUE WHERE IDESTOQUE=" + NumberSQL( nIdEstoque )
      :Execute()
      Encontra( StrZero( :Number( "ESPRODUTO" ), 6 ), "jpitem", "item" )
      SELECT jpitem
      nSomaTira := iif( :String( "ESTIPLAN", 1 ) == "1", -nSomaTira, nSomaTira )
      WITH OBJECT cnSQL
         RecLock()
         IF :String( "ESNUMDEP", 1 ) $ "23456789"
            cVarDep := "jpitem->ieQtd" + :String( "ESNUMDEP", 1 )
            REPLACE &( cVarDep ) WITH &( cVarDep ) + ( :Number( "ESQTDE" ) * nSomaTira )
            :ExecuteCmd( "UPDATE JPITEM SET IEQTD" + :String( "ESNUMDEP", 1 ) + " = IEQTD" + ;
               :String( "ESNUMDEP", 1 ) + " + " + NumberSQL( nSomaTira * :Number( "ESQTDE" ) ) + " WHERE IDPRODUTO = " + NumberSQL( :Number( "ESPRODUTO" ) ) )
         ELSE
            REPLACE jpitem->ieQtd1 WITH jpitem->ieQtd1 + ( :Number( "ESQTDE" ) * nSomaTira )
            :ExecuteCmd( "UPDATE JPITEM SET IEQTD1 = IEQTD1 + " + NumberSQL( nSomaTira * :Number( "ESQTDE" ) ) + ;
               " WHERE IDPRODUTO = " + NumberSQL( :Number( "ESPRODUTO" ) ) )
         ENDIF
         RecUnlock()
      ENDWITH
      :CloseRecordset()
   ENDWITH
   SELECT ( nSelect )

   RETURN NIL

Tem como criar um comando pequeno? ou só indicando todos os depósitos?

UPDATE JPESTOQUE,JPITEM .....
UPDATE JPESTOQUE LEFT JOIN JPITEM .....

Essa parte ok, o problema é atualizar IEQTD1, IEQTD2.... IEQTD9.
TALVEZ... a melhor solução fosse criar uma tabela de saldos contendo numero e quantidade.... sei lá...
Por enquanto pensando na mesma estrutura atual.

Mensagem por **JoséQuintas** » 25 Jun 2020 12:58

UPDATE JPITEM
LEFT JOIN JPESTOQUE ON JPITEM.IDPRODUTO = JPESTOQUE.ESPRODUTO
[comando]
WHERE JPESTOQUE.IDESTOQUE = 123

não queria usar comando grande, mas por enquanto só imagino algo parecido com isto.
por enquanto nem testando a sintaxe correta

CASE
CASE JPESTOQUE.ESNUMDEP = 9 THEN SET JPITEM.IEQTD9 = JPITEM.IEQTD9 + ( JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 )
CASE JPESTOQUE.ESNUMDEP = 8 THEN SET JPITEM.IEQTD9 = JPITEM.IEQTD8 + ( JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 )
CASE JPESTOQUE.ESNUMDEP = 7 THEN SET JPITEM.IEQTD9 = JPITEM.IEQTD7 + ( JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 )
CASE JPESTOQUE.ESNUMDEP = 6 THEN SET JPITEM.IEQTD9 = JPITEM.IEQTD6 + ( JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 )
CASE JPESTOQUE.ESNUMDEP = 5 THEN SET JPITEM.IEQTD9 = JPITEM.IEQTD5 + ( JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 )
CASE JPESTOQUE.ESNUMDEP = 4 THEN SET JPITEM.IEQTD9 = JPITEM.IEQTD4 + ( JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 )
CASE JPESTOQUE.ESNUMDEP = 3 THEN SET JPITEM.IEQTD9 = JPITEM.IEQTD3 + ( JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 )
CASE JPESTOQUE.ESNUMDEP = 2 THEN SET JPITEM.IEQTD9 = JPITEM.IEQTD2 + ( JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 )
CASE ELSE THEN SET JPITEM.IEQTD9 = JPITEM.IEQTD1 + JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 )
END

Talvez pudesse usar algo do tipo

@CFIELD = CONCAT( 'JPESTOQUE.ESNUMDEP', IF( JPESTOQUE.ESNUMDEP BETWEEN '2' AND '9', JPESTOQUE.ESNUMDEP, '1' ) );
@QTDE = IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 );
SET @CFIELD = @CFIELD + @QTDE

apesar que isso tá mais pra stored procedure do que pra comando.

Mesmo assim, existe diferença entre usar direto no HeidiSQL e por comando, justamente porque no HeidiSQL podem ser vários comandos, que não dá pra fazer diretamente.

Mensagem por **JoséQuintas** » 25 Jun 2020 13:15

Como primeiro teste, aceitou isto, mas eu queria comando menor

Código: Selecionar todos

UPDATE JPESTOQUE
LEFT JOIN JPITEM ON JPITEM.IDPRODUTO = JPESTOQUE.ESPRODUTO
SET
JPITEM.IEQTD1 = IEQTD1 + IF( JPESTOQUE.ESNUMDEP BETWEEN '2' AND '9', 0, JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 ) ),
JPITEM.IEQTD2 = IEQTD2 + IF( JPESTOQUE.ESNUMDEP = '2', JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 ), 0 ),
JPITEM.IEQTD3 = IEQTD3 + IF( JPESTOQUE.ESNUMDEP = '3', JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 ), 0 ),
JPITEM.IEQTD4 = IEQTD4 + IF( JPESTOQUE.ESNUMDEP = '4', JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 ), 0 ),
JPITEM.IEQTD5 = IEQTD5 + IF( JPESTOQUE.ESNUMDEP = '5', JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 ), 0 ),
JPITEM.IEQTD6 = IEQTD6 + IF( JPESTOQUE.ESNUMDEP = '6', JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 ), 0 ),
JPITEM.IEQTD7 = IEQTD7 + IF( JPESTOQUE.ESNUMDEP = '7', JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 ), 0 ),
JPITEM.IEQTD8 = IEQTD8 + IF( JPESTOQUE.ESNUMDEP = '8', JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 ), 0 ),
JPITEM.IEQTD9 = IEQTD9 + IF( JPESTOQUE.ESNUMDEP = '9', JPESTOQUE.ESQTDE * IF( JPESTOQUE.ESTIPLAN = '1', 1, -1 ), 0 )
WHERE IDESTOQUE = 1

Mensagem por **JoséQuintas** » 25 Jun 2020 13:44

Segundo teste, ficou um pouco mais "legível", evitando erro de digitação:

Código: Selecionar todos

UPDATE JPITEM
LEFT JOIN 
   ( SELECT ESPRODUTO, ESNUMDEP, ESQTDE * IF( ESTIPLAN = '1', 1, -1 ) AS SOMA
     FROM JPESTOQUE WHERE IDESTOQUE = 1 ) AS A
   ON A.ESPRODUTO = JPITEM.IDPRODUTO
SET
JPITEM.IEQTD1 = IEQTD1 + IF( A.ESNUMDEP BETWEEN '2' AND '9', 0, A.SOMA ),
JPITEM.IEQTD2 = IEQTD2 + IF( A.ESNUMDEP = '2', A.SOMA, 0 ),
JPITEM.IEQTD3 = IEQTD3 + IF( A.ESNUMDEP = '3', A.SOMA, 0 ),
JPITEM.IEQTD4 = IEQTD4 + IF( A.ESNUMDEP = '4', A.SOMA, 0 ),
JPITEM.IEQTD5 = IEQTD5 + IF( A.ESNUMDEP = '5', A.SOMA, 0 ),
JPITEM.IEQTD6 = IEQTD6 + IF( A.ESNUMDEP = '6', A.SOMA, 0 ),
JPITEM.IEQTD7 = IEQTD7 + IF( A.ESNUMDEP = '7', A.SOMA, 0 ),
JPITEM.IEQTD8 = IEQTD8 + IF( A.ESNUMDEP = '8', A.SOMA, 0 ),
JPITEM.IEQTD9 = IEQTD9 + IF( A.ESNUMDEP = '9', A.SOMA, 0 )
WHERE JPITEM.IDPRODUTO = A.ESPRODUTO

O MySQL otimiza, não gravando aonde o valor não se altera, mas... queria um comando mais simples que esse.

Mensagem por **JoséQuintas** » 26 Jun 2020 11:26

Deixei assim:

Código: Selecionar todos

STATIC FUNCTION UpdateJPESTOQUE( nIdEstoque, nSomaTira )

   LOCAL cCampo
   LOCAL cnSQL := ADOClass():New( AppConexao() )

   WITH OBJECT cnSQL
      :cSQL := "UPDATE JPITEM" + ;
         " LEFT JOIN" + ;
            " ( SELECT ESPRODUTO, ESNUMDEP, ESQTDE * IF( ESTIPLAN = '1', 1, -1 ) * " + NumberSQL( nSomaTira ) + " AS SOMA" + ;
               " FROM JPESTOQUE WHERE IDESTOQUE = " + NumberSQL( nIdEstoque ) + " ) AS A" + ;
            " ON A.ESPRODUTO = JPITEM.IDPRODUTO" + ;
         " SET" + ;
            " JPITEM.IEQTD1 = IEQTD1 + IF( A.ESNUMDEP BETWEEN '2' AND '9', 0, A.SOMA )," + ;
            " JPITEM.IEQTD2 = IEQTD2 + IF( A.ESNUMDEP = '2', A.SOMA, 0 )," + ;
            " JPITEM.IEQTD3 = IEQTD3 + IF( A.ESNUMDEP = '3', A.SOMA, 0 )," + ;
            " JPITEM.IEQTD4 = IEQTD4 + IF( A.ESNUMDEP = '4', A.SOMA, 0 )," + ;
            " JPITEM.IEQTD5 = IEQTD5 + IF( A.ESNUMDEP = '5', A.SOMA, 0 )," + ;
            " JPITEM.IEQTD6 = IEQTD6 + IF( A.ESNUMDEP = '6', A.SOMA, 0 )," + ;
            " JPITEM.IEQTD7 = IEQTD7 + IF( A.ESNUMDEP = '7', A.SOMA, 0 )," + ;
            " JPITEM.IEQTD8 = IEQTD8 + IF( A.ESNUMDEP = '8', A.SOMA, 0 )," + ;
            " JPITEM.IEQTD9 = IEQTD9 + IF( A.ESNUMDEP = '9', A.SOMA, 0 )" + ;
         " WHERE JPITEM.IDPRODUTO = A.ESPRODUTO"
      :Execute()
      :cSQL := "SELECT * FROM JPITEM WHERE IDPRODUTO = ( SELECT ESPRODUTO FROM JPESTOQUE WHERE IDESTOQUE = " + NumberSQL( nIdEstoque ) + " )"
      :Execute()
      :QueryCreate()
      FOR EACH cCampo IN { "IEQTD1", "IEQTD2", "IEQTD3", "IEQTD4", "IEQTD5", "IEQTD6", "IEQTD7", "IEQTD8", "IEQTD9" }
         :QueryAdd( cCampo, :Number( cCampo ) )
      NEXT
      jpitem->( Encontra( StrZero( :Number( "IDPRODUTO" ), 6 ), "jpitem", "item" ) )
      jpitem->( :DBFQueryExecuteUpdate( "JPITEM" ) )
      :CloseRecordset()
   ENDWITH

Acho que fica mais eficiente deixar por conta do MySQL, do que por batch pelo programa, mesmo que o comando fique maior.
Quanto ao DBF... é só uma cópia deixando de ser importante.