Oracle SQL: новый VIEW возвращает PLS-00428: ожидаемый ввод

Question

У меня проблемы с довольно длинным представлением в Oracle SQL (я использую RazorSQL для доступа к БД через ODBC). Мой код выглядит следующим образом (некоторые из них, но все внутри этого оператора SELECT):

    CREATE OR REPLACE VIEW VFWS1 AS

-- Hier oben werden alle Abfragen zusammengefasst:
SELECT DISTINCT
    EQ.BB_PARSEKEY, -- Zentraler Identifier aller Instrumente
    IP2.VALUE AS PREIS, -- Kursabfrage
    WHG.CODETAB_CD AS CRNCY, -- Währung
    MS.CODETAB_CD AS MARKET_STATUS, -- Marktstatus
    BP.CODETAB_CD AS EXCH_CODE, -- Börse
    KI.*, -- das sind alle Instrument-Kennzahlen
    K_VT.*, -- das sind alle Vortags-Kennzahlen
    IC.*, -- das sind alle Instrument-spezifischen Daten
    CO.* -- das sind alle Company-spezifischen Daten

-- Hier kommt die Ident-Tabelle, d.h. alle werden hierauf gejoint:
FROM (SELECT DISTINCT INSTRUMENT_ID, BB_PARSEKEY FROM DATABASE1P.INSTRUMENTCUSTOM 
    WHERE VALIDUNTIL_DAT = '31.12.9999' -- Instrument muss gültig sein
    AND INSTRUMENTART IN ('FixedIncome', 'Equity', 'Index') 
    AND BB_PARSEKEY IS NOT NULL) EQ

-- Join-Tabelle für alle möglichen IDs  
LEFT JOIN DATABASE1P.INSTRUMENT I ON EQ.INSTRUMENT_ID = I.INSTRUMENT_ID

-- Preise abrufen:
LEFT JOIN (SELECT INSTRUMENT_ID, VALUE, MARKET_DAT, PRICE_DAT FROM DATABASE1P.INSTRUMENTPRICE IP1
    LEFT JOIN DATABASE1P.CODETAB PR ON IP1.PROVIDER_ID = PR.CODETAB_ID 
    WHERE PR.CODETAB_CD = 'BB' -- Nur Bloomberg-Kurse ziehen
    AND TRUNC(PRICE_DAT) = (SELECT MAX(TRUNC(PRICE_DAT)) FROM DATABASE1P.INSTRUMENTPRICE WHERE TRUNC(PRICE_DAT) <> '31.12.9999')) IP2
    -- (letzte Zeile) Nur die letzten Kurse abrufen
ON EQ.INSTRUMENT_ID = IP2.INSTRUMENT_ID

-- Weitere Instrument-Daten abrufen:
LEFT JOIN (SELECT INSTRUMENT_ID, 
    -- Highs/Lows:
    HIGH_52WEEK, BB_HIGH_DT_52WEEK, LOW_52WEEK, BB_LOW_DT_52WEEK, 
    -- Equity-Felder
    BB_EQY_BOLL_UPPER, BB_EQY_BOLL_LOWER, 
    -- FixedIncome-Felder:
    COLLAT_TYP, ISSUER, MATURITY, CPN, BB_CPN_TYP, BB_IS_UNIT_TRADED 
    FROM DATABASE1P.INSTRUMENTCUSTOM WHERE VALIDUNTIL_DAT = '31.12.9999') IC
ON EQ.INSTRUMENT_ID = IC.INSTRUMENT_ID

-- Company-Daten abrufen:
LEFT JOIN (SELECT 
    -- Originäre Company
    C1.COMPANY_ID, C1.COMPANY_NR, C1.COMPANY_NAME, 
    --Parent-Company
    P1.COMPANY_NR AS PARENT_NR, P1.COMPANY_NAME AS PARENT_NAME, 
    -- Branchen-Zuordnung über Feldgruppierung:
    CD_GRP.CODETAB_CD AS GRP, CD_SEC.CODETAB_CD AS SEC, CD_SUBGRP.CODETAB_CD AS SUBGRP

    -- Company-Branchen-Daten über Codetabelle-Gruppierung abrufen:
    LEFT JOIN (SELECT C1.CODETAB_ID, C1.CODETAB_CD AS BB_BRANCHE, C3.CODETAB_CD AS FWS_BRANCHE 
        FROM DATABASE1P.CODETABGROUP CG 
        LEFT JOIN DATABASE1P.CODETAB C1 ON CG.ITEM_ID = C1.CODETAB_ID 
        LEFT JOIN DATABASE1P.CODETAB C2 ON CG.GROUP_ID = C2.CODETAB_ID 
        LEFT JOIN DATABASE1P.CODETAB C3 ON CG.BASKET_ID = C3.CODETAB_ID 
        WHERE CG.VALIDUNTIL_DAT = '31.12.9999' 
        AND C2.CODETAB_CD = 'FWS_BRANCHEN') BR 
    ON CC1.INDUSTRY_SUBGR_NR_CD = BR.CODETAB_ID) CO
ON I.ISSUER_ID = CO.COMPANY_ID

-- Alle Instrument-Kennzahlen
LEFT JOIN (SELECT INSTRUMENT_ID AS ID_K1, 
    K1.BB_PX_BID, 
    K2.BB_PX_ASK, 
    K3.BB_YLD_YTM_MID


    FROM DATABASE1P.INSTRUMENT I1
    -- Da die Tabelle KENNZAHL normalisiert ist, muss für jede Kennzahl getrennt gejoint werden:
    LEFT JOIN (SELECT REF_ID, KENNZAHL AS BB_PX_BID FROM DATABASE2P.KENNZAHL K LEFT JOIN DATABASE2P.KENNZAHLTYP KT ON K.KENNZAHLTYP_ID = KT.KENNZAHLTYP_ID WHERE KT.KENNZAHLTYP_CD = 'BB_PX_BID' AND K.GUELTIGBIS_DAT = '31.12.9999') K1 ON I1.DATABASE2_ID = K1.REF_ID
    LEFT JOIN (SELECT REF_ID, KENNZAHL AS BB_PX_ASK FROM DATABASE2P.KENNZAHL K LEFT JOIN DATABASE2P.KENNZAHLTYP KT ON K.KENNZAHLTYP_ID = KT.KENNZAHLTYP_ID WHERE KT.KENNZAHLTYP_CD = 'BB_PX_ASK' AND K.GUELTIGBIS_DAT = '31.12.9999') K2 ON I1.DATABASE2_ID = K2.REF_ID
    LEFT JOIN (SELECT REF_ID, KENNZAHL AS BB_YLD_YTM_MID FROM DATABASE2P.KENNZAHL K LEFT JOIN DATABASE2P.KENNZAHLTYP KT ON K.KENNZAHLTYP_ID = KT.KENNZAHLTYP_ID WHERE KT.KENNZAHLTYP_CD = 'BB_YLD_YTM_MID' AND K.GUELTIGBIS_DAT = '31.12.9999') K3 ON I1.DATABASE2_ID = K3.REF_ID

    ) KI
ON I.INSTRUMENT_ID = KI.ID_K1

-- Alle Vortags-Kennzahlen
LEFT JOIN (SELECT INSTRUMENT_ID AS ID_K2,
    K_VT1.BB_YLD_YTM_MID AS VT_BB_YLD_YTM_MID,
    K_VT2.BB_YLD_YTM_BID AS VT_BB_YLD_YTM_BID,
    K_VT3.BB_YLD_YTM_ASK AS VT_BB_YLD_YTM_ASK,
    K_VT4.BB_TOT_PUT_VOL_DAY AS VT_BB_TOT_PUT_VOL_DAY,
    K_VT5.BB_TOT_CALL_VOL_DAY AS VT_BB_TOT_CALL_VOL_DAY,
    K_VT6.BB_PX_VOLUME AS VT_BB_PX_VOLUME

    FROM DATABASE1P.INSTRUMENT I1
    -- Das Vortags-Matching erfolgt analog zu den Kennzahlen oben, allerdings mit der Einschränkung, dass das Datum das vorletzte sein muss, sprich Gültigbis darf nicht 31.12.9999 sein, Gültigvon aber auch nicht zu alt:
    LEFT JOIN (SELECT REF_ID, KENNZAHL AS BB_YLD_YTM_MID FROM DATABASE2P.KENNZAHL K LEFT JOIN DATABASE2P.KENNZAHLTYP KT ON K.KENNZAHLTYP_ID = KT.KENNZAHLTYP_ID WHERE KT.KENNZAHLTYP_CD = 'BB_YLD_YTM_MID' 
    AND TRUNC(K.GUELTIGBIS_DAT) = (SELECT MAX(TRUNC(GUELTIGBIS_DAT)) FROM DATABASE2P.KENNZAHL KD LEFT JOIN DATABASE2P.KENNZAHLTYP KTD ON KD.KENNZAHLTYP_ID = KTD.KENNZAHLTYP_ID WHERE TRUNC(KD.GUELTIGBIS_DAT) < '31.12.9999' AND KTD.KENNZAHLTYP_CD = 'BB_YLD_YTM_MID')) K_VT1 ON I1.DATABASE2_ID = K_VT1.REF_ID
    LEFT JOIN (SELECT REF_ID, KENNZAHL AS BB_YLD_YTM_BID FROM DATABASE2P.KENNZAHL K LEFT JOIN DATABASE2P.KENNZAHLTYP KT ON K.KENNZAHLTYP_ID = KT.KENNZAHLTYP_ID WHERE KT.KENNZAHLTYP_CD = 'BB_YLD_YTM_BID' 
    AND TRUNC(K.GUELTIGBIS_DAT) = (SELECT MAX(TRUNC(GUELTIGBIS_DAT)) FROM DATABASE2P.KENNZAHL KD LEFT JOIN DATABASE2P.KENNZAHLTYP KTD ON KD.KENNZAHLTYP_ID = KTD.KENNZAHLTYP_ID WHERE TRUNC(KD.GUELTIGBIS_DAT) < '31.12.9999' AND KTD.KENNZAHLTYP_CD = 'BB_YLD_YTM_BID')) K_VT2 ON I1.DATABASE2_ID = K_VT2.REF_ID
    LEFT JOIN (SELECT REF_ID, KENNZAHL AS BB_YLD_YTM_ASK FROM DATABASE2P.KENNZAHL K LEFT JOIN DATABASE2P.KENNZAHLTYP KT ON K.KENNZAHLTYP_ID = KT.KENNZAHLTYP_ID WHERE KT.KENNZAHLTYP_CD = 'BB_YLD_YTM_ASK' 
    AND TRUNC(K.GUELTIGBIS_DAT) = (SELECT MAX(TRUNC(GUELTIGBIS_DAT)) FROM DATABASE2P.KENNZAHL KD LEFT JOIN DATABASE2P.KENNZAHLTYP KTD ON KD.KENNZAHLTYP_ID = KTD.KENNZAHLTYP_ID WHERE TRUNC(KD.GUELTIGBIS_DAT) < '31.12.9999' AND KTD.KENNZAHLTYP_CD = 'BB_YLD_YTM_ASK')) K_VT3 ON I1.DATABASE2_ID = K_VT3.REF_ID
    LEFT JOIN (SELECT REF_ID, KENNZAHL AS BB_TOT_PUT_VOL_DAY FROM DATABASE2P.KENNZAHL K LEFT JOIN DATABASE2P.KENNZAHLTYP KT ON K.KENNZAHLTYP_ID = KT.KENNZAHLTYP_ID WHERE KT.KENNZAHLTYP_CD = 'BB_TOT_PUT_VOL_DAY' 
    AND TRUNC(K.GUELTIGBIS_DAT) = (SELECT MAX(TRUNC(GUELTIGBIS_DAT)) FROM DATABASE2P.KENNZAHL KD LEFT JOIN DATABASE2P.KENNZAHLTYP KTD ON KD.KENNZAHLTYP_ID = KTD.KENNZAHLTYP_ID WHERE TRUNC(KD.GUELTIGBIS_DAT) < '31.12.9999' AND KTD.KENNZAHLTYP_CD = 'BB_TOT_PUT_VOL_DAY')) K_VT4 ON I1.DATABASE2_ID = K_VT4.REF_ID
    LEFT JOIN (SELECT REF_ID, KENNZAHL AS BB_TOT_CALL_VOL_DAY FROM DATABASE2P.KENNZAHL K LEFT JOIN DATABASE2P.KENNZAHLTYP KT ON K.KENNZAHLTYP_ID = KT.KENNZAHLTYP_ID WHERE KT.KENNZAHLTYP_CD = 'BB_TOT_CALL_VOL_DAY' 
    AND TRUNC(K.GUELTIGBIS_DAT) = (SELECT MAX(TRUNC(GUELTIGBIS_DAT)) FROM DATABASE2P.KENNZAHL KD LEFT JOIN DATABASE2P.KENNZAHLTYP KTD ON KD.KENNZAHLTYP_ID = KTD.KENNZAHLTYP_ID WHERE TRUNC(KD.GUELTIGBIS_DAT) < '31.12.9999' AND KTD.KENNZAHLTYP_CD = 'BB_TOT_CALL_VOL_DAY')) K_VT5 ON I1.DATABASE2_ID = K_VT5.REF_ID
    LEFT JOIN (SELECT REF_ID, KENNZAHL AS BB_PX_VOLUME FROM DATABASE2P.KENNZAHL K LEFT JOIN DATABASE2P.KENNZAHLTYP KT ON K.KENNZAHLTYP_ID = KT.KENNZAHLTYP_ID WHERE KT.KENNZAHLTYP_CD = 'BB_PX_VOLUME' 
    AND TRUNC(K.GUELTIGBIS_DAT) = (SELECT MAX(TRUNC(GUELTIGBIS_DAT)) FROM DATABASE2P.KENNZAHL KD LEFT JOIN DATABASE2P.KENNZAHLTYP KTD ON KD.KENNZAHLTYP_ID = KTD.KENNZAHLTYP_ID WHERE TRUNC(KD.GUELTIGBIS_DAT) < '31.12.9999' AND KTD.KENNZAHLTYP_CD = 'BB_PX_VOLUME')) K_VT6 ON I1.DATABASE2_ID = K_VT6.REF_ID

) K_VT
ON I.INSTRUMENT_ID = K_VT.ID_K2

-- Währung übersetzen
LEFT JOIN (SELECT * FROM DATABASE1P.CODETAB WHERE TABTYPE = 'WAEHRUNG') WHG
ON IC.CRNCY1 = WHG.DEFAULTLABEL

-- Marktstatus übersetzen
LEFT JOIN (SELECT * FROM DATABASE1P.CODETAB WHERE TABTYPE = 'FELDTABCODE') MS
ON IC.MARKET_STATUS1 = MS.DEFAULTLABEL

-- Boepla übersetzen
LEFT JOIN (SELECT * FROM DATABASE1P.CODETAB WHERE TABTYPE = 'BOEPLA') BP
ON IC.EXCH_CODE1 = BP.DEFAULTLABEL;

Я удалил несколько строк, чтобы можно было увидеть общую структуру, но она не слишком длинная, imo. Из того, что я знаю об Oracle SQL, VIEWs (в отличие от PROCEDURES) не должен нуждаться в предложении INTO, но я рад, что мне сказали иначе. Спасибо за любые предложения!

Johannes

Answer 1

Что-то не так в вашем коде, между строками 43 и 63 здесь:

-- Company-Daten abrufen:
LEFT JOIN (SELECT 
    -- Originäre Company
    C1.COMPANY_ID, C1.COMPANY_NR, C1.COMPANY_NAME, 
    --Parent-Company
    P1.COMPANY_NR AS PARENT_NR, P1.COMPANY_NAME AS PARENT_NAME, 
    -- Branchen-Zuordnung über Feldgruppierung:
    CD_GRP.CODETAB_CD AS GRP, CD_SEC.CODETAB_CD AS SEC, CD_SUBGRP.CODETAB_CD AS SUBGRP
--> as if you're missing a closing bracket and the ON clause
    -- Company-Branchen-Daten über Codetabelle-Gruppierung abrufen:
    LEFT JOIN (SELECT C1.CODETAB_ID, C1.CODETAB_CD AS BB_BRANCHE, C3.CODETAB_CD AS FWS_BRANCHE 
        FROM DATABASE1P.CODETABGROUP CG 
        LEFT JOIN DATABASE1P.CODETAB C1 ON CG.ITEM_ID = C1.CODETAB_ID 
        LEFT JOIN DATABASE1P.CODETAB C2 ON CG.GROUP_ID = C2.CODETAB_ID 
        LEFT JOIN DATABASE1P.CODETAB C3 ON CG.BASKET_ID = C3.CODETAB_ID 
        WHERE CG.VALIDUNTIL_DAT = '31.12.9999' 
        AND C2.CODETAB_CD = 'FWS_BRANCHEN') BR 
    ON CC1.INDUSTRY_SUBGR_NR_CD = BR.CODETAB_ID) CO
ON I.ISSUER_ID = CO.COMPANY_ID

-- Alle Instrument-Kennzahlen

У вас первый LEFT JOIN что никогда не заканчивается, а потом еще LEFT JOIN который делает псевдоним CO. Я отметил это --> комментарий.