SQL nvl 等效 - 没有 if/case 语句和 isnull 和合并
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/451060/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL nvl equivalent - without if/case statements & isnull & coalesce
提问by CheeseConQueso
Are there any nvl() equivalent functions in SQL?
SQL 中是否有任何 nvl() 等效函数？
Or something close enough to be used in the same way in certain scenarios?
或者在某些情况下足够接近以相同方式使用的东西？
更新：
没有 if 语句
没有 case 语句
没有 isnull
没有合并
select nvl (purge_date,"SODIUFOSDIUFSDOIFUDSF") from id_rec where id=36581;
(expression)
SODIUFOSDIUFSDOIFUDSF
1 row(s) retrieved.
select isnull (purge_date,"SODIUFOSDIUFSDOIFUDSF") from id_rec where id=36581;
674: Routine (isnull) can not be resolved.
Error in line 1
Near character position 8
select coalesce (purge_date,"SODIUFOSDIUFSDOIFUDSF") from id_rec where id=36581;
674: Routine (coalesce) can not be resolved.
Error in line 1
Near character position 8
select decode(purge_date, NULL, "01/01/2009", purge_date) from id_rec where id=74115;
800: Corresponding types must be compatible in CASE expression.
Error in line 1
Near character position 57
回答by BradC
回答by bobwah
SQL Server: IsNull or COALESCE http://msdn.microsoft.com/en-us/library/ms184325.aspx
SQL Server：IsNull 或 COALESCE http://msdn.microsoft.com/en-us/library/ms184325.aspx
Sybase: isnull function http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks162.htm
Sybase：isnull 函数 http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks162.htm
Postgres: I couldn't find one though haven't fully checked. Suggests to select where IS NULL and build from here http://archives.postgresql.org/pgsql-sql/1998-06/msg00142.php
Postgres：虽然没有完全检查过，但我找不到。建议选择哪里是 NULL 并从这里构建 http://archives.postgresql.org/pgsql-sql/1998-06/msg00142.php
DB2 - COALESCE http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000780.htm
DB2 - COALESCE http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000780.htm
回答by RET
The problem with your DECODE statement that is generating the 800 error is simple. '01/01/2009'is being treated as a string, and it's actually the 4th argument that generates the error.
生成 800 错误的 DECODE 语句的问题很简单。'01/01/2009'被视为字符串，它实际上是产生错误的第四个参数。
Appreciate that the input and output of a DECODE statement can be different data-types, so the engine requires you to be more explicit in this case. (Do you want purge_datecast as a string or the string '01/01/2009', or the string argument parsed as a date or the original date? There's no way for the engine to know.
请注意 DECODE 语句的输入和输出可以是不同的数据类型，因此引擎要求您在这种情况下更加明确。（你想转换purge_date为 string 还是 string '01/01/2009'，或者将 string 参数解析为日期或原始日期？引擎无法知道。
Try this:
尝试这个：
SELECT DECODE(purge_date, NULL, '01/01/2009'::DATE, purge_date)
You could also write that 3rd argument as:
您也可以将第三个参数写为：
DATE('01/01/2009')
MDY(1,1,2009)
depending on version and personal preference.
看版本和个人喜好。
回答by Quassnoi
You seem to be using Informix.
您似乎在使用 Informix。
AFAIK, there is DECODE there:
AFAIK，那里有解码：
DECODE(field, NULL, 'it is null, man', field)should give you same result as NVL(field, 'it is null, man')
DECODE(field, NULL, 'it is null, man', field)应该给你相同的结果 NVL(field, 'it is null, man')
Please post exact name and version of the RDBMS you are using.
请发布您正在使用的 RDBMS 的确切名称和版本。
