CODEWITHSUNDEEP
sqloracle-database
charles
charles

Reputation: 297

four queries in one result - oracle

I have four queries.

1.result: Count() | Nazev

  1. result: Count() | Nazev

  2. result: Ode_dne_včetně | Do_dne_včetně | Nazev_organizace | Pocet

  3. result: Nazev | Create_uzivatel | create_cas

I want to have only one result after one click. In this way:

Count() | Nazev | Count() | Nazev | Ode_dne_včetně | Do_dne_včetně | Nazev_organizace | Pocet | Nazev | Create_uzivatel | create_cas

Is it possible?

--first

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

--second

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

--third

    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 ;

--fourth

    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

Upvotes: 1

Views: 81

Answers (3)

psaraj12
psaraj12

Reputation: 5072

You can use subquery refactoring to achieve this.

Edit1:- subjekt.nazev is the relationship between the four queries then you can add WHERE first_qry.nazev=second_qry.nazev and similar relationship with remaining queries.

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

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

third_qry as ( 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),

fourth_qry as (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)

select distinct a.*,b.*,c.*,d.* 
from first_qry a ,second_qry b,third_qry c,fourth_qry d

Upvotes: 2

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

All your queries contain column nazev which is grouping and order key so I assumed that this is the field joining results. If this is so, then you can use follwoing SQL. If this is not what you wanted then please edit question, attach some data, table definitions, preferably as SQL Fiddle and precisely explain your request.

SQLFiddle

with q1 as (select count(*) cnt, subjekt.nazev
  from osoba,subjekt where osoba.ID_PATRI_DO=subjekt.ID 
  group by subjekt.nazev ),
q2 as (select count(*) cnt, subjekt.nazev 
  from ZADAVACI_POSTUP, subjekt 
  where ZADAVACI_POSTUP.id_zadavatel=subjekt.ID 
  group by subjekt.nazev ),
q3 as (select max(trunc(sysdate)-6) ode_dne_vcetne, 
    max(trunc(sysdate)) do_dne_vcetne, nazev_organizace nazev, 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) )
  group by nazev_organizace),
q4 as (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') )
select nazev, q1.cnt cnt1, q2.cnt cnt2, q3.ode_dne_vcetne, q3.do_dne_vcetne, 
    q3.pocet, q4.create_uzivatel, q4.create_cas
  from q1 left join q2 using (nazev) left join q3 using (nazev) left join q4 using (nazev)
  order by nazev, create_cas

Output for sample data:

NAZEV      CNT1 CNT2 ODE_DNE_VCETNE DO_DNE_VCETNE POCET CREATE_UZIVATEL CREATE_CAS
---------- ---- ---- -------------- ------------- ----- --------------- ----------
SUBJEKT1      1    1                                                  1 2015-03-20 
SUBJEKT2      2    1                                                  1 2015-03-20

Upvotes: 0

kevinskio
kevinskio

Reputation: 4551

Yesterday you asked a similar question and I answered it with this answer.

You can use the same method of using Union or Union all and just select null for each column where you do not have a result

select count(*) AS subjekt_count,subjekt.nazev ,null,null,null,null,null,null
--null columns represent the results from the other queries
from osoba,subjekt 
where  osoba.ID_PATRI_DO=subjekt.ID
group by subjekt.nazev  
UNION ALL
select null, null,count(*) AS subjekt_nazev_count,subjekt.nazev,null,null,null,null 
from ZADAVACI_POSTUP,subjekt where
ZADAVACI_POSTUP.id_zadavatel=subjekt.ID 
group by subjekt.nazev 
---and so on

There are other methods using a WITH statement but you need a common key between the statements and I am not clear on whether your queries are four exclusive queries to the same tables or four overlapping queries. Do you expect duplicates in the results?

Upvotes: 0

Related Questions