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
eduardo
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 56
Registrado em: Ter, 10 Jul 2007 3:24 pm
Localização: sp
Sempre há algo a aprender....

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:

Selecionar tudo

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
rogenaro
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Sex, 30 Mar 2007 7:26 pm
Localização: Londrina - PR
Rafael O. Genaro

A forma "simples e rápida" seria jogar a condição

Selecionar tudo

and trunc(q.HIST_DATA)  BETWEEN trunc(ent.data)---??? 
                                         AND trunc(sai.data)---???
para o where mais externo:

Selecionar tudo

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:

Selecionar tudo

  (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.
eduardo
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 56
Registrado em: Ter, 10 Jul 2007 3:24 pm
Localização: sp
Sempre há algo a aprender....

obrigado, funcionou
Avatar do usuário
fabricio.silva
Rank: Programador Pleno
Rank: Programador Pleno
Mensagens: 34
Registrado em: Seg, 12 Mar 2007 7:07 am
Localização: Catanduva/SP
Fabricio M. Silva
Oracle Certified Associate
------------------------------
Catanduva - SP

Olá,

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

Selecionar tudo

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,
Responder
  • Informação
  • Quem está online

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