SQLPrepare: schema object identifier error

[uahmed90]

Does anyone know what this error means? It gives me no other explanation other than: Data Stream Out (58) Error SQLPrepare: SQL compilation error: schema object identifier. I have no clue what the issue is, all the connections are correct, I have many other in-db connections on this workflow all work fine, except for this one. The query runs fine when running it on snowflake too, so I just don't see what the problem is. Anyone encounter this before? 

[JimmyV]

Hi Uahmed90,

I wonder if it's an issue with the Snowflake query not having the Schema listed in the FROM clause or if it's because the columns / fields don't match up to the previous tool's schema? But I would look to those two and just confirm those if you haven't already done so.

[uahmed90]

That was the first thing I thought of as well, but I do have schema listed with every table name. I checked all the columns and fields as well, everything matches up. The same query works if I use the normal input and output tools, but when using In-DB, I get this error. very odd. 

[JimmyV]

Yeah, very strange! What's the error message in the Browse In-DB tool?

[uahmed90]

Same exact error. I can't even see any rows or nothing. 

[JimmyV]

Hmm... In the upstream Select In-DB tool are there any fields that are no long part of the query or the Data Stream Out tool? I'm almost thinking if everything is looking good, maybe copy the query and try to build out the first few tools in a new workflow and see if we're still getting the error?

[uahmed90]

Did that as well. Was thinking maybe it a cache thing, so I'll build it out piece by piece again. restarted my computer as well for good measure, new workflow, still, same error. 

[JimmyV]

We're definitely thinking the same troubleshooting efforts here. Are you limited to using an Connect In-Db tool or are you able to use an Input Data tool?

[uahmed90]

yeah, right now its working with input data tool, just not with In-DB tool. Don't know why. The reason I wanted to use In-DB tool is once I complete this current migration project, I'm expecting a couple millions rows in each load, so In-DB would work out better performance-wise. 

[apathetichell · Accepted solution]

You cannot use CTE (ie WITH) with In-DB tools.  (https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/common-table-expressions-in-da...)

I would recommend changing this to a sub-query if possible.