community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

In-Database Errors

Asteroid

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!

Alteryx Certified Partner
Alteryx Certified Partner

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Asteroid

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

Alteryx Partner

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?

Asteroid

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

Asteroid

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!

Labels