PL SQL与空值进行比较 [英] PL SQL comparing against null values

问题描述

您好，



我正在使用API​​提供给我的桌子，我必须使用我所拥有的：< br $>


产品



- PRODUCT_ID

- PRODUCT_NAME

- PRODUCT_DESC

- PRODUCT_CODE

- PRODUCT_TYPE



所以产品代码和产品类型值可以为null。在理想的情况下，我希望这些列总是填充，但不幸的是它们不是，这是我无法控制的。



首先，用户提供ProductCodes列表一个代码的值为UNKNOWN。我想使用此值并将其与Product_Code列进行比较，其中该列的值为NULL



其次，用户提供ProductTypes列表和其中一个值是UNKNOWN，我正在尝试将此值与空值相匹配。



任何想法如何实现这一目标？我很欣赏有关实例的建设性反馈。



我的尝试：



我试图构建一个查询来做我想要的但我相信我错过了一些查询，我不知道我错过了什么：



目前这是我正在使用的SQL，但它没有返回所需的结果：

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:

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')

推荐答案

使用 COALESCE 是一个选项，只要您选择一个不存在的值，它就可以正常工作原始数据。否则你可能会得到错误的结果，具体取决于逻辑。



另一种选择（也许是一种典型的方式）是使用 IS [NOT] NULL 关系运算符。如果你想包含 NULL 值，那么就像

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

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.

我通过修改我的查询来实现此目的：

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

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.

这篇关于PL SQL与空值进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！