SQLPrepare: schema object identifier error

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?

In Database

In-Database

Accepted answers

apathetichell

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

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

All comments

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

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

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

uahmed90

Oh wow. it was that simple huh. makes the ETL flow more complex since I have to pull out of 3 separate tables and join/union, but I guess it works fast enough. Thanks! good to know.

apathetichell

If it's always 3 tables - and fairly set - I'd recommend three different in-db tools or dynamic in-db) then a Union In-DB.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177