IsNull Function in PeopleSoft Meta-SQL

最新推荐文章于 2025-08-16 23:45:53 发布

转载最新推荐文章于 2025-08-16 23:45:53 发布 · 102 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/lei1016cn/archive/2012/04/29/2475964.html

文章标签：

#数据库

本文深入解析了三种数据库环境下处理NULL值的方法：InMSSQLServer使用ISNULL、OracleDatabase使用NVL以及PeopleSoftMeta-SQL使用%COALESCE函数。通过实例代码展示每种方法的具体应用，并解释其工作原理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

In MS SQL Server, sometimes we will use queries like this to avoid the NULL value to be populated

SELECT ISNULL(TABLE1.COLUMN1,'IT IS NULL')
FROM TABLE1
  

Similarly, in Oracle Database, we can use this:

SELECT NVL(TABLE1.COLUMN1,'IT IS NULL')
FROM TABLE1
  

However, in PeopleSoft Meta-SQL, we shall use the function %COALESCE to do the same thing

SELECT %COALESCE(TABLE1.COLUMN1,'IT IS NULL')
FROM TABLE1
  

Below is the syntax explanation from People Book.

%COALESCE

Syntax

%COALESCE(expr1, expr2, ...)

Description

Use the %COALESCE function to return the first non-null argument provided to the function.

Note. This meta-SQL function is not implemented for COBOL.

Parameters

expr1. . .exprn

Specify the expressions to check.

Note. You cannot specify bind parameters using these expressions.

Example

The following example uses the PRODUCT_INFO table to organize a clearance sale of products. It gives a 10 percent discount to all products with a list price. If there is no list price, the sale price is the minimum price. If there is no minimum price, the sale price is 10.

SELECT product_id, list_price, min_price, %COALESCE(0.9*list_price, min_price, 10)⇒ "Sale" from PRODUCT_INFO where SUPPLIER_ID = 6009;

转载于:https://www.cnblogs.com/lei1016cn/archive/2012/04/29/2475964.html