英文:
Oracle SQL Case with Null
问题
我有一个简单的查询，它从一个绑定变量中获取输入。
CREATE TABLE "FRUITS"
( "FRUIT_NAME" VARCHAR2(100),
"COLOR" VARCHAR2(100)
) ;
insert into fruits (fruit_name, color)
values ('Banana', 'Yellow')
insert into fruits (fruit_name, color)
values ('Lemon', '')
insert into fruits (fruit_name, color)
values ('Apple', 'Red')
SELECT * FROM FRUITS
WHERE
COLOR = case
when :P1_ITEM is null then null
else :P1_ITEM
end
如果输入为'Yellow'，结果将是'Banana'（如果为'Red'，则为'Apple'）。然而，如果输入恰好为null，结果将是'未找到数据'。
如何避免这种情况，因为null不是null值呢？
如果颜色为空，那么如何返回颜色为空的行？也就是'Lemon' + null
谢谢
英文:
I have a simple query that is taking an input from a bind variable.
CREATE TABLE "FRUITS"
( "FRUIT_NAME" VARCHAR2(100),
"COLOR" VARCHAR2(100)
) ;
insert into fruits (fruit_name, color)
values ('Banana', 'Yellow')
insert into fruits (fruit_name, color)
values ('Lemon', '')
insert into fruits (fruit_name, color)
values ('Apple', 'Red')
SELECT * FROM FRUITS
WHERE
COLOR = case
when :P1_ITEM is null then null
else :P1_ITEM
end
If the input is 'Yellow' the result would be 'Banana' (when 'Red' then 'Apple'). However, if the input happens to be null the result is 'no data found'.
How can this be avoided knowing that null is not a null value?
If the input is null on color then how can I return the null color row? meaning 'Lemon' + null
Thanks
答案1
得分: 1
以下是翻译好的部分：
"Something like this might be one option:
SELECT * FROM FRUITS
WHERE
nvl(COLOR, 'x') = case
when :P1_ITEM is null then 'x'
else :P1_ITEM
end;"
英文:
Something like this might be one option:
SELECT * FROM FRUITS
WHERE
nvl(COLOR, 'x') = case
when :P1_ITEM is null then 'x'
else :P1_ITEM
end;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论