Defninir nova tabela sem Master Detail

[cruzi]

Olá !

   alguém por gentileza poderia me dar um help :

 

   Estou num dilema a dias com o seguinte senário: Com Lazarus 1.6 com ZQuery    e    SQLite 3.31.1
      Tenho uma tabela detalhes ,de poucos registros (100)
   que com o seguinte select eu tento fazer um  cálculo que é baseado em uma soma de uma coluna de um lote de registros (estou testando com GROUP BY).

Uso uma Query master e uma Query detalhes que estao ligadas por Master / detalhes id X idNC. Puxei um total, campos de data e numero de

Nota  da tabela master pelo metodo LEFT OUTER JOIN que me traz certinho e ainda traz valores que somo no SQL.
  Antes tinha feito uma miscelânea que alguns calculos foi direto no SELECT outros usei por metodo CalcField na tabela detalhe.
  Como modulo somente para digitação está perfeito , monta toda a massa de dados que preciso, mas agora nescessito de
  trabalhar com datas nesta base e o agravante é que existe uma dependência nesta forma que  me obriga a ter a tabela master
  ligada, porque ela que dá alguns valores totalizados (tot_custos) e liga a chave primaria quando na seleção .
  Estou tentando agora eliminar a relação da tabela master para que o SELECT na detalhes faça tudo com autonomia.

    SELECT  A.id,
            A.idNC,
            B.data,         AS dataFK,
            B.num_NC        AS num_nota_FK,
            nome_do_item,  
            qde,

            preco_un,
            tot_item,
            IFNULL(B.val1,0) + IFNULL(B.val2,0) + IFNULL(B.val3,0)    AS   tot_custos ,
            IFNULL((qde * preco_un),0)     AS   tot_item,   
            IFNULL((SUM(tot_item)),0)      AS   tot_colunaItem,
            (((IFNULL(tot_custos,0) / IFNULL(tot_colunaItem,0)) * 100)   *    (tot_item / 100))   AS  custo_item

            FROM tboperacoes    AS   A
            LEFT OUTER JOIN  tbnotacorret AS B ON (B.id  = A.idNC)
            GROUP BY A.idNC
 

       Obs. Usei IFNULL para precaver erro de entrada ainda nula ou divisao por zero.

             

       Onde id : Chave primaria Identificadora da tabela master que seve de cabeçalho e numeração e data da nota.
            idNC : Referencial na tabela detalhes.
            Alias A : Refere-se a tabela detalhes.
            Alias B : Refere-se a tabela master.

            A expressão do calculo já esta ok tambem, mas não apresenta todos os registros, só o primeiro de cada nota. Eu queria de todos.

   O PROBLEMA: quando retiro a ligação master/detale ela não varre todos os registros apresentando somente o primeiro de cada nota.
   No grid da tabela master rodo este select que esta em procedure Calculquery para cada registro selecionado na query master via um grid de edição.

  para poder prosseguir em pesquisas nestes resultados já conseguidos.
   já tentei com ROOLUP mas SQLite não tem isso só emulado assim mesmo não sei se é isso que faria efeito.
   Preciso eliminar o método master/detalhes das querys que coloquei via propriedades master fild/master source etc..  e utilizar tudo via SQL.

Agradeço muito a quem se envolver neste help, que poderia até servir de estudo de caso para os demais.

 

 

Editado 13 Abril, 2020 por cruzi

[cruzi]

Continuando:

Segue as duas tabelas para melhor entendimento,

                                         TBNOTA  (master)

id    num_nc             data             val1      val2     val3

1       0001           2020-05-17    10.0       1.0      3.0
2       9619           2020-04-01     0.09      0.57    0.0
3       0003           2020-03-15     7.36      1.13    0.0
AUTO                   

          

                                          TBOPERACAO   (detalhes)

 id                nome_item_             qde         preco_un         idNC

1                 ITEM-X1                      1.0            525.3             2
2                 ITEM-B2                      15.0         103.87           2
3                 ITEM-B2                      10.0         104.0             2
4                 ITEM-C3                       41.0        452.0             1
5                 ITEM-C3B                    15.0           98.8             1
Auto         

O seguinte select já bem simplificado que o anterior, baseado nas tabelas acima produz o resultado abaixo:

  SELECT   A.id, idNC, B.DATA AS data_FK, B.num_nc AS nota_FK, nome_do_item, qde, preco_un,  
                   (qde * preco_un)                 AS tot_item,
                   B.val1 + B.val2 + B.val3         AS  tot_custos,              
                   SUM(qde * preco_un )             AS tot_coluna_item,
                         -->CALCULO DO CUSTO DE CADA OPERACAO
                   (((B.val1 + B.val2 + B.val3)    /    IFNULL((SUM(qde * preco_un )),0) * 100  
                    *  ((qde * preco_un) / 100)))   AS   CUSTO_ITEM     
             
                     FROM tboperacao                  AS  A            
                     LEFT OUTER JOIN  tbnota AS B ON (B.id  = A.idNC)
    
                     GROUP BY idNC

id   idNC          data_FK     nota_FK       nome_do_item       qde    preco_un       tot_item    tot_custos     tot_coluna_item        CUSTO_ITEM

4       1          2020-05-17     0001                ITEM-C3              41.0        452.0        18532.0         14.0            20014.0                           12.96
1       2          2020-04-01     9619                ITEM-X1                 1.0        525.3             525.3         0.66              3123.35                           0.11

 

  O objetivo desta é apurar o custo proporcional de cada ítem  (CUSTO_ITEM)  que traz corretamente mas,     
         observe que  no resultado não aparece os outros ítens de detalhes de cada nota. Somente o primeiro de cada nota.

        Eu queria todos os outros registros de cada respectiva nota.

         Quando eu monto tudo isso no programa eu  consigo ver os outros registros de detalhes se eu selecionar manualmente um  ítem da tabela master, então aparece todos os registros pertinentes a da nota escolhida e todos os cálculos corretos, lembrando que no programa eu utilizo uma parte do cálculo como por ex. -->CALCULO DO CUSTO DE CADA OPERACAO  por  método CalcField da query QRYOPERACAO. O que eu precisava é que isso funcionasse apenas em um select , sem nenhuma linha de metodo CalcField.  Porque?  Porque agora com cada operacao de todas as notas eu terei que ter uma base de dados única para fazer select por datas ou mês e o campo CUSTO_ITEM já calculado é mandatório.    Acho que agora ficou mehor .     Mais uma vez agradeço muito  a quem se propuser me ajudar . Obrigado e aguardo.

        

Só para lembrar , eu utilizei GROUP BY neste selct somente para ilustrar e poder fazer os calculos,    no programa eu não usei isto , usei método de propriedade master / detalhes  que mostra somente uma nota por vêz quando seleciona ela.

Editado 18 Abril, 2020 por cruzi

[Juliana Tamizou]

Bom dia.

Caso tenha chego a uma solução, seria interessante se pudesse compartilhar com a comunidade.

Att.

[cruzi]

  Continuado...
  Após longa espera para o Help, e ao mesmo tempo por outras sugestões e orientações foi chegado a conclusão parcial que
  o melhor seria abandonar os métodos anteriores tentados como por ex.STORED PROCEDURES (SQLite não provê) ou mesmo o GROUP BY
  nos selects tentados (comandos SUM, GROUP BY e outros são de agrupamento)   neste caso não apresentaria
  um resultado desejado. Foi sugerido a construção de uma tabela temporaria  (Create TEMP tbtempor ....) sem ligação , e trabalhar
  postando em cada coluna de totais e cálculos diversos individualmente via uma procedure D.Pascal/L.pascal e fazer laços usando
  componentes de classes TQuerys nativas:

                               RECOMEÇO COM UM TESTE CURTO:

                               //Objetivo : fazer update de um resultado de uma soma anteriormente feita  na mesma tabela.
                               //tbresult é uma tabela normal criada e populada a partir de outra tabela matriz ,em tempo de execução.
                               //Tem 3 registros com ocorrencias de idNC = 27 na tabela tbresult
                               //Criei uma tabela temporaria so para armazenamento de valor de variáveis como por ex. somas.

procedure TformLISTA.calcula(Sender: TObject);
 Var   s: string;
      vof : integer;
begin

         Dtm.ZQryResult.sql.clear;
         Dtm.ZQryResult.sql.add('CREATE TEMP TABLE IF NOT EXISTS tbtempor(totitem FLOAT); ');
         Dtm.ZQryResult.ExecSQL;

 

     //Query  ja foi assignada com SLECT * from tbresult  , na propriedade SQL inicial do compon.

       with Dtm.ZQryLista do
       begin;

              Open ;
         
              sql.clear;
              sql.add('INSERT OR REPLACE INTO tbtempor (totitem) VALUES((SELECT SUM(tot_item) FROM tbresult WHERE idNC = :Param_numID));');
              ParamByName('Param_numID').Value := 27;
              ExecSQL;

              Dtm.ZQryResult.sql.clear;
              Dtm.ZQryResult.sql.add('UPDATE tbresult SET total = (SELECT totitem FROM tbtempor) WHERE idNC = :Param_numID ;');
              Dtm.ZQryResult.ParamByName('Param_numID').Value := 27;
              Dtm.ZQryResult.ExecSQL;
       end;
          
 end;//Proced
                                                      Até aqui funciona mas sigo agora com a construção do laço seguinte que dará o mecanismo de loop onde apresenta um problema

                                                    crítico .  Postarei em prox. análise.

      Obs.Sr ou sra Monitora , favor nao fechar o post até eu demonstrar o ponto de erro. Obrigado

  

 

[cruzi]

CONCLUINDO EM RESUMO :  Agora estou livre da relação Master / Detail  pelo menos neste módulo de apresentação dos dados.

   Obs : Com esta construção não foi mais nescessário a tabela tempor.   que serve de repositório de variáveis.

 

Dentro de uma procedure com ZQuery acionar o laço seguinte operando 2 query Nota e Auxiliar    com a mesma tabela no banco:

With Dtm do

begin

WHILE NOT ZQryNota.EOF   do                                //dece selecionando cada NOTA
  begin

       ZQryResultAux.First;
       with ZQryResultAux do
       begin;

                     //Posta os valores direto na tab fisica de resultado utilizando sub selects
              WHILE not EOF do
              begin
                          //SOMA  coluna de tot_item  e posta em totcoluna_item
                ZQryResult.sql.clear;
                ZQryResult.sql.add('UPDATE tbresult SET totcoluna = (SELECT SUM(tot_item)
                   FROM tbresult WHERE idNC = :Param_numID)  WHERE idNC= :Param_numID ; ');
                ZQryResult.ParamByName('Param_numID').AsString  := ZQryNota.FieldByName('id').asstring;
                ZQryResult.ExecSQL ;

 

                                      //CALCULA, POSTA CUSTO PROPORCIONAL DE CADA ITEM
                ZQryResult.sql.clear;
                ZQryResult.sql.add('UPDATE tbresult SET custo_item = '+
                '(SELECT ((((tot_custos) / tot_opers_liq * 100) * '+
                '(tot_item / 100))) '+
                'FROM tbresult WHERE  id_result = :ParamIDresult) WHERE id_result  = :ParamIDresult; ');
                ZQryResult.ParamByName('ParamIDresult').AsString  := ZQryResultAux.FieldByName('id_result').asstring;
                ZQryResult.ExecSQL ;

                              //Acima Usei o metodo Parametro das Querys para nao concatenar valores.

                ZQryResultAux.Next ;

              end; //EOF tabela auxiliar

 
       end;        //ZQryResultAux

    VZQryNota.Next;

  end;       //EOF notas
 end;        //Dtm
 

Solução simples para uma situação supostamente complexa.

Fica aí para quem precisar. Obrigado

 

 

 

[Juliana Tamizou]

Obrigado por reportar.

Fechando. Para novas dúvidas, criar um novo tópico.