Help-Error

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Thu, 03 Jan 2008 4:15 pm

Presents this error in procedure-I don't know what else can be ERROR at line 1: ORA-00979: not a GROUP BY expression ORA-06512: at \"FINEMPDBS.PRC_EMPRESTIMO_GF \", line 13 ORA-06512: at line 1 code:
Code: Select all
CREATE OR REPLACE PROCEDURE prc_emprestimo_gf

IS
   v_con_num_est      NUMBER (14, 4);
   v_data             NUMBER (8);
   v_principal_pago   NUMBER (14, 4);
   v_juros_pago       NUMBER (14, 4);
   v_mora_paga        NUMBER (14, 4);
   v_iof_pago         NUMBER (14, 4);
   v_multa_paga       NUMBER (14, 4);
   v_arquivo          UTL_FILE.file_type;
BEGIN
   SELECT   c.con_num_est,
            TO_CHAR (t.pgt_dat, 'dd/mm/yyyy'),
            SUM (t.ppcvalpriantpgt - t.ppcvalpripospgt),
            SUM (t.ppcvaljurantpgt - t.ppcvaljurpospgt),
            SUM (t.ppcvalmorantpgt - t.ppcvalmorpospgt),
            SUM (t.ppcvaliofantpgt - t.ppcvaliofpospgt),
            SUM (t.ppcvalmulantpgt - t.ppcvalmulpospgt)
       INTO v_con_num_est,
      v_data,
      v_principal_pago,
      v_juros_pago,
      v_mora_paga,
      v_iof_pago,
      v_multa_paga           
       from   emptblppc t, tb_con c, tb_pgt p
      WHERE t.con_num = c.con_num
        AND t.ppcdthalt = c.con_dat_alt
        AND t.con_num = p.con_num
        AND t.ppcdthalt = p.pgt_dat_alt
        AND t.pgt_dat = p.pgt_dat
        AND t.ppcdthalt = TO_DATE ('01-01-1960','DD-MM-YYYY')
        AND p.DATA = TO_DATE ('23-12-2007','DD-MM-YYYY')
   GROUP BY c.con_num_est, TO_DATE (t.pgt_dat, 'dd/mm/yyyy');

   v_arquivo := UTL_FILE.fopen ('/backup/CPMF', 'EMPRESTIMO_GF.CSV', 'W');
   UTL_FILE.put_line (v_arquivo, 'v_con_num_est    : '||NVL (v_con_num_est, 0));
   UTL_FILE.put_line (v_arquivo, 'v_data           : '||NVL (v_data, 0));
   UTL_FILE.put_line (v_arquivo, 'v_principal_pago : '||NVL (v_principal_pago, 0));
   UTL_FILE.put_line (v_arquivo, 'v_juros_pago     : '||NVL (v_juros_pago, 0));
   UTL_FILE.put_line (v_arquivo, 'v_mora_paga      : '||NVL (v_mora_paga, 0));
   UTL_FILE.put_line (v_arquivo, 'v_iof_pago       : '||NVL (v_iof_pago, 0));
   UTL_FILE.put_line (v_arquivo, 'v_multa_paga     : '||NVL (v_multa_paga, 0));
   UTL_FILE.fclose   (v_arquivo);
END;
mariogus
Location: Porto Alegre - RS

Poston Thu, 03 Jan 2008 4:22 pm

This problem is because of the difference in your select, at the beginning of his você uses the TO_CHAR and group by você uses the TO_DATE, you could change the group by, leaving with the TO_CHAR too.

[] 's
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Thu, 03 Jan 2008 4:29 pm

Now, appears: error on line 1: ORA-01403: no data fo ORA-06512: at \"FINEMP
mariogus
Location: Porto Alegre - RS

Poston Thu, 03 Jan 2008 4:30 pm

* Error at line 1: ORA-01403: no data found ORA-06512: at \"FINEMPDBS.PRC_EMPRESTIMO_GF \", line 13 ORA-06512: at line 1
mariogus
Location: Porto Alegre - RS

Poston Thu, 03 Jan 2008 4:43 pm

This message is displayed because your query found no record of any.

Has a detail there. in your query you're assigning the value of the select directly on the variables through the \"INTO\" .... then you gotta be careful in 2 situations, the first is in this case where found no record, and in the second case is the possibility of finding more than one record, these two forms will give error in your procedure. I will put below the ways that you can treat these 2 errors.

Code: Select all
BEGIN
   SELECT ...
     INTO ...
    WHERE ...
EXCEPTION
   WHEN NO_DATA_FOUND THEN   -- vai cair aqui caso não tenha encontrado nenhum registro
      dbms_output.put_line('não encontrou nenhum registro.');
   WHEN TOO_MANY_ROWS THEN   -- vai cair aqui caso tenha encontrado mais de um registro
      dbms_output.put_line('Encontrou mais de um registro. ');
   WHEN OTHERS THEN          -- caso não seja nenhum dos erros acima vai cair aqui...
      dbms_output.put_line('Erro desconhecido : '||SQLERRM);
END;


[]'s
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Fri, 04 Jan 2008 7:53 am

Good day mariogus, Td good with you?

So. .. check the receipt of também v_data variable, if it is receiving 8 or 10 characters ..!!

Any things, posts here on the forum!

Abs Norry
Hahu
Location: São Paulo

O mundo gira muito!!



Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests