Join on field X Causes Error: ORA-00904: "R_Status": invalid identifier

[msbs48]

All In-DB Controls

Left outer Join on "Status".   "Status" is in left and right columns.  Alteryx in Config Tool just after join creates "R_Status" Column )

A formula tool right after the Config tool does a Case statement (nothing to do with Status or R_status) and it fails with Error: ORA-00904: "R_Status": invalid identifier

I put a browse in DB BEFORE the Formula coming out of the Config tool and it fails too with the same error  -> FROM "Tool22_67d9" WHERE ROWNUM <= 100" Error: ORA-00904: "R_Status": invalid identifier

Tool 22 is the Config tool is the same config tool after the join.

What do i do.... i tried deleting tools and re-adding

[msbs48]

Mark...sent you a private message reply... ben

[MarqueeCrew · Accepted solution]

@msbs48 (aka Ben),

That was a problem for a Monday and not a Friday!  I'm glad that we were able to solve the issue and as promised, here's my recap:

1. ORA Error was "caused" by an IN DB Join where alteryx :( created a R_STATUS field (rename from join on L & R STATUS fields).
2. Several SELECT Tools needed to be reconfigured.  The XML and SQL had "Title Case" instead of UPPERCASE.

The fix was to rename (via select) the incoming R-join data as R_STATUS before it got to the Join tool (where we found the source of the field).

I found the clue when seeing lowercase "R_Status" in the error msg as an invalid identifier and seeing it (R_STATUS) in uppercase previously.  I didn't really think much of it until I read a post in stackoverflow (http://stackoverflow.com/questions/6027961o/ora-00904-invalid-identifier).

This is the text that stuck out to me:

Oracle SQL allows us to ignore the case of database object names provided we either create them with names all in upper case, or without using double quotes. If we use mixed case or lower case in the script and wrapped the identifiers in double quotes we are condemned to using double quotes and the precise case whenever we refer to the object or its attributes:

After we solved the issues, I showed you how to create instructions for the macro (essentially a complex IN DB process) that outputs data.  Then we placed the macro in the runtime directory and the macro magically appeared on your toolbar in the macro tab.  I think that if you put it into a directory that you configure within options - macros, that it will do the same.

I will point this article out to someone very special at Alteryx who might reach out to you for more information.

Cheers,

Mark