"WITH" error in Desktop

[Zophia]

Error Code Summary: Formula In-DB (341) Error opening "WITH `Tool374_1d28` AS ... No Columns Returned."

Issue Statement:

I have a large workflow that has been running weekly on server for the past year and was stable. I did no editing, yet last week the workflow failed to run. The error code is incredibly long (200+ words) but the start and end are above. I have verified the field types of the tables have not changed and the databases I am pulling from have had no major updates.

The issue is in the In-DB tools. I do not call any formulas that use the term "with" and have run the database queries in a SQL processor to verify the code is not corrupted. I've shown the workflow to my internal SSA team and they were unsure what could be causing it and told me to come here as it might be an Alteryx thing rather than a my data thing.

I can't share the workflow due to data privacy policies at the company I work for. Any insight on what might throw a "WITH" error would be greatly appreciated. Thank you in advance for your time and energy!

Alteryx Version: 2018.3 x64

[BrandonB]

Hi @Zophia 

The In-DB tools use the "WITH" common table expression to effectively create SQL behind the scenes that is executed In-Database. You can think of this kind of like visual SQL in a sense. 

That being said, there is probably some new data element in one of the tables that is throwing things off. The easiest way to check this is to break your In-DB tools from the workflow into their own smaller workflow parts and add on additional pieces until you hit an error. That way you can isolate the specific operation where things aren't playing nice for some reason. 

[BrandonB]

Also, rather than a data stream out at the end you can use a Dynamic Output In-DB tool to export the actual query if you are wanting to speak to your data team about the actual SQL being leveraged. 

[Zophia]

Great idea! This is what I did when I went to check my code. The team at my company doesn't like the format that the dynamic output tool generates, but it was good enough for me to check the SQL. Thanks for the suggestion!

[Zophia]

I have identified when the error first pops up. Went through each In-DB tool and added them back to the workflow until the error was flagged.

It comes up on a left outer join where the key field is "unique id". The field type is the same for both sides of the join. Additionally, the join is bringing the same DB tables back into themselves, so to me it logically isn't an error coming from the tables themselves. AND the only place in the workflow where the unique id is manipulated, past a Group By within a summarize tool, is where the workflow splits after to then join in on itself.

Any further ideas on how to resolve this?

[BrandonB]

Is it possible that you have null or blank values in that unique id field that is being joined? Maybe try a filter before the join for data on both sides to make sure that the field isn't null and isn't empty prior to joining and see if you can successfully join after doing so. 

[Zophia]

Brandon,

I added a filter for both Null and Empty and it did not fix it. I then took the data on both sides of the join out of DB and checked it in a different format and found no null or blank values. Yet, the join is still throwing the error.

Any other suggestions I can try?

Thanks!

Zophia

[BrandonB · Accepted solution]

Hi @Zophia 

I would suggest booking a quick 30 minutes on the Virtual Solution Center so that someone can take a look at it and work with you directly: https://community.alteryx.com/t5/Virtual-Solution-Center/tkb-p/vsc 

Hopefully its a quick fix!

[Shank]

@Zophia ,

Wish you happy new year!

Did you get a solution for this problem? if yes, then can you help me on who did you fix it.

Best Regards,

Shashank