Oracle: Spaltenwert über 2 Ebenen in Subqueries durchreichen

[hsg]

Hallo,
ich kämpfe gerade mit einem etwas blöden SQL-Statement:

Code:

select kp.vorgangs_nr as "VGNR1",
       kp.artikel_nr as "ArtikelNr",
       kp.bestaetigter_termin as "Bestaetigter Termin 1",
       (select sum(menge) 
          from (            
                 select ek.artikel_nr as Artikel,
                       (ek.menge_soll - ek.menge_lager_ist - ek.menge_wareneingang_ist) as Menge,
                       ek.bestaetigter_termin as Datum
                 from pekp ek
                 where ek.artikel_nr = kp.artikel_nr
                   and ek.bestaetigter_termin <  kp.bestaetigter_termin
                   and ek.vorgangs_art = 'BE'
                   and ek.bestellpositions_status < 6
                                  
                union all
                
                select sp.komponenten_artikel_nr as Artikel,
                       -1 * (sp.entnahme_menge - sp.entnahme_menge_ist) as Menge,
                       sp.entnahme_termin_gre as Datum
                 from pfsp sp
                 where sp.entnahme_termin_gre <  kp.bestaetigter_termin
                   and sp.komponenten_artikel_nr =  kp.artikel_nr */ 
                   and sp.rueckmelde_status between 1 and 3
            )
         ) as "Deckung"
  from pekp kp

Das Statement ist hier jetzt etwas vereinfacht: im innersten Block werden noch mehr Tabellen angezogen und deren Werte über "union all" zu einer Ergebnismenge vereint.
Das ganze dient als Berechnung des theoretischen Lagerbestandes des jeweiligen Artikels zum Zeitpunkt des bestätigten Liefertermines einer Bestellung.

Ich habe also quasi ein Subquery mit einem Subqery, dessen innerster Block von den Spaltenwerten des äußersten Selects abhängig ist.

Was leider in Oracle nicht geht (Ora-Fehler ORA-00904: "KP"."BESTAETIGER_TERMIN": ungültiger Bezeichner)

Natürlich könnte ich jetzt den innersten Block als Stored-Function schreiben, aber für eine einzige Abfrage, die nur alle paar Wochen von einem einzigen Mitarbeiter aufgerufen wird und sonst niemanden interessiert, ist mir das zu viel Aufwand in der Pflege (diese Funktion müsste in 9 Datenbereichen gepflegt werden).

Irgendwelche Alternativen?

[jobo]

Also diese Art Subselects sind mir immer suspekt.
Wieso lagerst Du das nicht in die normale From Clause mit klassischen Joins usw.
Natürlich muss dann das Schlüsselfeld mit selektiert werden und gegrouped.

Ach und nochwas:
ist das ein Statement für einen Report (also viele Daten) oder für eine Einzeldarstelleung (also bspw per Auswahl Artikelnummer/ Vorgangsnummer)

[smallie]

Zitat von hsg:

Hallo,
ich kämpfe gerade mit einem etwas blöden SQL-Statement:

<schnipp>

Irgendwelche Alternativen?

Das sieht für mich nach WITH-Clause aus.

Damit lassen sich "im Vorübergehen" Unterabfragen mit Namen versehen, die sich im Weiteren wie eine Tabelle oder View ansprechen lassen.

Code:

with EKSP as
            (   select ek.artikel_nr as Artikel,
                       (ek.menge_soll - ek.menge_lager_ist - ek.menge_wareneingang_ist) as Menge,
                       ek.bestaetigter_termin as Datum
                 from pekp ek
                 where ek.vorgangs_art = 'BE'
                   and ek.bestellpositions_status < 6
                                  
                union all
                
                select sp.komponenten_artikel_nr as Artikel,
                       -1 * (sp.entnahme_menge - sp.entnahme_menge_ist) as Menge,
                       sp.entnahme_termin_gre as Datum
                 from pfsp sp
                 where sp.rueckmelde_status between 1 and 3
            )

select kp.vorgangs_nr as "VGNR1",
       kp.artikel_nr as "ArtikelNr",
       kp.bestaetigter_termin as "Bestaetigter Termin 1",
       sum(EKSP.menge)
  from pekp kp, EKSP
      where EKSP.datum <  kp.bestaetigter_termin
        and EKSP.artikel =  kp.artikel_nr  
  group by kp.vorgangs_nr, kp.artikel_nr, kp.bestaetigter_termin

So ungefähr.

Mit dem Ansatz sollten sich auch die weiteren Subqueries bändigen lassen.

Mit mehreren Unterabfragen sieht die Syntax so aus:

Code:

with FOO as 
   (  select some_fields from some_tables ),

     BAR as
   (  select some_fields from some_other_tables )  -- kein Komma hier

select f.some_fields, b.some_fields                -- erstes Statement muß select sein
  from FOO f, BAR b
  where f.some_id = b.some_id

Wenn die Unterabfragen mehrmals durchgenudelt werden müssten, ist die WITH-Clause auch um ein Hauseck schneller.

[p80286]

SQL-Code:

                 select ek.artikel_nr as Artikel,
                       (ek.menge_soll - ek.menge_lager_ist - ek.menge_wareneingang_ist) as Menge,
                       ek.bestaetigter_termin as Datum
                 from pekp ek
                 where ek.artikel_nr = kp.artikel_nr
                   and ek.bestaetigter_termin <  kp.bestaetigter_termin ---<< woher soll denn dieses Datum kommen? da ek=kp ist die Bedingung nie erfüllt???????

Gruß K-H

[hsg]

Zitat von jobo:

Also diese Art Subselects sind mir immer suspekt.
Wieso lagerst Du das nicht in die normale From Clause mit klassischen Joins usw.
Natürlich muss dann das Schlüsselfeld mit selektiert werden und gegrouped.

Ach und nochwas:
ist das ein Statement für einen Report (also viele Daten) oder für eine Einzeldarstelleung (also bspw per Auswahl Artikelnummer/ Vorgangsnummer)

Das Statement ist für einen Report gedacht, also viele Artikel und viele Bestelldaten, die gegeneinander geprüft werden müssen.

Allerdings ist mir dein Ansatz zur Lösung nicht ganz klar. Ich brauche zum Schluss zu jedem Artikel der Hauptauswahl (die dort natürlich noch durch zusätzliche Selektionen gefiltert werden) zum Zeitpunkt eines Einkaufsvorganges die theoretische Deckungsmenge, also die Summe aller Lagerzu- und abgänge aus verschiedenen anderen Objekten.

Zitat von smallie:

Das sieht für mich nach WITH-Clause aus.

Damit lassen sich "im Vorübergehen" Unterabfragen mit Namen versehen, die sich im Weiteren wie eine Tabelle oder View ansprechen lassen.

An WITH hatte ich auch schon gedacht, habe allerdings noch keine Erfahrung damit gemacht. Dadurch hatte ich keine Idee, wie ich das Datum dort unterbringe, nun sehe ich etwas klarer und werde es mal ausprobieren.

Zitat von p80286:

SQL-Code:

                 select ek.artikel_nr as Artikel,
                       (ek.menge_soll - ek.menge_lager_ist - ek.menge_wareneingang_ist) as Menge,
                       ek.bestaetigter_termin as Datum
                 from pekp ek
                 where ek.artikel_nr = kp.artikel_nr
                   and ek.bestaetigter_termin <  kp.bestaetigter_termin ---<< woher soll denn dieses Datum kommen? da ek=kp ist die Bedingung nie erfüllt???????

Gruß K-H

Nein, ek und kp sind zwar dieselbe Tabelle, aber unterschiedliche Datensätze daraus. Die EK-Sätze sind kurz gesprochen alle Bestellungen, die vor der zu untersuchenden Bestellung liegen.

Danke an alle für den bisherigen Input!

Edit: Habe es jetzt mit WITH gelöst bekommen. Nochmals Dank für den Hinweis!