In-DB join on self results in error: ORA-01722: invalid number

[joejoe317]

This was working last week and then it wasn't. I have isolated this down to a point where I have to sum some deal id's then join back in the data.

I noticed last week I was getting errors when joining one table in 2 different areas in my flow.

I copied out the alteryx sql query and get the same error in oracle developer. 

If I pull the query before the join it is fine. If I pull the data after the sum it works.

After the join it does not.

If I save off the summation into a table, then join it works...

Does a join on itself not supposed to work, even though just was last week it was?

Pardon my ignorance I am brand new to in-db for the most part... here is a screen shot of where it fails.

In oracle developer I chipped away at the sql and got to the point before the join, both streams work fine, but when I do the join they do not... Chipping away at this revealed where it said "tablexyz" invalid identifier "DEAL_ID"... but literally if I delete that, it just moved onto the next field saying invalid identifier. It's almost like the "TABLEXYX" AS ( select deal_id,. .. etc) is not actually creating anything even though the commands to do so are there.

Again, if I save the summation off into it's own table and do the join on that it works just fine. I do not see any issues with numeric conversions etc.

My join is on "Deal_Id" which is a string, not a number.

[danilang]

hi @joejoe317 

Can you share the SQL that Alteryx generated?  

Dan

[joejoe317]

Let me go through it first so I can redact or change things.

[joejoe317 · Accepted solution]

I was able to get it to work. 

I have about 3 or 4 other joins before I get to this one. 

I added it to the beginning, and now it is working. 

Not sure what is specifically going on, but the behavior indicates that the "TableXYZ" (failure point)  either does not exist, or simply the process isn't catching up to this part of this when it is running in parallel, so technically it exists but does not have any metadata when it tries the join. Which is maybe why it is saying invalid number on the join.

I simply just added the join (was for product) to the beginning right after the fact table, and everything went through.