Query complex, using queries like tables

[eduardo]

Hello everyone, I have a problem and I hope someone can help me;

I need to calculate an average using 3 queries as individual tables;

The problem is that I can not reference a column of a table inside another. Below is Query:

Select all

 
create or replace view v_giro_financeiro as 
select  (sum(nvl(entrada,0)) + sum(nvl(saida,0))/2) 
        /sum(med.media) media, 
        trunc(ent.data) data, 
        trunc(sai.data), 
        ent.Cliente, 
        ent.Site 
         
 from ( 
        Select (sum(nvl(r.gew, (wp.gew / wp.mng_ist) * r.mng_ist) +  
                  (nvl(a.gew_tara,0) * r.mng_ist)) / 1000) Entrada , 
                  trunc(w.datum_we) data, 
                  w.id_klient       Cliente, 
                  wp.lager          Site 
         from  we         w  ,  
                 wepos      wp ,  
                 ruestdaten r  ,  
                 artikel    a  ,   
                 klienten   k 
                 where w.lager = Nvl( Null,w.lager) 
                 and   w.id_klient = nvl( Null, w.id_klient)  
                 and   w.typ_we in ('LI','KR') 
                 and   wp.lager = w.lager 
                 and   wp.nr_we = w.nr_we 
                 and   r.lager = wp.lager 
                 and   wp.lager = '1' 
                 and   r.nr_we = wp.nr_we 
                 and   r.nr_we_pos = wp.nr_we_pos 
                 and   a.id_klient = wp.id_klient_artikel 
                 and   a.id_artikel = wp.id_artikel 
                 and   w.stat = '90' 
                 and   wp.stat = '90' 
                 and   r.stat = '90' 
                 and   k.id_klient = w.id_klient 
                 and   w.id_klient = '000514' 
         group by trunc(w.datum_we), 
                       w.id_klient, 
                       wp.lager) ent, 
 
(select (sum(p.gew + (nvl(a.gew_tara, 0) * p.mng))/1000) Saida, 
           trunc(af.datum_lief)  data, 
           af.id_klient          Cliente, 
           af.lager              Site 
   from auftraege af, 
           packpos   p, 
           pickauf   f, 
           artikel   a, 
           klienten  k 
 where af.lager = Nvl( Null, af.lager) 
     and af.id_klient = NVL( Null, af.id_klient) 
     and af.stat = '95' 
     and af.art_auf != 'AJUS' 
     and p.lager = af.lager 
     and af.lager = '1' 
     and p.id_klient = af.id_klient 
     and p.nr_auf = af.nr_auf 
     and p.nr_auf_ta = af.nr_auf_ta 
     and p.id_klient_artikel = a.id_klient 
     and p.id_artikel = a.id_artikel 
     and p.lager = f.lager 
     and p.id_klient = f.id_klient_auf 
     and p.id_klient = '000514' 
     and p.nr_auf = f.nr_auf 
     and p.nr_auf_ta = f.nr_auf_ta 
     and p.nr_auf_pos = f.nr_auf_pos 
     and p.nr_pick = f.nr_pick 
     and k.id_klient = af.id_klient 
Group By af.id_klient , 
         trunc(af.datum_lief), 
         af.lager)sai, 
(select avg(media) media, 
          cliente, 
          site 
 from  
  (select q.id_klient cliente, 
          q.lager site, 
          q.hist_data ,  
          sum(((mng_frei+mng_res_auf) * a.gew_tara) +             
          q.gew_brutto_le_1) / 1000 media 
  from quanten_history q, 
          artikel a 
where q.id_klient = a.id_klient 
   and a.id_klient = '000514' 
   and q.lager = '1' 
   and q.id_artikel = a.id_artikel 
   and trunc(q.HIST_DATA)  BETWEEN trunc(ent.data)---??? 
                                         AND trunc(sai.data)---??? 
group by q.id_klient, 
         q.lager, 
         q.hist_data) 
group by cliente, 
         site )med          
where ent.cliente = sai.cliente 
  and sai.cliente = med.cliente 
  and ent.site = sai.site 
  and sai.site = med.site 
group by trunc(ent.data), 
         trunc(sai.data), 
         ent.Cliente, 
         ent.Site 
          
 
 

The problem is where I marked with question; I need the third query to use data from the first and second column and Oracle does not recognize the fields of other queries.

Unfortunately I need to do this in a simple SELECT command ...

Does anyone have any suggestions to solve this?

Thanks in advance

[rogenaro]

The "simple and fast" form would play the condition

Select all

 
and trunc(q.HIST_DATA)  BETWEEN trunc(ent.data)---???  
                                         AND trunc(sai.data)---??? 

for the outermost WHERE:

Select all

 
where ent.cliente = sai.cliente  
and   sai.cliente = med.cliente  
and   ent.site = sai.site  
and   sai.site = med.site 
and   med.hist_data between trunc(ent.data) and trunc(sai.data) 

in this case, You will need to return the Histame field in the subquery "Med", and you should stop doing the Sum and Group By on it to perform these operations in a more external query.

This is clear, considering that the number of records filtered by this condition is small, otherwise query may be much longer.

What I Converse Do in these situations is to perform a "Super Select", performing Join among all the tables involved and returning the fields without any Agproader (Sum, AVG, MIN, MAX, Count, etc.).
Then I play this query in a more external query and perform the data grouping on this external query.
This is the way that brings me better results usually ... although there may be other better methods.

A third option would be to duplicate the query to seek the value of ent. Data and Sai.Data, getting something like:

Select all

 
  (select q.id_klient cliente,  
          q.lager site,  
          q.hist_data ,  
          sum(((mng_frei+mng_res_auf) * a.gew_tara) +             
          q.gew_brutto_le_1) / 1000 media  
  from quanten_history q,  
          artikel a  
where q.id_klient = a.id_klient  
   and a.id_klient = '000514'  
   and q.lager = '1'  
   and q.id_artikel = a.id_artikel  
   and trunc(q.HIST_DATA)   
BETWEEN  
(select ... from ... where ....) 
AND  
(select ... from ... where ....) 

That at the end is much less readable and usually slower than the method I said before.

I hope I have helped.

[eduardo]

Thanks, it worked.

[fabricio.silva]

Hello,

An alternative to improve code would be the use of with clause, eg:

Select all

with dept_costs as (select department_name, sum(salary) as dept_total 
                    from   hr.employees, hr.departments 
                    where  employees.department_id = departments.department_id 
                    group by department_name), 
     avg_cost as (select sum(dept_total)/count(*) as dept_avg 
                    from dept_costs) 
select * 
from   dept_costs 
where  dept_total > (select * from avg_cost);

: This feature is available from version 9i.

abs,