PL SQL comparing against null values

Question

0.00/5 (No votes)

See more:

Hi there,

I am working with a table which has been provided to me by an API and i have to work with what i've got:

PRODUCTS

- PRODUCT_ID
- PRODUCT_NAME
- PRODUCT_DESC
- PRODUCT_CODE
- PRODUCT_TYPE

So the product code and and product type values can be null. In an ideal scenario i wish these columns were always populated but unfortunately they are not and this is out of my control.

Firstly, the user is providing a list of ProductCodes and one the codes has a value of "UNKNOWN". i want to use this value and compare it against the Product_Code column where the value is NULL for this column

Secondly the user is providing a list of ProductTypes and one of the value is "UNKNOWN" and i am trying to match that on the null value as well for this column.

Any ideas how i could achieve this? I appreciate constructive feedback with examples.

What I have tried:

I have attempted to construct a query to do what i want but i believe i am missing something from the query and i do not know what i have missed:

Currently here is my SQL that i am using but its not returning the desired results:

SQL

SELECT PRODUCT_ID, NVL(PRODUCT_CODE, 'UNKNOWN'), NVL(PRODUCT_TYPE, 'UNKNOWN')
from PRODUCTS where PRODUCT_CODE IN('ABGC', '89ABHS', 'UNKNOWN') and PRODUCT_TYPE in('BEDDING','UNKNOWN','SPORTSWEAR')

Posted 15-Oct-16 6:58am

Eagle32

Updated 15-Oct-16 22:21pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2016-10-15T22:21:00

Solution 2

Using COALESCE is one option and works fine as long as you choose a value that cannot exist in the original data. Otherwise you could have false results, depending on the logic.

Another option (perhaps a typical way) is to use IS [NOT] NULL relational operator. If you want to include NULL values then something like

SQL

SELECT COALESCE(PRODUCT_CODE,'UNKNOWN')  AS PRODUCT_CODE,
       COALESCE(PRODUCT_TYPE, 'UNKNOWN') AS PRODUCT_TYPE
FROM PRODUCTS
WHERE (  PRODUCT_CODE IN IN ('ABGC','89ABHS')
      OR PRODUCT_CODE IS NULL) 
AND   (  PRODUCT_TYPE IN ('BEDDING','SPORTSWEAR')
      OR PRODUCT_TYPE IS NULL);

Note that this also may affect the execution plan created by the optimizer.

Posted 15-Oct-16 22:21pm

Wendelius

Comments

Eagle32 16-Oct-16 12:56pm

Mika, regarding your commen "works fine as long as you choose a value that cannot exist in the original data. Otherwise you could have false results, depending on the logic" can you elaborate on this please? Thanks

Wendelius 16-Oct-16 13:08pm

Consider a situation where the original data would contain a value 'UNKNOWN' for product code. Your code relies on the fact that UNKNOWN isn't found in the original data and this value is always generated becuse of NULL.

Now, if the 'UNKNOWN' would suddenly appear in the original data you would falsely interpret it the same as NULL since you convert NULL to text UKNOWN. In such case your code would return all rows having product code
- ABGC
- 89ABHS
- UNKOWN
- NULL

Because of this comparison with IS NULL or IS NOT NULL is safer since there are no assumptions.

Eagle32 · Answer 2 · 2016-10-15T08:05:00

I got this to work by modifying my query to this:

SQL

SELECT COALESCE(PRODUCT_CODE,'UNKNOWN') as PRODUCT_CODE,
COALESCE(PRODUCT_TYPE, 'UNKNOWN') as PRODUCT_TYPE
FROM PRODUCTS
WHERE COALESCE(PRODUCT_CODE,'UNKNOWN')
IN ('ABGC','89ABHS','UNKNOWN') AND
COALESCE(PRODUCT_TYPE, 'UNKNOWN') IN ('BEDDING','UNKNOWN','SPORTSWEAR');

Hope this helps for anyone trying to do something similar to me.