Join using in-database functions

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?

Screenshot 2024-03-14 174308.png

Error Message

Connectors

In Database

Database Connection

In-Database

Accepted answers

All comments

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

SAMPLE.xlsx

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

apathetichell

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 (sam Screenshot 2024-03-19 092430.png e 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...

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

Screenshot 2024-03-19 164552.png

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?

camilacb

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

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

testjoin.yxmd

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-server/ 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).

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703