Click here to Skip to main content
15,506,599 members
Please Sign up or sign in to vote.
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
Updated 15-Oct-16 23:21pm

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.
 
Share this answer
 
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.
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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900