How to Display NULL and Non-NULL Values in Oracle 19c Using Toad

Описание: Learn how to structure your SQL query in Oracle 19c to achieve the desired output of displaying NULL and non-NULL values based on specific conditions in your data set.
---
This video is based on the question https://stackoverflow.com/q/75689569/ asked by the user 'user16399158' ( https://stackoverflow.com/u/16399158/ ) and on the answer https://stackoverflow.com/a/75689687/ provided by the user 'Littlefoot' ( https://stackoverflow.com/u/9097906/ ) 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 19c/Toad - Need to display NULL and non-NULL values in the result set based on below requirement

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.
---
How to Display NULL and Non-NULL Values in Oracle 19c Using Toad

When working with data analysis, especially in Oracle 19c using Toad, you may encounter situations where you need to display NULL and non-NULL values based on specific conditions. This can often feel daunting if you're new to querying databases. In this post, we’ll address a specific problem where two address columns need to show NULL when certain criteria are not met, and we’ll guide you on how to achieve this effectively.

The Problem Statement

Consider the following:

You need to pull addresses (addr1 and addr2) from Table B.

If the addr_cd in Table A is not 'R', you want the addresses to be displayed as NULL.

If both 'R' and non-'R' values exist for an ID, you want the addresses to display their actual values.

Current logic is resulting in unwanted duplicate values.

Here’s what you’re currently working with in SQL:

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

Despite your efforts, this query yielded duplicate rows. Let's break down the solution.

The Solution Explained

To resolve this issue, we’ll use common table expressions (CTEs) to calculate the count of 'R' and non-'R' occurrences for each ID, and then structure our final selection accordingly.

Step 1: Define the Address and Code Tables

First, set up your data as follows:

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

Step 2: Calculate Counts of 'R' and non-'R'

Next, we compute the counts:

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

This CTE will provide a summary of each ID with counts of both categories.

Step 3: Join and Finalize Data Output

Now, we will join the summarized data with the address data and apply the logic for displaying addresses:

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

Expected Output

This approach yields:

IDs that have both 'R' and non-'R' values will show actual addresses.

If an ID has no 'R' value, the addresses will appear as NULL.

Here’s a quick look at the sample output:

IDADDR1ADDR212Address 2-1Address 2-2Conclusion

By reorganizing your SQL queries using the outlined steps, you can easily manage how null and non-null values are displayed based on business requirements. Utilizing common table expressions to summarize data makes your queries more robust and easier to read, especially for complex requirements.

If you're new to data analysis, don't hesitate to experiment and adjust queries on sample data to see how changes affect the output. Happy querying!