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
Defining and Declaring Records To create records, you have to define a RECORD type, and then declare records of that type. You may also define RECORD types in the declarative

Example of Tables within a Table - SQL Example: Obtaining C_ER from COURSE and EXAM_MARK SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM_MARK AS EM

Transaction context As the figure shows, the major transaction shares its context with the nested transactions, but not with the autonomous transactions. Similarly, If one aut

Parameter and Keyword Description: SQL: This SQL is the name of the implicit SQL cursor. %FOUND: This attribute results TRUE if an INSERT, DELETE, or UPDATE state

BIOCHE M ICA L ORIGIN OF LIFE - It is generally agreed by astronomers, geologists and biologists that the earth is approximately 4500-5000 million years old. It is an

Relational Operators and Logical Operators It prepares the ground for subsequent sections in which each specific relational operator is paired with its logical counterpart, su

CLOSE Statement The CLOSE statement allows the resources held by a cursor variable or open cursor to be reused. No more rows can be fetched from the cursor variable or closed

Data Types in SQL - Timestamp TIMESTAMP for values representing points in time on a specified uniform scale. DATE is used for timestamps on a scale of one day, such as DATE '2

Projection in SQL - correct version Student StudentId is enrolled on some course. SELECT DISTINCT StudentId FROM IS_ENROLLED_ON In more complicated examples it is someti

PL SQL Code Review HEADER ELEMENTS File Name Clear, meaningful and descriptive about main objective of the file. Multiple words are joined using underscores which adh