Oracle DB read creating extra rows of data

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.

Topic of Interest

Accepted answers

Tyro_abc

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

All comments

Tyro_abc

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.

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!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335