Aggregate Behavior

ansinull determines whether NULL-valued operands in aggregate functions are evaluated in compliance with the ANSI SQL standard. If you use set ansinull on, the SAP ASE server generates a warning when an aggregate function eliminates a null-valued operand from the calculation.

For example, if you perform the following query on the titles table with set ansinull off (the default value):
select avg (total_sales) from titles
The SAP ASE server returns:
-----------
       6090

However, if you perform the same query with set ansinull on, the SAP ASE server returns the following:

1> use pubs2
2> go
1> select avg (total_sales) from titles
2> go
 -----------
         6090
 (1 row affected)
1> set ansinull on
2> go
1> select avg (total_sales) from titles
2> go
 -----------
         6090
Warning - null value eliminated in set function
(1 row affected)

This message indicates that some entries in total_sales contain NULL instead of a real amount, so you do not have complete data on total sales for all books in this table. However, of the available data, the value returned is the highest.