Select multiple Selects???

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Wed, 23 Nov 2005 10:09 am

Hi Facebook friends, blame me, I don't know a lot about SQL ... I'm trying to retrieve some data to generate a tax file, and I couldn't put everything inside a select, because of some mistakes due to the GROUP BY I don't know, so I got a SELECT for each column like this:
Code: Select all
SELECT distinct nf.documentno, TO_CHAR(nf.dateissued, 'DDMM') AS data FROM compiere.c_notafiscal nf, compiere.c_notafiscalline nfl WHERE nf.documentno > 289 AND nf.documentno < 348 AND nf.c_notafiscal_id = nfl.c_notafiscal_id GROUP BY nf.documentno, nf.dateissued;
SELECT nf.documentno, SUM(nfl.price * nfl.qty) FROM compiere.c_notafiscal nf, compiere.c_notafiscalline nfl WHERE nf.documentno > 289 AND nf.documentno < 348 AND nf.c_notafiscal_id = nfl.c_notafiscal_id GROUP BY nf.documentno;
SELECT nf.documentno, SUM(nfl.price * nfl.qty + (nfl.price * nfl.qty * nfl.rateipi/100)) total FROM compiere.c_notafiscal nf, compiere.c_notafiscalline nfl WHERE nf.documentno > 289 AND nf.documentno < 348 AND nf.c_notafiscal_id = nfl.c_notafiscal_id GROUP BY nf.documentno;
SELECT nf.documentno nf, cfop.name cfop FROM compiere.c_notafiscal nf, compiere.c_cfop cfop WHERE nf.documentno > 289 AND nf.documentno < 348 AND cfop.c_cfop_id = nf.c_cfop_id GROUP BY documentno, cfop.name;
SELECT nf.documentno nf, bp.taxidbr1 cnpj FROM compiere.c_notafiscal nf, compiere.c_bpartner bp WHERE nf.documentno > 289 AND nf.documentno < 348 AND bp.c_bpartner_id = nf.c_bpartner_id GROUP BY documentno, taxidbr1;
SELECT nf.documentno nf, bp.taxidbr2 ie FROM compiere.c_notafiscal nf, compiere.c_bpartner bp WHERE nf.documentno > 289 AND nf.documentno < 348 AND bp.c_bpartner_id = nf.c_bpartner_id GROUP BY documentno, taxidbr2;
SELECT nf.documentno nf, bp.name ie FROM compiere.c_notafiscal nf, compiere.c_bpartner bp WHERE nf.documentno > 289 AND nf.documentno < 348 AND bp.c_bpartner_id = nf.c_bpartner_id GROUP BY documentno, bp.name;
SELECT nf.documentno, SUM(nfl.price * nfl.qty * (1+nfl.rateipi/100)) FROM compiere.c_notafiscal nf, compiere.c_notafiscalline nfl WHERE nf.documentno > 289 AND nf.documentno < 348 AND nf.c_notafiscal_id = nfl.c_notafiscal_id AND nfl.rateipi >= 1 GROUP BY nf.documentno;
SELECT nf.documentno, nfl.rateicms FROM compiere.c_notafiscal nf, compiere.c_notafiscalline nfl WHERE nf.documentno > 289 AND nf.documentno < 348 AND nf.c_notafiscal_id = nfl.c_notafiscal_id GROUP BY nf.documentno, nfl.rateicms;
SELECT nf.documentno, SUM(nfl.rateicms * nfl.price * nfl.qty * (1+nfl.rateipi/100)) FROM compiere.c_notafiscal nf, compiere.c_notafiscalline nfl WHERE nf.documentno > 289 AND nf.documentno < 348 AND nf.c_notafiscal_id = nfl.c_notafiscal_id AND nfl.rateipi > 0 AND nfl.rateicms > 0 GROUP BY nf.documentno;
SELECT nf.documentno, SUM(nfl.price * nfl.qty) valor FROM compiere.c_notafiscal nf, compiere.c_notafiscalline nfl WHERE nf.documentno > 289 AND nf.documentno < 348 AND nf.c_notafiscal_id = nfl.c_notafiscal_id AND nfl.rateicms = 0 GROUP BY nf.documentno;
SELECT nf.documentno, SUM(nfl.price * nfl.qty * (nfl.rateipi/100)) FROM compiere.c_notafiscal nf, compiere.c_notafiscalline nfl WHERE nf.documentno > 289 AND nf.documentno < 348 AND nf.c_notafiscal_id = nfl.c_notafiscal_id GROUP BY nf.documentno;
SELECT nf.documentno, SUM(nfl.price * nfl.qty) icms FROM compiere.c_notafiscal nf, compiere.c_notafiscalline nfl WHERE nf.documentno > 289 AND nf.documentno < 348 AND nf.c_notafiscal_id = nfl.c_notafiscal_id AND nfl.rateipi = 0 GROUP BY nf.documentno;
what I want now is raking all columns in a single query, because each select has 2 columns and one is in common to all (documentno) ... has how to do this???

Thanks to all
ralexsander
Location: SP

Poston Wed, 23 Nov 2005 10:24 am

Well, you can put each of your select in from clause, and then do an OUTER JOIN at documentno.

Something like that;

Code: Select all
select
  A.documentno
, x1.data
, x2.preco_qtd
etc....

from
   (aqui, você coloca um select que retorna todos documentno possíveis para a suas consulta )  A
,  (SELECT distinct nf.documentno, TO_CHAR(nf.dateissued, 'DDMM') AS data FROM compiere.c_notafiscal nf, compiere.c_notafiscalline nfl WHERE nf.documentno > 289 AND nf.documentno < 348 AND nf.c_notafiscal_id = nfl.c_notafiscal_id GROUP BY nf.documentno, nf.dateissued )    X1

,  (SELECT nf.documentno, SUM(nfl.price * nfl.qty) preco_qtd FROM compiere.c_notafiscal nf, compiere.c_notafiscalline nfl WHERE nf.documentno > 289 AND nf.documentno < 348 AND nf.c_notafiscal_id = nfl.c_notafiscal_id GROUP BY nf.documentno  )  X2

,  etc... 
where a.documentno = x1.documentno (+)
  and a.documentno = x2.documentno (+)
etc...
I think so you can do it. Tests there, like, going to increasing gradually to your table to see if the wheel right ...
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 22 Feb 2006 4:35 pm

Guys, it's cool there???
Good ... got the following query, which runs perfectly in plus:
Code: Select all
select distinct b.cd_disc,
       b.nm_abrev,
       (select sum(QT_QTDTOTAL)
         from f_estat_geral_aproveitamento c
        where c.aa_anomatr||c.ss_semematr = a.aa_anomatr||a.ss_semematr
         and c.id_idtfdisc = b.cd_identdisc) total_de_alunos,
       (select sum(QT_QTDTOTAL)
         from f_estat_geral_aproveitamento c
        where c.aa_anomatr||c.ss_semematr = a.aa_anomatr||a.ss_semematr
         and c.id_idtfdisc = b.cd_identdisc
         and c.cd_condaprv not in ('AP','SA')) Total_de_reprovados,
    trunc(((select sum(QT_QTDTOTAL)
         from f_estat_geral_aproveitamento c
        where c.aa_anomatr||c.ss_semematr = a.aa_anomatr||a.ss_semematr
         and c.id_idtfdisc = b.cd_identdisc
         and c.cd_condaprv not in ('AP','SA'))
         /
        (select sum(QT_QTDTOTAL)
         from f_estat_geral_aproveitamento c
        where c.aa_anomatr||c.ss_semematr = a.aa_anomatr||a.ss_semematr
         and c.id_idtfdisc = b.cd_identdisc))*100,2) Perc_de_reprovados
  from f_estat_geral_aproveitamento a,f_departamento d,
       f_disciplina b
where a.aa_anomatr||a.ss_semematr = 20052
   and a.id_idtfdisc = b.cd_identdisc
   and d.cd_dept = 'CI'
   and a.cd_dept = d.cd_dept

order by b.cd_disc
but as soon as I step pro Pl/SQL from Forms, gives error and doesn't make the query.
What am I doing wrong?
Hugs!
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Wed, 22 Feb 2006 5:09 pm

gives what error?
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 22 Feb 2006 5:12 pm

Gives an error right at the select the third parameter.
Here:
Code: Select all
select distinct b.cd_disc,
       b.nm_abrev,
       (select sum(QT_QTDTOTAL) -- Erro nesse select...
hugs!
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Wed, 22 Feb 2006 5:19 pm

What error? which version of forms? which version of the Bank?
All of this makes a difference because the PL/SQL forms PL/SQL differs from the Bank.

Look at this:
Code: Select all
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for Linux: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL>
I'm using version 10.1.0.2.0 to the PL/SQL in the Bank.
And in forms, I was in the wool \"About\" and saw that my version of PL/SQL from forms PL/SQL Version 8.0.6.0.0 (Production).

Is a big difference!
Most of the stuff works, but the NEW features may not.

I'vê had a case that the PL/SQL from forms does not accept in any way an ORDER BY at the end of a select very complex. In SQL * Plus and in packages of Bank, worked perfectly.

Probably, this is your case then ...:-
Last edited by dr_gori on Wed, 22 Feb 2006 5:31 pm, edited 1 time in total.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 22 Feb 2006 5:29 pm

Pootz ... now you left me scared ...
10 g Bank Foms: oops: 4.5
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Wed, 22 Feb 2006 5:30 pm

I'm sorry for you TOAD.. hehehe that must be what I quoted above.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 22 Feb 2006 5:40 pm

Yes Dr. .. Imagine each dragon that I don't have to kill that Forms ... imagine the tool you have and that I have to \"do\" ...: cry: Well ... the question I do is: and now? How can I do this Query Returns me the values. From what I understand, he does not accept subquery as a parameter.
Any ideas!?
Hugs!
And thank you once again!!
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Wed, 22 Feb 2006 5:59 pm

An idea is you create a VIEW in the Bank. From there you can access this query as if it were a table.

:-o
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Fri, 24 Feb 2006 11:22 am

Is Dr ... Did a routine that worked ....
I did a general cursor ... and with a for him, carrying the data based on a parameter.
Was like this:
Code: Select all
DECLARE

cursor X is
SELECT distinct
      (b.cd_identdisc) B_IDENTIDISC,b.cd_disc,
      (b.cd_disc||'#'||b.nm_abrev||'#' ) linha
  FROM  f_estat_geral_aproveitamento a,
       f_Disciplina b,
       f_departamento d
WHERE to_char(a.aa_anomatr)||to_char(a.ss_semematr) = :TXT_ANO||:TXT_SEM
   AND a.id_idtfdisc = b.cd_identdisc
   AND a.cd_dept = :CD_DEPT
   AND a.cd_dept = d.cd_dept
   order by b.cd_disc;

CURSOR TOTAL_ALUNO(p_identdisc f_disciplina.cd_identdisc%Type) IS
select sum(c.QT_QTDTOTAL)
       from f_estat_geral_aproveitamento c,f_departamento d,
       f_disciplina b
       where to_char(c.aa_anomatr)||to_char(c.ss_semematr) = :TXT_ANO||:TXT_SEM
       and c.id_idtfdisc = b.cd_identdisc
       and d.cd_dept = :CD_DEPT
       and c.cd_dept = d.cd_dept
       and b.cd_identdisc = p_identdisc;

CURSOR REPROVADOS(p_identdisc f_disciplina.cd_identdisc%Type) IS
select sum(QT_QTDTOTAL)
         from f_estat_geral_aproveitamento c,f_departamento d,
         f_disciplina b
        where to_char(c.aa_anomatr)||to_char(c.ss_semematr) = :TXT_ANO||:TXT_SEM
         and c.id_idtfdisc = b.cd_identdisc
         and c.cd_condaprv not in ('AP','SA')
         and d.cd_dept = :CD_DEPT
         and c.cd_dept = d.cd_dept
         and b.cd_identdisc = p_identdisc;
       

  v_texto_linha varchar2(250);
  v_total_alunos number;
  v_total_REPROVA number;
  v_PERC_REPROVA number;
  meuarquivo text_io.file_type;
 
  begin
   
   if  :cd_dept is null or :txt_ano is null or :txt_sem is null then
    mensagem('Todos os dados devem ser preenchidos!!', true);
   end if;
   
   if  :TXT_SEM NOT IN ('1','2') then
    mensagem('Semestre deve ser apenas 1 ou 2!!', true);
   end if;
   
   if  (to_date(:TXT_ANO,'yyyy') > round(sysdate,'yyyy')) or length(:TXT_ANO)<4 then
    mensagem('Ano deve ser menor ou igual ao atual!!', true);
   end if;

    set_application_property(cursor_style,'busy');
    meuarquivo := text_io.fopen('c:   emp'||:NM_DEPT||' - '||:TXT_SEM||:TXT_ANO||'.txt','W');

    for r in x loop
           
       
       open TOTAL_ALUNO(r.B_IDENTIDISC);
       fetch TOTAL_ALUNO into v_total_alunos;
       close Total_aluno;
     
       open REPROVADOS(r.B_IDENTIDISC);
       fetch REPROVADOS into v_total_REPROVA;
       close REPROVADOS;
       
       IF v_total_REPROVA is null THEN

          v_total_REPROVA := 0;

       END IF;


       v_PERC_REPROVA := TRUNC((v_total_REPROVA/v_total_alunos)*100,2);
       

       v_texto_linha := r.linha ||to_char(v_total_alunos)||'#';
       V_TEXTO_LINHA := V_TEXTO_LINHA ||to_char(v_total_REPROVA)||'#';
       V_TEXTO_LINHA := V_TEXTO_LINHA || to_char(v_PERC_REPROVA)||'%';
       text_io.put_line(meuarquivo,v_texto_linha);

    end loop;
    set_application_property(cursor_style,'default');
    text_io.fclose(meuarquivo);
    mensagem('Texto Gerado !!');
 
   exception
    when others then
      set_application_property(cursor_style,'default');
      text_io.fclose(meuarquivo);
      mensagem('Erro ao criar o arquivo !!',true);
 
end;
Is there ... If it helps someone, it looks great!

Hugs!

Toad
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek


  • See also
    Replies
    Views
    Last Post


Return to PL/SQL

Who is online

Users browsing this forum: No registered users and 9 guests