Select auf Select (Subquery) ersetzt View

Anforderung:

Ermittlung aller Reports und die Anzahl der Anwender je Report, die seit dem 01.01.2007 von min. 3 verschiedenen Anwendern verwendet wurden.

1.) Mit diesem Select erhalte ich die verschiedenen Anwender mit zugehöriger Report-Nummer:

select distinct rep_anwender, rep_nr

from report_list

where rep_aenddat >= TO_DATE('01.01.2007', 'dd.mm.yyyy')

group by rep_nr, rep_anwender

Anwender Report

1 100

2 100

3 100

Mit diesem Select kann ich eine View erstellen:

CREATE OR REPLACE VIEW V_View_01 AS

select distinct rep_anwender, rep_nr

from report_list

where rep_aenddat >= TO_DATE('01.01.2007', 'dd.mm.yyyy')

group by rep_nr, rep_anwender

2.) Mit dieser View ermittle ich nun zur Report-Nummer die maximale Anzahl der verschiedenen Anwender:

select rep_nr, count (all rep_nr) as maxAnzahl

from V_View_01

group by rep_nr

Als View formuliert:

CREATE OR REPLACE VIEW V_View_02 AS

select rep_nr, count (all rep_nr) as maxAnzahl

from V_View_01

group by rep_nr

3.) Mit dem dritten Select erhalte ich mein gewünschtes Ergebnis:

select rep_nr, maxAnzahl

from V_View02

where maxAnzahl >= 3

Report maxAnzahl

100 3

-------------------------------------------------------------

In Oracle kann ich das nun in folgendem Statement zusammenfassen:

select rep_nr,  
       count (rep_nr) maxAnzahl

  from (  
    select rep_user, 
           rep_nr

      from report_list

     where rep_aenddat >= TO_DATE('01.01.2007', 'dd.mm.yyyy')

    group by rep_nr, 
             rep_anwender

  )

group by rep_nr

having count (rep_nr) >= 3

oder

select rep_nr, maxAnzahl

  from ( 
    select rep_nr, 
           count (all rep_nr) as maxAnzahl

      from ( 
        select distinct rep_anwender, 
               rep_nr

          from report_list

         where rep_aenddat >= TO_DATE('01.01.2007', 'dd.mm.yyyy')

        group by rep_nr, 
                 rep_anwender

      )

    group by rep_nr

  )

where maxAnzahl >= 3

Select innerhalb Selektion, Subselect

SELECT ALL VSZ_FLV_NR,

       VSZ_JAHR,

       VSZ_VKZF,

       VSZ_NAMETG,

       vsz_flaecheinhektar,

       vsz_name,

       vsz_anzahleigentuemer,

       --

       (SELECT sum (AKF_AKTUELLER_BETRAG)

          FROM AKFINANZ

         where AKF_KTO_JAHR = vsz_jahr

           and akf_kto_bun_nr = vsz_bun_nr

           and AKF_KTO_FLV_NR = vsz_flv_nr

           and (AKF_FINANZIERUNGSART = 29 

                 and AKF_FINANZIER in (1, 2))

           and AKF_KTO_VKZF = VSZ_VKZF

        group by AKF_KTO_FLV_NR,

                 AKF_KTO_JAHR,

                 AKF_KTO_VKZF) as Eigenleistung,

       --

       (SELECT sum (AKF_AKTUELLER_BETRAG)

          FROM AKFINANZ

         where AKF_KTO_JAHR = vsz_jahr

           and akf_kto_bun_nr = vsz_bun_nr

           and AKF_KTO_FLV_NR = vsz_flv_nr

           and (AKF_FINANZIERUNGSART = 32 
                 and AKF_FINANZIER IN (16, 1, 11))

           and AKF_KTO_VKZF = VSZ_VKZF

        group by AKF_KTO_FLV_NR,

                 AKF_KTO_JAHR,

                 AKF_KTO_VKZF) as ZuschussEU,

       --

       (SELECT SUM(AKF_AKTUELLER_BETRAG)

          FROM AKFINANZ

         WHERE AKF_KTO_JAHR = vsz_jahr

           and akf_kto_bun_nr = vsz_bun_nr

           and AKF_KTO_FLV_NR = vsz_flv_nr

           AND (AKF_FINANZIERUNGSART = 32 
                 AND AKF_FINANZIER IN (26, 0))

           and AKF_KTO_VKZF = VSZ_VKZF

        GROUP BY AKF_KTO_FLV_NR,

                 AKF_KTO_JAHR,

                 AKF_KTO_VKZF) as ZuschussGA,

       --

       (SELECT sum (AKF_AKTUELLER_BETRAG)

          FROM AKFINANZ

         where AKF_KTO_JAHR = vsz_jahr

           and akf_kto_bun_nr = vsz_bun_nr

           and AKF_KTO_FLV_NR = vsz_flv_nr

           and (AKF_FINANZIERUNGSART = 32 
                 and AKF_FINANZIER IN (4, 5, 14))

           and AKF_KTO_VKZF = VSZ_VKZF

        group by AKF_KTO_FLV_NR,

                 AKF_KTO_JAHR,

                 AKF_KTO_VKZF) as ZuschussLM,

       --

       (SELECT sum (AKF_AKTUELLER_BETRAG)

          FROM AKFINANZ

         where AKF_KTO_JAHR = vsz_jahr

           and akf_kto_bun_nr = vsz_bun_nr

           and AKF_KTO_FLV_NR = vsz_flv_nr

           and (AKF_FINANZIERUNGSART = 26 
                 and AKF_FINANZIER IN (1, 2, 3, 4, 5, 6))

           and AKF_KTO_VKZF = VSZ_VKZF

        group by AKF_KTO_FLV_NR,

                 AKF_KTO_JAHR,

                 AKF_KTO_VKZF) as KostenbeteiligungDritter,

       --

       (SELECT VFA_DATUM

          FROM VSZFFAKTUM

         where vfa_vsz_jahr = vsz_jahr

           and vfa_bun_nr = vsz_bun_nr

           and vfa_vsz_flv_nr = vsz_flv_nr

           and vfa_vsz_vkzf = VSZ_VKZF

           and vfa_vpo_vee_nr = 41) as Anordnung,

       --

       (SELECT VFA_DATUM

          FROM VSZFFAKTUM

         where vfa_vsz_jahr = vsz_jahr

           and vfa_bun_nr = vsz_bun_nr

           and vfa_vsz_flv_nr = vsz_flv_nr

           and vfa_vsz_vkzf = VSZ_VKZF

           and vfa_vpo_vee_nr = 141) as Ausführungsanordnung,

       --

       (SELECT sum (KTO_BETRAG)

          FROM KONTO

         where kto_jahr = vsz_jahr

           and kto_bun_nr = vsz_bun_nr

           and kto_flv_nr = vsz_flv_nr

           and kto_vkzf = VSZ_VKZF

           and kto_massnahmeart = 182) as VLEBeitrag

       --

  FROM VSZFNEU

 where vsz_jahr = 2007

   and vsz_bun_nr = 9

   and vsz_flv_nr =5

   and vsz_vkzf in (
     select vfa_vsz_vkzf 
       from VSZFFAKTUM

      where vfa_vsz_jahr = vsz_jahr

        and vfa_bun_nr = vsz_bun_nr

        and vfa_vsz_flv_nr = vsz_flv_nr

        and vfa_vsz_vkzf = VSZ_VKZF

        and vfa_vpo_vee_nr = 191

        and (vfa_datum = null or vfa_datum > 
               to_date('31.12.1994','dd.mm.yyyy'))

   )

Subselect

SELECT ALL a.AKF_KTO_FLV_NR,

           a.AKF_KTO_JAHR,

           a.AKF_KTO_VKZF,

           sum (a.AKF_AKTUELLER_BETRAG) 

                 + (SELECT sum (b.AKF_AKTUELLER_BETRAG)

                      FROM AKFINANZ b

                     WHERE b.AKF_KTO_JAHR = 2007

                       AND (b.AKF_FINANZIERUNGSART = 29 

                               and b.AKF_FINANZIER = 2)

                       AND b.AKF_KTO_VKZF = a.AKF_KTO_VKZF

                    ) as Eigenleistung

  FROM AKFINANZ a

 WHERE a.AKF_KTO_JAHR = 2007

   AND (a.AKF_FINANZIERUNGSART = 29 AND a.AKF_FINANZIER = 1)

   AND a.AKF_KTO_VKZF = 586031

GROUP BY a.AKF_KTO_FLV_NR,

         a.AKF_KTO_JAHR,

         a.AKF_KTO_VKZF

Ein Beispiel für Mengenoperationen

SELECT AKF_KTO_JAHR,

       AKF_KTO_FLV_NR,

       AKF_KTO_VKZF,

       AKF_KTO_NR,

       AKF_FINANZIERUNGSART,

       AKF_FINANZIER,

       AKF_FINANZKENNZ

  from akfinanz

 where AKF_FINANZIER in (41, 44)

   and (AKF_KTO_JAHR,

        AKF_KTO_FLV_NR,

        AKF_KTO_VKZF,

        AKF_KTO_NR,

        AKF_FINANZIERUNGSART,

        AKF_FINANZIER,

        AKF_FINANZKENNZ) IN (select AKF_KTO_JAHR,

                                    AKF_KTO_FLV_NR,

                                    AKF_KTO_VKZF,

                                    AKF_KTO_NR,

                                    AKF_FINANZIERUNGSART,

                                    AKF_FINANZIER,

                                    AKF_FINANZKENNZ

                               from akfinanz

                             MINUS

                             select AKF_KTO_JAHR,

                                    AKF_KTO_FLV_NR,

                                    AKF_KTO_VKZF,

                                    AKF_KTO_NR,

                                    AKF_FINANZIERUNGSART,

                                    AKF_FINANZIER,

                                    AKF_FINANZKENNZ

                               from akfinanz

                              where ((AKF_FINANZIERUNGSART = 32                                 and AKF_FINANZIER = 40)

                                 or (AKF_FINANZIERUNGSART = 32 
                                      and AKF_FINANZIER = 50)

                               )

                             MINUS

                             select AKF_KTO_JAHR,

                                    AKF_KTO_FLV_NR,

                                    AKF_KTO_VKZF,

                                    AKF_KTO_NR,

                                    AKF_FINANZIERUNGSART,

                                    AKF_FINANZIER,

                                    AKF_FINANZKENNZ

                               from akfinanz

                              where AKF_KTO_JAHR not in 2008

                             MINUS

                             select AKF_KTO_JAHR,

                                    AKF_KTO_FLV_NR,

                                    AKF_KTO_VKZF,

                                    AKF_KTO_NR,

                                    AKF_FINANZIERUNGSART,

                                    AKF_FINANZIER,

                                    AKF_FINANZKENNZ

                               from akfinanz

                              where AKF_FINANZKENNZ = 1

                                                                        )

Bei der Teil-Ergebnismenge müssen zwei Bedingungen erfüllt sein, die in einer Oder-Beziehung verknüpft sind. Alle Elemente der Gesamtmenge, die dieser TeilErgebnismenge nicht entsprechen, bilden die Lösungsmenge.

Ich bilde zwei Mengen, bei der ich von der Gesamtmenge die Menge subtrahiere (MINUS-Operator), die der ersten Bedingung entspricht. Dann subtrahiere ich die Menge, die der zweiten Bedingung entspricht. Somit enthalte ich die gewünschte Menge.