1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-00904: "ENDELZ": ungültiger Bezeichner (English: invalid identifier)

Discussion in 'SQL PL/SQL' started by riankail, Feb 28, 2012.

  1. riankail

    riankail Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    In our project (to create an report with an Oracle BI tool) we have constructed the following statement:

    SELECT
    AKP.ABRECHNUNGSOBJEKT AKPA, AKP.RASTERTYP, AKP.GJ, CAST(SUM(AKP.BUCHUNG) AS NUMBER) SUM_BUCHUNG, CAST(SUM(AKP.VORBINDUNG) AS NUMBER) SUM_VORBINDUNG, CAST(SUM(AKP.FESTLEGUNG) AS NUMBER) SUM_FESTLEGUNG, CAST(SUM(AKP.PLANUNG) AS NUMBER) SUM_PLANUNG, CAST(SUM(AKP.URBUDGET) AS NUMBER) SUM_URBUDGET , AOBJ.ABRECHNUNGSOBJEKT, COALESCE (EXTRACT (YEAR FROM AOBJ.STARTLAUFZEIT),2012) AS STARTLZ, COALESCE (EXTRACT (YEAR FROM AOBJ.ENDELAUFZEIT),2012) AS ENDELZ
    FROM
    ABRECHNUNGSOBJEKTE AOBJ
    FULL OUTER JOIN
    AOBJKTOPER AKP
    ON
    AKP.ABRECHNUNGSOBJEKT = AOBJ.ABRECHNUNGSOBJEKT
    LEFT JOIN
    SACHKTOGRUNDDATEN SKG
    ON
    AKP.KONTOTITEL = SKG.KONTOTITEL
    WHERE
    (AOBJ.STARTLAUFZEIT >= STARTLZ OR AOBJ.ENDELAUFZEIT <= ENDELZ)
    AND
    AKP.KOSTENRECHNUNG='KoRe_3 PRJ' AND SKG.KONTOART
    NOT IN
    ('KtoArt_Erloes','KtoArt_Statistik')
    GROUP BY
    AKP.ABRECHNUNGSOBJEKT,AKP.RASTERTYP,AKP.GJ, AOBJ.ABRECHNUNGSOBJEKT,AOBJ.STARTLAUFZEIT, AOBJ.ENDELAUFZEIT

    When we commit the statement, we get the following error message:
    java.sql.SQLSyntaxErrorException: ORA-00904: "ENDELZ": ungültiger Bezeichner (which means in English: invalid identifier).
    First of all we do not understand, why he grumbles about ENDELZ.
    Secondly, when he grumbles about ENDELZ he should first grumble about STARTLZ, why doesn't he?

    We are absolute SQL Beginners :)
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It's finding the the first unresolved identifier (I know that sounds strange but it has to do with how Oracle parses the statement). Resolve ENDELZ and the SQL engine will complain about STARTLZ until it's resolved. You're using column aliases in the query where they are defined and they won't resolve in the context of a where clause. You will need to replace the aliases with the actual constructs from the select statement. There are other ways to address this but since you're new to SQL I'll not confuse you further.
     
    riankail likes this.
  3. riankail

    riankail Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    OK, thank you David.

    Then I know what I have to find out, namely how I can declare or populate variables in our design tool. I guessed that, but I was not sure, because he did not grumble about STARTLZ - but what you say, I can understand. Oracle finds ENDELZ first, because of its parsing methods. Good to know.
    Thank you very much :)

    Then I will go back to work and try to find out....