In-Database Errors

[Bonediggler]

Experts -

I have a little Oracle sql statement here that works just fine in an MS Access pass-through query but continuously throws the "Error: ORA-00972: identifier is too long" error via the Connect In-DB tool.

Any idea why and/or the solution?

Thanks!

[MarqueeCrew]

I wonder what is really at play here too.  If you use the DYNAMIC OUTPUT IN-DB tool you can see the SQL.  Google says that the error is that the field name that you are creating is more than 30 characters long.

ORA-00972: identifier is too long tips. Cause: An identifier with more than 30 characters was specified. Many users find themselves encountering ORA-00972when attempting to access a table that has a large column length. Furthermore,ORA-00972 can be caused by column, alias, or table names which are too long.

Cheers,

Mark

[Bonediggler]

I googled that too. 

It doesn't make sense though because it is the same sql hitting the same db and generating the error via one application but not another....

[jwalder]

Just spit balling, but is it possible that the query isn't the exact source of the error? By that I mean the the In-DB tools basically represent CTEs and maybe the references created in the CTE are in fact where the issue is? Does it happen when the only thing in the workflow is the Connect In-DB and a data-stream out?

[Bonediggler]

Good question - I just noticed the CTE created via the Data Stream Out is not wrapped by parens ().  So it is saying "select * from select field1, field2............." and a really long statement, which is probably being interpreted as a table name and thus throwing the error...?

[Bonediggler · Accepted solution]

Think I got it here.  I was copying SQL from a Cognos report and pasting to the query builder.  Apparently there wasn't a space after "SELECT" which probably threw off Alteryx's attempt to parse the SQL into its various components and resulted in a deformed CTE.

Anyway, thanks for the help!