本文介绍了PL SQL与空值进行比较的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 您好, 我正在使用API​​提供给我的桌子,我必须使用我所拥有的: 产品 - 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_TYPESo 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 columnSecondly 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 likeSELECT COALESCE(PRODUCT_CODE,'UNKNOWN') AS PRODUCT_CODE, COALESCE(PRODUCT_TYPE, 'UNKNOWN') AS PRODUCT_TYPEFROM PRODUCTSWHERE ( 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_TYPEFROM PRODUCTSWHERE COALESCE(PRODUCT_CODE,'UNKNOWN')IN ('ABGC','89ABHS','UNKNOWN') ANDCOALESCE(PRODUCT_TYPE, 'UNKNOWN') IN ('BEDDING','UNKNOWN','SPORTSWEAR'); 希望这有助于任何尝试做类似我的事。Hope this helps for anyone trying to do something similar to me. 这篇关于PL SQL与空值进行比较的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
05-27 08:34
查看更多