Oracle SQL: New VIEW returns PLS-00428: INTO CLAUSE expected

Question

I am having trouble with a rather long view in Oracle SQL (I am using RazorSQL to access the DB via ODBC). My Code looks as follows (some of it, but everything is inside that one SELECT statement):

    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;

I deleted a few lines, so that the general structure can be seen but its not too long, imo. From what I know about Oracle SQL, VIEWs (as opposed to PROCEDURES) shouldn't need an INTO clause, but I am happy to be told otherwise. Thanks for any suggestions!

Johannes

Answer 1

There's something wrong in your code, between lines 43 and 63, here:

-- 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

You have the first LEFT JOIN that never ends, and then yet another LEFT JOIN which makes the CO alias. I marked it with the --> comment.