Problems encountered when using count in conjunction with the NVL function

count () function function: In the Statistics table The number of fields or records in a field, and the value of NULL is not counted.

Explained in the manual:

COUNTReturns the number of rows returned by the query. You can use it as an aggregate or analytic function.

If you specify DISTINCT and then you can specify only the query_partition_clause of the analytic_clause . The and is not order_by_clause windowing_clause allowed.

If you specify expr , then returns the number of COUNT rows where is not expr null. You can count either all rows, or only distinct values of expr .

If you specify the asterisk (*), then this function returns all rows, including duplicates and nulls. COUNTnever returns NULL.

From the manual you can see that the value returned by count () is number, which means that even if a record is not in a table, the return is 0. The NVL () or NVL2 () function determines whether the condition is null

(For function usage See: http://www.cnblogs.com/space-place/p/5151913.html), the application error occurs with NVL (count (*), 1).

Workaround: Use the Decode function.

Example:

1. Create an empty table without inserting any data.

The 2.NVL2 is used in combination with count ():

The error message is returned with no data in table Test2 and 1 returned. The reason is that count (ID) returns 0, and NVL2 () is judged null:

3. Use the Decode () function to resolve:

Problems encountered when using count in conjunction with the NVL function