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?

[cjaneczko]

Can you include your entire SQL statement? 

[apathetichell]

Can you confirm that 1) the two DBS you are connecting in the Join statement can reach each other (ie they are in the same Database/connection). 2) Can you check the types of the join field (ie string/number etc) and confirm they are both the same.

what is your underlying db?

[geraldo]

@camilacb 

do the join with the in-out tool between these two tools and check what could be happening.
It may happen that the temporary table has fields with different data types. Compare data types

[camilacb]

yes, and in fact if i do the join using JOIN tools (for a sample) it allows me and it works, so it seems to be the in database function

[camilacb]

for table A:

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

Table B:

is a temporal table from an excel file with more than 1 million rows, I put a sample below.

The join is done on two variables: BvD_ID_number and year(Closing_date) = signature date

[apathetichell]

and you can confirm that you aren't modifying any of the field types prior to joining out of the db? can you turn off amp?  and I don't think you menitoned what DB you are using.

The only other issues I can think to look at would be a) are you sure the location you are writing your temp table is reachable for your other db in your join? b) do you have write access to your db at the location you are writing your temp table? c) is there something wrong with the way alteryx is writing the temp table. 

If you can confirm the Db locations can connect - Can you disable the join and add a browse in-db after the datastream-in (ie let's focus on if the datastream-in is actually doing what it should).

[cjaneczko]

Are the two tables in the same DB, are you able to write the join right in the first Connect In-DB tool? 

[camilacb]

yes, they are in the same DB, but i cannot join nor left or right... on the other side the join function in alteryx is working so is the indatabase join that is giving me problems...

[camilacb]

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...