Wie kann man dynamische Spalten in einer Oracle SQL-Anweisung wiederverwenden?

Question

Ich versuche, einige Spalten wiederzuverwenden, die ich dynamisch in Oracle SQL berechne, etwa so

SELECT
    A*2 AS P,
    P+5 AS Q
FROM tablename

Die Spalte "Tabellenname" hat eine Spalte mit dem Namen "A", aber keine anderen Spalten. Dies gibt mir eine

ORA-00904: "P": invalid identifier

Ich weiß, wie man dieses Problem umgehen kann, indem man eine Unterabfrage wie

SELECT P, P+5 AS Q
FROM ( SELECT A*2 AS P FROM tablename )

aber ich denke, das ist ziemlich hässlich. Außerdem möchte ich die Abfrage etwas komplexer gestalten, z. B. auch "Q" wiederverwenden, und ich möchte nicht noch eine weitere Unterabfrage erstellen.

Aktualisierung: Der Grund, warum ich die Berechnung von "P" speichern möchte, ist, dass ich sie komplexer gestalten und "P" mehrfach verwenden möchte. Ich möchte also nicht explizit sagen "A*2+5 AS Q", denn das würde schnell zu umständlich werden, wenn "P" komplexer wird.

Es muss doch eine gute Möglichkeit geben, dies zu tun, haben Sie eine Idee?

Update: Ich sollte anmerken, dass ich kein DB-Admin bin :(.

---

Update: Ein Beispiel aus der Praxis, mit einer konkreteren Abfrage. Was ich gerne tun würde, ist:

SELECT 
    SL/SQRT(AB) AS ALPHA,
    5*LOG(10,ALPHA) AS B,
    2.5*LOG(10,1-EXP(-5/ALPHA)*(5/ALPHA+1)) AS D
    BS -2.74 + B + D AS BSA
FROM tablename

Ich habe es vorerst ausgeschrieben, was funktioniert, aber hässlich ist:

SELECT
    SL/SQRT(AB) AS ALPHA,
    5*LOG(10,SL/SQRT(AB)) AS B,
    2.5*LOG(10,1-EXP(-5/(SL/SQRT(AB)))*(5/(SL/SQRT(AB))+1)) AS D
    BS -2.74 + 5*LOG(10,SL/SQRT(AB)) + 2.5*LOG(10,1-EXP(-5/(SL/SQRT(AB)))*((5/(SL/SQRT(AB)))+1)) AS BSA
FROM tablename

Ich könnte all dies tun, nachdem ich die Daten erhalten habe, aber ich dachte, mal sehen, wie viel ich die Datenbank machen lassen kann. Außerdem möchte ich auch nach "BSA" auswählen (was ich jetzt mit dieser Abfrage als Unterabfrage/Mit-Klausel tun kann).

---

Aktualisieren: OK, ich glaube, ich bin jetzt mit der Lösung von Cade Roux und Dave Costa fertig. Obwohl die Lösung von Pax und Jens Schauder besser aussehen würde, aber ich kann sie nicht verwenden, da ich kein DBA bin. Jetzt weiß ich nicht, wen ich als die beste Antwort markieren soll :).

WITH 
  A1 AS ( 
    SELECT A0.*, 
    SL/SQRT(AB) AS ALPHA
    FROM tablename A0
  ),
  A2 AS (
    SELECT A1.*, 
    5*LOG(10,ALPHA) AS B,
    2.5*LOG(10,1-EXP(-5/ALPHA)*((5/ALPHA)+1)) AS D
    FROM A1
  )
SELECT
  ALPHA, B, D, BS,
  BS -2.74 + B + D AS BSA
FROM A2

---

Übrigens, falls es jemanden interessiert: SB ist die "Oberflächenhelligkeit" von Galaxien, für die B und D Korrekturterme sind.

Answer 1

Wir haben das gleiche Problem in SQL Server (es ist ein ANSI-Problem). Ich glaube, es ist beabsichtigt, verwirrende Aliasing-Effekte zu vermeiden:

SELECT A * 2 AS A
    ,A * 3 AS B -- This is the original A, not the new A
FROM whatever

Wir umgehen das Problem, indem wir gängige Tabellenausdrücke übereinanderlegen:

WITH A1 AS (
    SELECT A * 2 AS A
    FROM whatever
)
,A2 AS (
    SELECT A1.*
        ,A * 3 AS B
    FROM A1
)
,A3 AS (
    SELECT A2.*
        ,A + B AS X
    FROM A2
)
SELECT *
FROM A3

Dies ist die am besten lesbare, überschaubare und nachvollziehbare Version.

Für UPDATEs gibt es einen veralteten SQL-Server-Workaround, der die Notation column_name = verwendet, bei der Sie auf eine Spalte verweisen können, die zuvor in der Liste aktualisiert wurde. Dies kann jedoch nicht in SELECTs verwendet werden.

Ich hoffe, dass ANSI SQL irgendwann in der Zukunft die Möglichkeit bietet, Ausdrücke zu stapeln (ohne Verwendung einer skalaren UDF).

Answer 2

Ich bin mir nicht sicher, ob man das tun kann (ich habe es noch nie gesehen), aber man könnte das Problem damit umgehen:

SELECT
    A*2   AS P,
    A*2+5 AS Q
FROM tablename

Das ist sicherlich besser als die Einführung einer Unterabfrage.

Die einzige andere Möglichkeit, die ich vorschlagen würde, ist die Erstellung einer Ansicht, die Ihnen die Spalten vom Typ P/Q liefert (unter Verwendung der oben genannten Formeln), was zumindest den Text der Abfrage vereinfachen würde. Dann könnten Sie einfach:

SELECT P, Q FROM viewintotablename

Answer 3

Das können Sie nicht.

Wenn Sie nicht möchten, dass die Unterabfrage neu ausgewertet wird, fügen Sie eine NO_MERGE Hinweis für die Unterabfrage:

Diese Unterabfrage wird in der geschachtelten Schleife neu ausgewertet ( MERGE Hinweis verwendet wird):

SELECT  /*+ LEADING(g) USE_NL(g, r) MERGE(g) */
        *
FROM    (
        SELECT  1
        FROM    dual
        UNION ALL
        SELECT  2
        FROM    dual
        ) r, 
        (
        SELECT  SYS_GUID() AS guid
        FROM    dual d
        ) g

---
33CA48C1AB6B4403808FB0219302CE43
711BB04F9AFC406ABAEF8A8F4CFA1266

Diese Unterabfrage wird in der geschachtelten Schleife nicht neu ausgewertet ( NO_MERGE Hinweis verwendet wird):

SELECT  /*+ LEADING(g) USE_NL(g, r) NO_MERGE(g) */
        *
FROM    (
        SELECT  1
        FROM    dual
        UNION ALL
        SELECT  2
        FROM    dual
        ) r, 
        (
        SELECT  SYS_GUID() AS guid
        FROM    dual d
        ) g

------
7715C69698A243C0B379E68ABB55C088
7715C69698A243C0B379E68ABB55C088

In Ihrem Fall schreiben Sie einfach:

SELECT  BS - 2.74 + d
FROM    (
        SELECT  t2.*, 2.5 * LOG(10, 1 - EXP(-5 / b)) * ((5 / A) + 1) AS d
        FROM    (
                SELECT  t1.*, 5 * LOG(10, alpha) AS b
                FROM    (
                        SELECT  /*+ NO_MERGE */ t.*,
                                SL/SQRT(AB) AS alpha
                        FROM    tablename t
                        ) t1
                ) t2
        ) t3

die effizienter ist ( EXP y LOG sind kostspielig) und ist viel einfacher zu debuggen.

Answer 4

Es gibt keine direkte Möglichkeit, dies in Sql zu tun.

Sie könnten aber eine Funktion mit PL/SQL definieren. Ihr Select würde dann wie folgt aussehen

select 
    P(A), 
    Q(P(A)) 
from tablename

Für P und Q ist dies nicht (viel) besser als das Original, aber wenn die Funktionen komplex sind und nicht zu viele Parameter haben, könnte es Ihre Aussage viel lesbarer machen.

Es würde Ihnen auch ermöglichen, Ihre Funktionen unabhängig von der SQL-Anweisung und den Daten zu testen.

Answer 5

Vielleicht gefällt Ihnen das etwas besser als das Beispiel mit der Inline-Ansicht, das Sie gegeben haben:

WITH inner_view AS ( SELECT A*2 AS P FROM tablename )
SELECT P, P+5 AS Q
FROM inner_view

Es läuft auf dasselbe hinaus, aber es ist etwas klarer zu lesen, denke ich.

Wenn Sie die berechnete Spalte in mehreren Spalten verwenden wollen, kann es sinnvoll sein, eine permanente Ansicht zu erstellen.

Oracle 11 (das ich noch nicht verwendet habe) hat eine virtuelle Spaltenfunktion, die für Sie nützlich sein könnte.