"WITH" error in Desktop

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

Error Message

In Database

In-Database

Accepted answers

BrandonB

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!

All comments

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

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?

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703