Skip to content
Advertisement

Why does outer reference in SQL subquery produce different results?

I run two SQL queries: The first one have an outer reference to the table inside subquery. In the second one I add the same table inside subquery. The results are different, it fails due to multiple rows.

The first one runs on Oracle, but fails on Spark-SQL. Therefore I am looking for a solution similar to Oracle SQl as in the first SQL code.

Query 1:

Query 2:

The second one is my solution to the first one in Spark SQL, but it fails on both Oracle and Spark. How can I run the first query on Spark SQL similar to Oracle?

Please do not modify the structure of the query.

Advertisement

Answer

Oracle supports multiple inner queries but spark does not. The best way to overcome it is to split your super query into pieces and use join them.

For instance run this part and save it as a table3:

Then use it for your main query:

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement