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]

still now working that.. I tried to build the whole thing in SQL and there I got an error with the collation... in sql I could solve it by simply potting 'COLLATE DATABASE_DEFAULT' in the join clause.... in alteryx of course is another story

[apathetichell]

Going back to this - this thread - https://www.mssqltips.com/sqlservertip/4395/understanding-the-collate-databasedefault-clause-in-sql-... make me believe you can cast your join fields to match (perhaps with formula in-db using your join tool?) I don't use SQL_SERVER - but I believe I mentioned (in the second response on this thread) field types not matching (in this case it would be format of the field types) could cause the behavior you were seeing. I think the key is to know the format of the fields you are joining (and modify the fieldtype of the table that is permanent by changing format/casting in your query).