Oracle - more queries in one result

Question

I've four different queries and I want to see all results in same statement(after one click). I have no idea. Is it possible?

1.query

    select count(*),subjekt.nazev from osoba,subjekt where   
    osoba.ID_PATRI_DO=subjekt.ID group by subjekt.nazev order by     
    subjekt.nazev;

2. query

   select count(*),subjekt.nazev from ZADAVACI_POSTUP,subjekt where
   ZADAVACI_POSTUP.id_zadavatel=subjekt.ID group by subjekt.nazev order by 
   subjekt.nazev;
   

3. query

   select    max(trunc(sysdate)-6) ode_dne_včetně, max(trunc(sysdate))  
   do_dne_včetně,nazev_organizace,count(*) pocet
   from(
   select to_char(t.popis) popis_typu,subj.nazev nazev_organizace,   
   u.username,u.nazev, a.datumzapisauditu   
   ,to_char(a.datumzapisauditu,'DD.MM.YYYY')  datum , a.id
   from d$caudit a
   join cuzivatel u on u.id= a.id_uzivatel
   
   join osoba os on u.id_osoba_bridge = os.id
   join t$subjekt subj on subj.id = os.id_patri_do
   left join d$caudittyp t on t.id=a.id_audittyp
   where datumzapisauditu between trunc(sysdate)-7  AND  trunc(sysdate)
   order by  a.datumzapisauditu desc
   )
   

   group by nazev_organizace order by nazev_organizace ;

4. query

   select  sb.nazev, lg.create_uzivatel, lg.create_cas from Aplikacni_log   lg 
   join zadavaci_postup zp on zp.id = lg.id_zp
   join subjekt sb on sb.id = zp.id_zadavatel
   where lg.create_cas > to_date('08.11.2014', 'DD.MM.YYYY')
   order by sb.nazev asc
   

Answer 1

You can get the results in one query by using the UNION operator. It does not lend itself to being cost efficient on large sets but does the job.

• all the queries must select the same number of columns of the same data type.
• you can select null if you have a unique column in a query
• it is also handy to select a string indicating the origin of the data
• the result set will only use the names of the columns of the first query

• ordering applies to the whole result set not individual queries

  select count(*) AS R_COUNT, 'OSABA' AS SOURCE,subjekt.nazev , null, null,null,null

  from osoba,subjekt

  where osoba.ID_PATRI_DO=subjekt.ID

  group by subjekt.nazev

  UNION

  select count(*),'ZADAVACI_POSTUP', subjekt.nazev , null, null,null,null

  from ZADAVACI_POSTUP,subjekt

  where ZADAVACI_POSTUP.id_zadavatel=subjekt.ID

  group by subjekt.nazev

  UNION

  select NULL,'Aplikacni_log', sb.nazev, lg.create_uzivatel, lg.create_cas,null,null

  from Aplikacni_log lg join zadavaci_postup zp on zp.id = lg.id_zp

  join subjekt sb on sb.id = zp.id_zadavatel

  where lg.create_cas > to_date('08.11.2014', 'DD.MM.YYYY')

  UNION

  select count(*) pocet,'SUBQUERY', do_dne_včetně,nazev_organizace,max(trunc(sysdate)-6) ode_dne_včetně, max(trunc(sysdate))

  from(

  select to_char(t.popis) popis_typu,subj.nazev nazev_organizace,

  u.username,u.nazev, a.datumzapisauditu

  ,to_char(a.datumzapisauditu,'DD.MM.YYYY') datum , a.id

  from d$caudit a

  join cuzivatel u on u.id= a.id_uzivatel

  join osoba os on u.id_osoba_bridge = os.id

  join t$subjekt subj on subj.id = os.id_patri_do

  left join d$caudittyp t on t.id=a.id_audittyp

  where datumzapisauditu between trunc(sysdate)-7 AND trunc(sysdate)

  order by a.datumzapisauditu desc )

  ;