当没有数据返回时,选择大小写 [英] Select Case, when no data return
问题描述
当我需要验证选择查询的返回是否为空或具有值时,是否可以使用SELECT CASE,解码,nvl或其他查询功能? 例如,我有这个:
it is possible do a SELECT CASE, decode, nvl or another query function when I need verify if the return of a select query is empty or has a value? For example, I have this:
Record | type | id_customer
-------+--------+-------------
1 | T | cus1
2 | A | cus2
3 | T | cus3
4 | | cus4
如果我这样做:
select decode(type,'T','Main','A','Adicional','none') from table where record=1;
我得到Main.
如果我这样做:
select decode(type,'T','Main','A','Adicional','none') from table where record=4;
我什么也没得到.
但是,如果我这样做:
select decode(type,'T','Main','A','Aditional','none') from table where record=5;
我什么也没得到,这是逻辑.因此,当行存在时,我需要获取解码值;如果行不存在,则需要获取文本.
I get nothing, and is logic. So, I need get the decode value when the row exist and a text if the rows no exist.
因此,我尝试使用SELECT CASE,但无法使用COUNT获取值.例如这样的
So, I tried with SELECT CASE but is not posible get a value using COUNT. For example like this:
SELECT
CASE
WHEN count(1)>0 THEN decode(type,'T','Main','A','Aditional','none')
ELSE '-'
END
FROM TABLE WHERE record=5;
得到一个-",如果记录是2,则得到一个-",得到"Aditional".
And get a ' - ', or the same if the record is 2, get 'Aditional'
非常感谢.
推荐答案
您可以在表达式外部使用聚合函数min
或max
:
You can use aggregate functions min
or max
outside expression:
select max(decode(type,'T','Main','A','Aditional','none'))
from table
where record=5;
如果查询返回一行,则获取该行的值.如果查询返回0行,则得到NULL
.
然后,您可以使用nvl
替换NULL
:
If query returns one row, you get value of that row. If query returns 0 rows, you get NULL
.
Then you can replace NULL
using nvl
:
select nvl(max(decode(type,'T','Main','A','Aditional','none')), ' - ')
from table
where record=5;
编辑
另外,如果您需要从多个字符串中选择一个字符串:
EDIT
Also, if you need to choose one string from several:
select decode(max(decode(type,'T', 2, 'A', 1, 0)), 0, 'none', 1, 'Additional', 2, 'Main', null, ' - ')
from table
where record=5;
这篇关于当没有数据返回时,选择大小写的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!