Query Complexa, usando queries como tabelas

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

Mensagemem Qui, 28 Fev 2008 4:06 pm

Olá a todos, eu estou com um problema e espero q alguém possa me ajudar;

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:
Código: Selecionar todos
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
         




O problema está onde marquei com interrogação; eu preciso na terceira consulta usar dados da primeira e segunda coluna e o oracle não reconhece os campos das outras queries.

Infelizmente eu preciso fazer isso num comando select simples...

alguém tem alguma sugestão para resolver isso?

desde já agradeço
eduardo
Localização: sp

Sempre há algo a aprender....

Mensagemem Qui, 28 Fev 2008 7:16 pm

A forma "simples e rápida" seria jogar a condição
Código: Selecionar todos
and trunc(q.HIST_DATA)  BETWEEN trunc(ent.data)---???
                                         AND trunc(sai.data)---???


para o where mais externo:
Código: Selecionar todos
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)


Neste caso, você precisará retornar o campo hist_data na subquery "med", e deverá deixar de fazer o SUM e group by nela, para realizar estas operações em uma query mais externa.

Isto, é claro, considerando que o número de registros filtrados por esta condição seja pequeno, caso contrário a query poderá ficar bem mais demorada.

O que eu constumo fazer nestas situações é realizar um "super select", realizando o join entre todas as tabelas envolvidas e retornando os campos sem nenhuma função agupradora (sum, avg, min, max, count, etc).
Então eu jogo esta query em uma query mais externa e realizo o agrupamento dos dados nesta query externa.
Esta é a forma que me traz melhores resultados geralmente... se bem que podem existir outros métodos melhores.

Uma terceira opção seria duplicar a consulta para buscar o valor de ent.data e sai.data, ficando algo do tipo:

Código: Selecionar todos
  (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 ....)


Que no final das contas é bem menos legível e geralmente mais lento que o método que disse antes.

Espero ter ajudado.
rogenaro
Localização: Londrina - PR

Rafael O. Genaro

Mensagemem Qui, 06 Mar 2008 1:41 pm

obrigado, funcionou
eduardo
Localização: sp

Sempre há algo a aprender....

Mensagemem Qua, 12 Mar 2008 10:33 am

Olá,

Uma alternativa para melhorar o código seria a utilização do WITH CLAUSE, p.ex:

Código: Selecionar todos
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);


Importante: Este recurso está disponível a partir da versão 9i.

Abs,
fabricio.silva
Localização: Catanduva/SP

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



Voltar para SQL

Quem está online

Usuários navegando neste fórum: Nenhum usuário registrado e 5 visitantes