Effects of null in aggregate operator - sql, PL-SQL Programming

Effects of NULL in Aggregate Operator - SQL

Let aggop(x) be an invocation of some aggregate operator aggop in SQL, where x is an expression (usually an open expression) to be evaluated against each row of the table t determined by the context in which the invocation appears. Then aggop considers only those rows that satisfy the condition x IS NOT NULL. It follows that if aggop is EVERY or SOME and x evaluates to TRUE or FALSE for at least one row of t, then the result is either TRUE or FALSE, never UNKNOWN. However, if x evaluates to UNKNOWN for every row of t (which is true in the particular case when t is empty), then SQL's other general rule kicks in, requiring the result to be NULL, which is equivalent to UNKNOWN when it appears in the place of a BOOLEAN value.

That anomaly is to some extent compensated for, when EVERY is used in constraint declarations, by SQL's rule that a constraint is deemed to be satisfied when it evaluates to UNKNOWN. However, (SELECT SOME(TRUE) FROM (tx) AS T) is not reliable as an existence test because it evaluates to UNKNOWN if the result of tx is empty, when a constraint based on that condition would be deemed satisfied. That problem could be addressed by writing COALESCE((SELECT SOME(TRUE) FROM (tx) AS T), FALSE) or, equivalently, (SELECT SOME(TRUE) FROM (tx) AS T) IS TRUE .

Posted Date: 1/18/2013 7:47:54 AM | Location : United States







Related Discussions:- Effects of null in aggregate operator - sql, Assignment Help, Ask Question on Effects of null in aggregate operator - sql, Get Answer, Expert's Help, Effects of null in aggregate operator - sql Discussions

Write discussion on Effects of null in aggregate operator - sql
Your posts are moderated
Related Questions
THEO R Y OF ETERNITY OF LIFE (PRAYER - 1880) - The theory of eternity of life, also called the steady-state theory , states that life has ever been in existence as at presen

Question: (a) The objective of query optimization is to choose the most efficient strategy for implementing a given relational query, thereby improving the system performance. On

Project Description: We organize an online system called ACPAS we have created a project called EVO that can be use by our customers to integrate their web sites with the Acpas

Data Types in SQL - Integer INTEGER or  synonymously INT, for integers within a certain range. SQL additionally has types SMALLINT and BIGINT for certain ranges of integers. T

PITS Depressions in secondary cell wall is called pit. A pit present on the free cell wall surface without its partner is called Blind pit. It consists of 2 parts -

SQL Pseudocolumns The PL/SQL recognizes the following SQL pseudocolumns, that returns the specific data items: LEVEL, NEXTVAL, CURRVAL, ROWID, & ROWNUM. The Pseudocolumns are n

Using FOR UPDATE If you declare a cursor which will be referenced in the CURRENT OF clause of an UPDATE or DELETE statement, you should use the FOR UPDATE clause to obtain an

COSMOZOI C THEORY - Richter (1865) proposed the cosmozoic theory that says that life came by spores (cosmozoa) or other particles from other planets on the earth.

What Is a Package? The package is a schema object that group logically related PL/SQL items, types, and subprograms. The Packages usually have 2 parts, a specification & a bo

Effects of NULL for Table Expression Here's an important distinction between expressions denoting tables and expressions denoting multisets of rows: a table expression cannot