Oracle DB read creating extra rows of data

[pasta_enthusiast]

Hi all,

I'm pretty new to both Alteryx and Oracle, so please bear with me here! Essentially, I am trying to simulate querying an Oracle DB on automatic time intervals and updating a separate Oracle DB with the pulled data. My problem is that when I read in the tables as input using the following code:

select Table1.*, Table2.*, Table3.*, Table4.* from Table1, Table2, Table3, Table4

Alteryx seems to be reading it in all as one huge table, matching every record in one table to every record in each of the other tables, thusly spawning millions of data points. 

I've tried reading each table from the DB in separately and using the Append Fields node to join the tables, but I ultimately get the same results. Is this some issue with how I'm joining the tables, or is there some way that I can have 4 separate input tables without needing to join them? Any help is greatly appreciated.

For reference, the "dummy" DB I set up in Oracle consists of 4 tables, each with 50 rows of made-up data, and there are no dependencies set up between tables. Ideally, I would like for Alteryx to interpret this as four tables of 50 records each, meaning a maximum of 200 records.

[Tyro_abc · Accepted solution]

Hi

When you write 

SELECT TAB1.* , TAB2.* FROM TAB1,TAB2

 most databases would do a cartesian join and bring you m * n entries.  

     Where m = number of entries in Tab1 and

                 n = number of entries in Tab2.

In your case, you are most likely getting 50*50*50*50 = 6,250000 records. If these tables are independent, you can use a different "Input Data" node for each table. "Append Fields" does the same thing in alteryx, so you would get same result as what you are getting from Oracle.

Regards

Arundhuti

[grazitti_sapna]

Hi @pasta_enthusiast,

Writing SQL query as " Select t1*, t2* from t1, t2" would make Alteryx think you are doing cross join, which is basically the cartesian product and hence you will end up having the Cartesian product of both the tables.

Append tool is basically used to do cartesian product in Alteryx. So let's suppose you have 2 records in one table and 8 in the other one, after using append tool you would end up with 16 records in output. So I suggest you use different input data tools for separate tables.

[pasta_enthusiast]

Thank you for the explanation! I ended up using multiple join and union nodes to get all the fields together after importing each table from the DB separately. Works perfectly now!