Classic Report and SQL Function

[Robson Costa]

Hello,

I created a classic report in my application via SQL function. Each day, it includes a new column in the report for the day. However, when I access the report, after the upgrade of another day, it gives error or-01403. This error stops when I enter Apex to edit the page and saved the new column to appear.

[dr_gori]

ORA-01403: On Data FOUN é porque alguma query não ta retornando linha em um bloco PLSQL.

How are you doing your job?

[Robson Costa]

The report is created with this PL / SQL command:

Select all

declare 
   comando varchar2(32767); 
   cursor datas is select distinct trunc(dt_lev) from table1 order by trunc(dt_lev); 
   dt_menor date; 
   v_data date; 
begin 
  comando := 'select campo1,'; 
  dt_menor:= to_date('27/02/2015','DD/MM/YYYY'); 
  open datas; 
  loop 
    fetch datas into v_data; 
    exit when datas%notfound; 
    if (v_data > dt_menor) then 
        comando := comando ||tot1||' '||chr(34)||to_char(v_data,'DD/MM/YYYY')||chr(34)||','; 
    end if; 
   end loop; 
   comando := rtrim(comando,','); 
   comando := comando ||' from table1 group by campo1 order by campo1'; 
   close datas; 
   return(comando); 
end;

and the table is in this way:

Select all

Campo1   09/03/2015  10/03/2015 
    X               77              88 
    y                22             33

And so successively, at the end of the day there is a job that inserts the refraining data a day. Then after that, the table should be:

Select all

Campo1   09/03/2015  10/03/2015 11/03/2015 
    X               77              88                99 
    y                22             33                 99

But it is not what happens.

[dr_gori]

He puts us an example of Query that he is riding.
I think the problem is the creation of Query.

From what I have seen, it is something like this:

Select all

select campo1,   CAMPO1   "09/03/2015",   CAMPO2 "10/03/2015",   CAMPO3 "11/03/2015" 
from table1 group by campo1 order by campo1

Where this field1 comes from tot1, that we do not You know what you can have.

It's strange, because normally with GROUP BY you need to have a SUM there for you to get your result. And I'm not seeing it.

[Robson Costa]

The correct PL / SQL would be this:

Select all

declare 
   comando varchar2(32767); 
   cursor datas is select distinct trunc(dt_lev) from table1 order by trunc(dt_lev); 
   dt_menor date; 
   v_data date; 
begin 
  comando := 'select campo1,'; 
  dt_menor:= to_date('27/02/2015','DD/MM/YYYY'); 
  open datas; 
  loop 
    fetch datas into v_data; 
    exit when datas%notfound; 
    if (v_data > dt_menor) then 
        comando := comando ||fc1(campo1,v_data)||' '||chr(34)||to_char(v_data,'DD/MM/YYYY')||chr(34)||','; 
    end if; 
   end loop; 
   comando := rtrim(comando,','); 
   comando := comando ||' from table1 group by campo1 order by campo1'; 
   close datas; 
   return(comando); 
end;

The query produced would be:

Select all

select campo1, fc1(campo1,'09/03/2015') "09/03/2015", fc1(campo1,'10/03/2015') "10/03/2015", fc1(campo1,'11/03/2015') "11/03/2015" 
from table1 group by campo1 order by campo1

[dr_gori]

Well, in a group by, all fields should have a sum, avg, min, max, etc. function. (less those in Group BY).

See:

Select all

SQL> select 
  2    code_combination_id, chart_of_accounts_id 
  3  from gl_code_combinations 
  4  group by code_combination_id 
  5  / 
  
select 
  code_combination_id, chart_of_accounts_id 
from gl_code_combinations 
group by code_combination_id 
  
ORA-00979: not a GROUP BY expression 
  
SQL> 

Apex must be generating some error thus internally.

[Robson Costa]

Group by works. The problem happens when it is a new date is included.

I took the group by and put Distinct

[Robson Costa]

The error continues, it was not the issue of Group By or Distinct.

[Renato Menezes Viana]

Personnel:

Select all

 
select 
  code_combination_id, chart_of_accounts_id 
from gl_code_combinations 
group by code_combination_id 
 
ORA-00979: not a GROUP BY expression

is missing in the group by the chart_of_accounts_id column.

Explanatory error in the link below:


By the way you will also have to dynamically mount the V_Data columns that you aggregate in

Select all

comando := comando ||' from table1 group by campo1 order by campo1';

]

I did not quite understand, but it seems like in the clausula group by and order by the field1 will have to exist the other campon originated by the mounted columns of v_data.

ABS, Renato