Join using in-database functions

[camilacb]

Hi,

I have a file with millions of rows that I use it to create a temporal table through Alteryx. Then in the same cloud connection I connected to a table (with terabytes of data) and this was also done using in-database connections.

My next step was to inner join them using more than one clause but it did not work. As you can see both the temporal table and the table I created, both return proper output  and the 'Data Stream Out' executes properly, but after the join I get an error saying:

Error: Data Stream Out (54): Error opening "WITH "Tool1_64e6" AS (SELECT * FROM "##AYX002b4ccf32b2f725fc2570bee08e1f95"), "Tool3_c8dc" AS ([...]), "Tool4_719e" AS (
SELECT TOP 50 * FROM "Tool3_c8dc")  SELECT [...]" No Columns Returned.

I tested with smaller samples and still it does not work... anyone has ever seen this error?

[camilacb]

SQL Server (32/64 bit)

[apathetichell]

o.k. - I'm at the "the only logical reason this would not work is because the two datasets cannot reach each other on the DB" level of thought. I guess just open a ticket with Alteryx? @simonaubert_bd any hypothesis on your end?

[camilacb]

Hi, how can I open a ticket with alteryx? I am quite new with the tool and it is my company that paid for the license so I havent had any interaction with Alteryx team... 

[apathetichell]

Click Support - and scoll down to Support.

[geraldo]

@camilacb 

Have you tried creating a physical table in a schema instead of using a temporary table to check if the error persists.
Execution times are different when using inout with in-db
I advise you to make a version by changing the connection in-db to dynamic input in-db , in a formula you write the sql and the name of the dsn odbc

[camilacb]

I cannot since in the schema I am trying to access I have permissions to create temporal tables but no actual tables... 

[apathetichell]

O.K. I'm going with the - with your permission level - Alteryx will not let you join temp tables to perm tables because your SQL Server is thorwing an error when you try to do this. I'm going with the - this may be a feature of creating a new temp table off of a temp table and a perm table and might be replicated if you tried to do this natively on SQL server. I'm going with the "this is not an Alteryx issue - this ia  SQL Server issue - and your permissions" hypothesis.

@geraldo - thanks for asking the right question.

[camilacb]

Are you sure? I could join my temporal table and the permanent one in SQL platform with the same credentials I input to the Alteryx configuration.. 

[apathetichell]

Set your  ODBC 64 configuration to log at a certain director. Run your workflow. Go to your odbc log file. Inspect the error message at the ODBC level. 

My experience is that Alteryx doesn't make determinations about what is and is not working at the DB level - it just tries to reprint the errors returned by the server. This coudld be an overall driver issue (ie a limiation at the driver level.).

[geraldo]

@camilacb 

try making a version by changing the connection in-db tool with these tools that I have attached and check if the join gives an error