"identifier is too long" error

[MithilaVR]

Hello,

I get error on my oracle query: identifier is too long. However, even when I make the identifier or column names 5 letters long, it stil lgives me the same error. 

More over, I am using the same "long" column names for another input tool with a slightly different query, it doesnt give me this error.

errorneous query:

SELECT
YEAR,
INSERT_TIMESTAMP,
AVG(AREANME) AS AREANME_PREVDAY_AVG,
AVG(REGIONAL) AS REGIONAL_PREVDAY_AVG,
AVG(NATIONAL) AS NATIONAL_PREVDAY_AVG,
AVG(CLIENT1_NAME_NULLS) AS gnpa--CLIENT1_NAME_NULLS_PREVDAY_AVG,
AVG(CLIENT2_NAME_NULLS) AS dnpa--CLIENT2_NAME_NULLS_PREVDAY_AVG,
AVG(SALES_TERRITORY_NULLS) AS stnpa--SALES_TERRITORY_NULLS_PREVDAY_AVG
FROM SEED_METRIC_LOG
WHERE TO_DATE(INSERT_TIMESTAMP, 'RRRR-MM-DD HH24:MI:SS') >= '27-jun-2024'
and year='2024'
group by YEAR,
INSERT_TIMESTAMP

non-errorneous query: slightly similar query but no errors in alteryx

SELECT

YEAR,
INSERT_TIMESTAMP,
AVG(AREANME) AS AREANME_PREVDAY_AVG,
AVG(REGIONAL) AS REGIONAL_PREVDAY_AVG,
AVG(NATIONAL) AS NATIONAL_PREVDAY_AVG,
AVG(CLIENT1_NAME_NULLS) AS CLIENT1_NAME_NULLS_PREVDAY_AVG,
AVG(CLIENT2_NAME_NULLS) AS CLIENT2_NAME_NULLS_PREVDAY_AVG,
AVG(SALES_TERRITORY_NULLS) AS SALES_TERRITORY_NULLS_PREVDAY_AVG

FROM METRIC_LOG

WHERE SUBSTR(TO_TIMESTAMP(INSERT_TIMESTAMP, 'RRRR-MM-DD HH24:MI:SS'),1,9) = (

    SELECT INSERT_DATE FROM (

        SELECT ROW_NUMBER() OVER(ORDER BY "Date_For_Entry" DESC) AS ROWNUM_,

        SUBSTR("Date_For_Entry",1,9) AS INSERT_DATE

        FROM  DATA_TABLE

        WHERE "Dataset_Name" = 'Name'

        AND SUBSTR("Date_For_Entry",1,9) <> SUBSTR(CURRENT_TIMESTAMP,1,9)

        )

    WHERE ROWNUM_ = 1)

GROUP BY YEAR, INSERT_TIMESTAMP

ORDER BY YEAR DESC

[apathetichell]

I believe you are having an issue with the hyphen in your fieldnames. switch to undrescore or try putting them in quotes.

[MithilaVR]

those are underscores. i only have hyphens in the Date expression.

[apathetichell]

sorry - let me rephrase - try getting rid of the inline comments (no idea why it would affect it but it seems like the only major difference between the two queries).

also - try running 

    SELECT INSERT_DATE FROM (

        SELECT ROW_NUMBER() OVER(ORDER BY "Date_For_Entry" DESC) AS ROWNUM_,

        SUBSTR("Date_For_Entry",1,9) AS INSERT_DATE

        FROM  DATA_TABLE

        WHERE "Dataset_Name" = 'Name'

        AND SUBSTR("Date_For_Entry",1,9) <> SUBSTR(CURRENT_TIMESTAMP,1,9)

        )

    WHERE ROWNUM_ = 1)

as is and seeing if it returns what you think it should return.

[MithilaVR]

So this is what i did:

I commented everything from my query except "select year from metric_log" it still gives identifier too long. 

I think my Alteryx has gone bonkers on the top floor.

[apathetichell]

put that in a new query and see what you get. 

this response - 

sorry - let me rephrase - try getting rid of the inline comments (no idea why it would affect it but it seems like the only major difference between the two queries). - was trying to say test if  Alteryx can parse your comments and if the comments are the source of your errors.

[MithilaVR · Accepted solution]

it worked after I removed all the spaces between select year except one space. 

so instead of :

"Select

      YEAR, <blah blah>"

I did "SELECT YEAR, <blah blah>"

this is so weird

. @apathetichell Can you explain what happened and why it would error on some extra spaces? 

[apathetichell]

Are you using input data? or In-DB? it sounds like there was a character that Oracle didn't like being fed in to your query.

[MithilaVR]

I am using input-data tool. 

it worked fine in toad. it didnt give any errors when I ran it in toad. or even sql developer.