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
Functions The function is a subprogram which can take parameters and be invoked. Normally, you can use a function to calculate a value. The function has 2 sections: the specifi

UNNEST operator in SQL The inverse operator of GROUP is UNGROUP. SQL has an operator, UNNEST, that can be used for similar purposes, but its method of invocation is somewhat p

Using COMMIT The COMMIT statements end the present transaction and make permanent any changes made during that transaction. Till you commit the changes, other users cannot acc

WHILE-LOOP The WHILE-LOOP statement relates a condition with the series of statements enclosed by the keywords LOOP and END LOOP, as shown: WHILE condition LOOP sequence_of_sta

Using Operator REF: You can retrieve refs by using the operator REF that, like VALUE, takes as its argument a correlation variable. In the illustration below, you retrieve one

Updating Objects: To change the attributes of objects in an object table, you can use the UPDATE statement, as the illustration below shows: BEGIN UPDATE persons p SET p

%TYPE Attribute The %TYPE attribute gives the datatype of a record, field, nested table, database column, or the variable. You can use the %TYPE attribute as the datatype speci

Case Sensitivity Similar to all the identifiers, the variables, the names of constants, and parameters are not case sensitive. For illustration, PL/SQL considers the following n

Effects of NULL in Table Literal When a VALUES expression appears as the source value for an SQL INSERT statement, the key word NULL can appear as a field value, such that for

Identifiers You use identifiers to name the PL/SQL program items and units that include constants, variables, cursors, exceptions, cursor variables, subprograms, and packages.