Replicating a SQL query in Alteryx

Question

Hi, We have an old SQL query written for Oracle. I am trying to understand the query and replicate it in Alteryx but having difficulty understanding the need for sub queries. Any help in rewriting these queries using the ANSI92 syntax or rewriting in Alteryx is appreciated.

Here is how the query looks. Both the header and lines table have about 1.5 million rows. All fields used in the query are of type varchar.

SELECT a.*, b.*
FROM header a, lines b
WHERE a.id = (SELECT MIN(a1.id) FROM header a1 WHERE a1.company = a.company AND a1.docnumber = a.docnumber)
AND a.company = b.company AND a.docnumber = b.docnumber
AND b.id = (SELECT MIN(b1.id) FROM lines b1 WHERE a.company = b1.company AND a.docnumber = b1.docnumber)

Kwebb · Answer

Honestly I think your best best is to start from scratch, understand your data structure, figure out what your end goal is and work through it one step at a time without looking at the query.

I'm no SQL expert but that looks like a cartesian join and then a few where clauses that are mainly functioning to exclude bad data points. With 1.5M records in each table that doesn't sound like a very efficient approach... but I also don't know what you're trying to achieve or how your data is structured.

If you're dead set on recreating the query in its exact form - look into using the append fields function. You will then need to limit that append down to only records where company and docnumber match between your target and source. Then, create a summarize off each individual data source that will capture a list of minimum ID grouped by customer and docnumber. You'll then need to join both of those to your appended data to get it down to the necessary records.

On a side note - its also possible to use a query to bring in your data and that may be an option to explore

altstart · Answer

Hi, @BRRLL99 Thank you taking the time to answer. I figured out that this join a.id = (SELECT MIN(a1.id) FROM header a1 WHERE a1.company = a.company AND a1.docnumber = a.docnumber) is unnecessary and I am able to replicate the rest of it without any issues. So, this is no longer an issue.

BRRLL99 · Answer

you can use

Select tool,

Filter tool and

Formula tool