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
Packages The package is a schema object which groups logically associated to the PL/SQL items, types, and subprograms. The Packages have 2 sections: the specification & the bod

%TYPE: This attribute gives the datatype of a formerly declared collection, cursor variable, object, field, record, database column, or variable. Datatype: This is simply

Authorize and fetch data from Instagram Project Description: Incorporate Instagram feed on mobile site platform: c#, ms sql, jquerymobile, jquery Web admin Author

Using Invoker Rights: By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inh

Question: (a) In the context of database security explain how the following database features help to enforce security in the database system: (i) Authorisation (ii) Access

Cause of Indeterminacy in SQL One root cause of indeterminacy in SQL lies in its implementation of comparison for equality. For certain system-defined types it is possible for

Manipulating Objects: You can use an object type in the CREATE TABLE statement to indicate the datatype of a column. When the table is created once, you can use the SQL statem

Logical Connectives - SQL SQL's extended truth tables in which the symbol, for unknown, appears along with the usual T and F. Negation (NOT, ¬) Conjunction (

Using DELETE This process has three forms. The DELETE removes all elements from the collection. DELETE(n) removes the nth element from the nested table. When n is null, then D

Extension and AND in SQL The theory book gives the following simple example of relational extension in Tutorial D: EXTEND IS_CALLED ADD ( FirstLetter ( Name ) AS Initial )