Complex Query, using queries as tables

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

Poston Thu, 28 Feb 2008 4:06 pm

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 q I can't reference a column from a table in another. below is the query:
Code: 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 marks; I need the third query uses 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 ... anyone have any suggestions to fix this?

I thank you
eduardo
Location: sp

Sempre há algo a aprender....

Poston Thu, 28 Feb 2008 7:16 pm

The form \"quick and simple\" would play the condition
Code: Select all
and trunc(q.HIST_DATA)  BETWEEN trunc(ent.data)---???
                                         AND trunc(sai.data)---???
to the where more external:
Code: 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 hist_data field in the subquery \"med\", and should stop doing the SUM and group by her, to perform these operations in an outer query.

This, of course, whereas the number of filtered records for this condition is small, otherwise the query may be much more time consuming.

What I constumo do in these situations is to perform a \"super select\", performing the join between the tables involved and returning the fields without any agupradora function (sum, avg, min, max, count, etc).
So I play this query in a query more foreign and realize the grouping of data in this query.
This is the form that brings best results usually ... Although there may be other better methods.

A third option would be to duplicate the query to fetch the value of ent. date and Nos date, getting something like:
Code: 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 ultimately is far less readable and generally slower than the method you said before.

I hope I helped.
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Thu, 06 Mar 2008 1:41 pm

Thanks, it worked
eduardo
Location: sp

Sempre há algo a aprender....

Poston Wed, 12 Mar 2008 10:33 am

Hello, an alternative to improve the code would be to use the WITH CLAUSE, e.g.:
Code: 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);
important: this feature is available as of version 9i.

Abs,
fabricio.silva
Location: Catanduva/SP

Fabricio M. Silva
Oracle Certified Associate
------------------------------
Catanduva - SP



Return to SQL

Who is online

Users browsing this forum: No registered users and 4 guests