Скачать или смотреть How to Use a Subquery Value in Another Subquery in Oracle SQL

Learn how to effectively pass values between subqueries in Oracle SQL using Common Table Expressions (CTEs) and CROSS JOINs.
---
This video is based on the question https://stackoverflow.com/q/65143118/ asked by the user 'Lisek' ( https://stackoverflow.com/u/3045845/ ) and on the answer https://stackoverflow.com/a/65143389/ provided by the user 'The Impaler' ( https://stackoverflow.com/u/6436191/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Oracle SQL use subquery value in another subquery

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Introduction

Working with Oracle SQL can sometimes be daunting, especially when you're trying to reference values from one subquery within another. One such common scenario arises when you want to use a variable derived from a subquery in a Common Table Expression (CTE), but find that it’s not accessible. If you've ever encountered the ORA-00904: "IN_SERIAL" error, you’re not alone. In this guide, we’ll dissect this issue and walk you through a systematic solution for utilizing subquery values in your SQL statements effectively.

Understanding the Problem

When you are trying to execute a complex query, especially one that involves multiple layers of subqueries, you may want to reference a calculated value from one part of your query in another. For instance, let’s say you want to retrieve a series of financial values tied to a particular serial number, but first, you need to derive this serial number from your main query. This is where the trouble arises: the derived variable doesn't appear to be accessible when you try to use it in your CTE, resulting in the aforementioned SQL error.

Here’s a quick recap of what our user faced:

They had a CTE that was supposed to calculate a value_net based on in_serial.

They wanted to pull in_serial from another subquery, but were unable to reference it in the CTE.

The Solution

The good news is that there is a straightforward way to resolve this issue by leveraging an additional CTE. Here’s how you can do it:

Step 1: Create an Extra CTE for Parameters

The first step is to create a dedicated CTE that retrieves all required parameters. This CTE will encapsulate your main query, delivering a single row result set that can be referenced throughout your SQL script.

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Utilize CROSS JOIN

Next, you will incorporate a CROSS JOIN with the params CTE in your cte_value_net. By doing this, you can freely access the variables defined in the params CTE wherever necessary in your main query:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Complete Your Query

Once your CTE with the parameters is established, you can follow it up with your main query or additional CTEs that require these values. This approach streamlines the use of subquery data across different layers of your SQL logic.

Example Summary

Your complete SQL structure would appear as follows:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By effectively managing parameter references with an additional CTE and using CROSS JOIN, you can overcome the common hiccups that arise during complex SQL queries. This strategy not only organizes your query better but also eliminates errors that stem from inaccessible variables. Happy querying!