Alteryx duplicating rows in SQL query

[Joker_Hazard]

I have this query:

The result should have been ONLY two rows, however Alteryx is giving me literally 100.000 rows. (Using the "record limit = 100.000" in input tool.)

Example: 

Unique tool:

How can this be possible? I have already ran the same query on TOAD and different programs and this does not happen.

I am unable to use in-database tools due to company rules.

Any input ins appreciated. Thanks!

[AbhilashR · Accepted solution]

Hi @Joker_Hazard, the results of an Input tool in Alteryx querying an Oracle db (am assuming it is Oracle) should give you the same results as any other application (e.g. TOAD). You mention running the same query in Alteryx and TOAD but getting different results, can you once again check and confirm if they are the same SQLs?

Separately, try using the Group By function in your SQL to remove duplicates.

[mst3k]

See the results for your ID=169647  screenshot with 2 rows - the CARTEIRA and ID_TITULO are different, at the very least.

If that's not the result you expect, the issue must be in the SQL query somewhere. Alteryx doesn't really do anything except pass the SQL off to the server, so it should give the same results as running it in TOAD, SSMS, SQL Developer, etc

[danilang · Accepted solution]

Hi @Joker_Hazard 

You're getting a CROSS Join somewhere.

Try using one of the different drivers, OCI, ODBC, or OLDDB to see if you get different results. 

You can also try rewriting your query using the ON syntax for the joins i.e.

Select t1.ID, t2.name,t1.pay from table1 t1

   Join table2 t2

       on t1.ID = t2.ID 

It shouldn't make a difference but possibly the combination of Alteryx passing the query which is interpreted by the driver is causing the X Join.

Dan

[mmtouma]

Hi!  I am having the same issue.  Results truncate in Alteryx despite using the same TOAD SQL.  As a test, I went as basic as I could and the gap was still there.  I was under the impression that this was a passthrough to the server (as mentioned in this thread).  Any help would be appreciated- I did try another ODBC connection, same results- thanks!