如何将数字替换/转换为字符串

Question

我有一个查询运行良好，返回-1:

SELECT -1 FROM (select SUM(column1), column2 
from documents where column2 is NULL group by column2) aa
where aa.column2_rid = rid and typ_doc in (4,2) and rownum = 1

但我想将-1转换/替换为字符串->'no_doc'

如果我使用replace，它会返回0

我想，因为这是一个数字。。。。。

NVL不工作。To_char(-1)也不起作用。或者我失败了。

SELECT REPLACE('-1', '-1', 'no_doc') FROM ....
output: 0
SELECT REPLACE(-1, -1, 2) FROM .... 
output: 2

我怎样才能达到预期的产出？('no_doc'(

Answer 1

这将起作用：

select decode((select -1 from dual),-1,'no_doc','anything') from dual;

查询：

select decode((SELECT -1 FROM (select SUM(column1), column2 
from documents where column2 is NULL group by column2) aa
where aa.column2_rid = rid and typ_doc in (4,2) and rownum = 
1),-1,'no_doc','anything') from dual;

Answer 2

为什么不能在查询中使用'no_doc'而不是-1？

select 'no_doc'
from (select SUM(column1), column2 
from documents
where column2 is NULL
group by column2
) aa
where aa.column2_rid = rid and typ_doc in (4,2) and
rownum = 1;

Answer 3

您也可以使用NVL2函数

SELECT NVL2(TO_CHAR(-1),'no_doc','Anyhing') FROM (
select SUM(column1), column2 from documents where column2 is NULL group by column2) aa
where aa.column2_rid = rid and typ_doc in (4,2) and rownum = 1