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?

[apathetichell]

yes, I can confirm I am not modifying any field. Also yes the temporal table must be reacheable since I created from Alteryx, using a connection configurated there and I could created successfully, it was at the join stage that the error pops up. I added the  add a browse in-db after the creation of the temporal table and the connection to the other table and it was all ok, when i try to add the browse in=db after the join of these two tables is when i get the error...

No - that's not necessarily true. Try to make sure you can datastream in your table - by itself. Isolate this part. Add a browse in-db and confirm that there is no error.

And again - what underlying DB are you using? This is critical.  My next question would be a) can you post your full error message. It truncated. b) is there data with the same fieldname on both sides? Some dbs do not allow for duplicated fieldnames and do not auto-create new names. This will create an error. 

re: b) try renaming 

bvd_id_number

for one of the sides of data (probably the datastream-in side).

[apathetichell]

did you have a chance to test renaming the fields? most dbs will error if there are duplicate fieldnames coming out of a join. Alteryx will not error if you do this on canvas.

[camilacb]

so, the connection is MS SQL connection (same one for both), it is said I work on the cloud and I have permits to create temporal tables there since I have dpne it multiple times. I attached another picture to see that works and where the flow does not execute anymore, as you can see I am able to create a db and fech records, also connect to that same server and fetch records and to download and join them locally but if I try to do the join using in=database is where the error pops up... I already renamed the fields to have exact same name and still nothing... 

[simonaubert_bd]

Hello @camilacb 

I'm very surprised by the ¶ (pilcrow) characters in the query. Can we have a screenshot of the join configuration please?

[camilacb]

i dont see it anymore, this is the error message:

Error: Data Stream Out (20): Error opening "WITH "Tool1_78a5" AS (SELECT * FROM "##AYX7c0ff4f5d1ca4878c64a1ef4211a34c3"), "Tool4_11db" AS (select a.BvD_ID_number,year([Closing_date])[year_Closing_date],[Closing_date]
, [Consolidation_code], [Filing_type],[Number_of_months],
Total_assets

from FinancialsGlobalFormatInclHistoForIndustries.dbo.Industry_Global_financials_and_ratios_EUR a

where Total_assets is not null), "Tool5_c1bc" AS (SELECT TOP 50000000 * FROM "Tool4_11db"), "Tool6_b6c5" AS (SELECT "Tool1_78a5"."strataid", "Tool1_78a5"."year_signature", "Tool1_78a5"."smecountry", "Tool1_78a5"."nace_div2", "Tool1_78a5"."AgeClass", "Tool1_78a5"."age_min", "Tool1_78a5"."age_max", "Tool1_78a5"."N_treated", "Tool1_78a5"."sortid", "Tool1_78a5"."BvD_ID_number", "Tool1_78a5"."date_of_incorporation", "Tool1_78a5"."date_bankruptcy", "Tool1_78a5"."max_closing_year", "Tool1_78a5"."ratio", "Tool1_78a5"."age_year", "Tool1_78a5"."year_1", "Tool1_78a5"."year_2", "Tool5_c1bc"."BvD_ID_number" AS "R_BvD_ID_number", "Tool5_c1bc"."year_Closing_date", "Tool5_c1bc"."Closing_date", "Tool5_c1bc"."Consolidation_code", "Tool5_c1bc"."Filing_type", "Tool5_c1bc"."Number_of_months", "Tool5_c1bc"."Total_assets" FROM "Tool1_78a5" FULL JOIN "Tool5_c1bc" ON "Tool1_78a5"."bvd_id_number" = "Tool5_c1bc"."BvD_ID_number" AND "Tool1_78a5"."year_1" = "Tool5_c1bc"."year_Closing_date"), "Tool8_ec58" AS (SELECT "Tool1_78a5"."strataid", "Tool1_78a5"."year_signature", "Tool1_78a5"."smecountry", "Tool1_78a5"."nace_div2", "Tool1_78a5"."AgeClass", "Tool1_78a5"."age_min", "Tool1_78a5"."age_max", "Tool1_78a5"."N_treated", "Tool1_78a5"."sortid", "Tool1_78a5"."BvD_ID_number", "Tool1_78a5"."date_of_incorporation", "Tool1_78a5"."date_bankruptcy", "Tool1_78a5"."max_closing_year", "Tool1_78a5"."ratio", "Tool1_78a5"."age_year", "Tool1_78a5"."year_1", "Tool1_78a5"."year_2", "Tool5_c1bc"."BvD_ID_number" AS "R_BvD_ID_number", "Tool5_c1bc"."year_Closing_date", "Tool5_c1bc"."Closing_date", "Tool5_c1bc"."Consolidation_code", "Tool5_c1bc"."Filing_type", "Tool5_c1bc"."Number_of_months", "Tool5_c1bc"."Total_assets" FROM "Tool1_78a5" RIGHT JOIN "Tool5_c1bc" ON "Tool1_78a5"."year_2" = "Tool5_c1bc"."year_Closing_date" AND "Tool1_78a5"."BvD_ID_number" = "Tool5_c1bc"."BvD_ID_number") SELECT "strataid", "year_signature", "smecountry", "nace_div2", "AgeClass", "age_min", "age_max", "N_treated", "sortid", "BvD_ID_number", "date_of_incorporation", "date_bankruptcy", "max_closing_year", "ratio", "age_year", "year_1", "year_2", "R_BvD_ID_number", "year_Closing_date", "Closing_date", "Consolidation_code", "Filing_type", "Number_of_months", "Total_assets" FROM "Tool6_b6c5" UNION ALL SELECT "strataid", "year_signature", "smecountry", "nace_div2", "AgeClass", "age_min", "age_max", "N_treated", "sortid", "BvD_ID_number", "date_of_incorporation", "date_bankruptcy", "max_closing_year", "ratio", "age_year", "year_1", "year_2", "R_BvD_ID_number", "year_Closing_date", "Closing_date", "Consolidation_code", "Filing_type", "Number_of_months", "Total_assets" FROM "Tool8_ec58"": No Columns Returned.

[apathetichell]

Can you hook a field info up to your on canvas join tool - and post the results.

It should be:

1) fieldnames are the same in both sources and sql server isn't handling.

2) field types are different (maybe a unit of precision or something).

3) the two datasources cannot touch each other.

to test 3 - create a new data source. call it test. one record. one field (test_field?)

union it to your current data source.

does that work?

[camilacb]

I did it for each data source individually since the join does not work and therefore i cannot run field info... :

 

In any case it seems to me this looks like a bug from Alteryx, it shouldn't be that exactly same data can be properly joined for the join tool not the in-db....

[apathetichell]

Looking that way - but not necessarily.

can you change the right BVD_ID_NUMBER field to TEST and then join BVD_ID_NUMBER to TEST?

[simonaubert_bd]

@camilacbWhat driver do you use? Can we have some details about the configurations? We had some trouble with finding the good one for Hive.

[camilacb]

I just did it and still does not work...