eu preciso calcular uma média usando 3 queries como tabelas individuais ;
o problema é q não consigo referenciar uma coluna de uma tabela dentro de outra. abaixo segue a query:
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
Infelizmente eu preciso fazer isso num comando select simples...
alguém tem alguma sugestão para resolver isso?
desde já agradeço