CODEWITHSUNDEEP
sqloracle-databasegroup-bysubquery
Some One
Some One

Reputation: 31

Why does the subquery throw errors in a group by query?

Ok, I will try a 2nd attempt because the first one was not that intelligent

My Query:

SELECT distinct  kg.datum,
   sum(case when leis.code = 'Oph3001' then leis.anzahl END) as leis3001,
   sum(case when leis.code = 'Oph3003' then leis.anzahl END) as leis3003,
   (select nvl((select to_date(KG2.KURZTEXT, 'dd.mm.yyyy')from kg_eintraege kg2 where kg2.kgtitel_nr = 1003350007 and kg.fall_nr = kg2.fall_nr and kg.patient_nr = kg2.patient_nr and kg.kg_id = kg2.kontext),'') from dual) as real_datum 
  FROM kg_eintraege kg
   INNER JOIN aufenthalte a
   ON kg.patient_nr = a.patient_nr
   and kg.fall_nr = a.fall_nr
   INNER JOIN MF_LEIS_DIAG_OP_MD leis 
   on leis.aufenthalte_nr = a.nr
   group by kg.datum, kg.kg_id
    order by /*real_datum*/ kg.datum desc

The following subquery is causing the problem:

(select nvl((select to_date(KG2.KURZTEXT, 'dd.mm.yyyy')from kg_eintraege kg2 where kg2.kgtitel_nr = 1003350007 and kg.fall_nr = kg2.fall_nr and kg.patient_nr = kg2.patient_nr and kg.kg_id = kg2.kontext),'') from dual)

Puting it into the group by expression throws the ORA-22818 error ("subquery not allowed here").

Not puting it throws the ORA-00979 error ("not a group by expression").

Can someone help me?

Upvotes: 0

Views: 71

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

You could use an outer join instead; something like:

SELECT distinct  kg.datum,
   sum(case when leis.code = 'Oph3001' then leis.anzahl END) as leis3001,
   sum(case when leis.code = 'Oph3003' then leis.anzahl END) as leis3003,
   to_date(KG2.KURZTEXT, 'dd.mm.yyyy') as real_datum 
FROM kg_eintraege kg
INNER JOIN aufenthalte a
ON kg.patient_nr = a.patient_nr
and kg.fall_nr = a.fall_nr
INNER JOIN MF_LEIS_DIAG_OP_MD leis 
on leis.aufenthalte_nr = a.nr
LEFT OUTER JOIN kg_eintraege kg2 
on kg2.kgtitel_nr = 1003350007
and kg.fall_nr = kg2.fall_nr
and kg.patient_nr = kg2.patient_nr
and kg.kg_id = kg2.kontext
group by kg.datum, kg.kg_id, to_date(KG2.KURZTEXT, 'dd.mm.yyyy')
order by /*real_datum*/ kg.datum desc

Upvotes: 2

hotfix
hotfix

Reputation: 3396

you can use a subquery 

select datum,
       sum(case when code = 'Oph3001' then anzahl END) as leis3001,
       sum(case when code = 'Oph3003' then anzahl END) as leis3003,
       real_datum
from (       
      SELECT distinct  kg.datum,
             leis.code,
             leis.anzahl,       
            (select nvl((select to_date(KG2.KURZTEXT, 'dd.mm.yyyy')from kg_eintraege kg2 where kg2.kgtitel_nr = 1003350007 and kg.fall_nr = kg2.fall_nr and kg.patient_nr = kg2.patient_nr and kg.kg_id = kg2.kontext),'') from dual) as real_datum 
        FROM kg_eintraege kg
        INNER JOIN aufenthalte a ON kg.patient_nr = a.patient_nr
                                and kg.fall_nr = a.fall_nr
        INNER JOIN MF_LEIS_DIAG_OP_MD leis on leis.aufenthalte_nr = a.nr
      )
group by datum, kg_id, real_datum
order by /*real_datum*/ kg.datum desc

Upvotes: 0

Related Questions