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
Consider the schema for FreeChecking Bank, that we designed given below. Translate the given ER schema into SQL CREATE TABLE statements (indicating primary key, unique and foreign

Recursive Subprograms The recursive subprogram is the one that calls itself. Think of a recursive call as a call to a few other subprograms that does the similar task as your

Entering and Exiting If you enter the executable part of an autonomous routine, the major transaction suspends. When you exit the routine, the major transaction resumes. To ex

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


Calling Constructors: The Calls to a constructor are allowed wherever the function calls are allowed. Similarly to the functions, a constructor is called as a section of an ex

Exceptions An exception is the runtime error or warning condition that can be predefined or user-defined. The Predefined exceptions are raised implicitly through runtime system

Ending Transactions A good quality programming practice is to commit or roll back every transaction explicitly. Whether you rollback or issue the commit in your PL/SQL program

Procedures   The procedure is a subprogram which performs a specific action. You write procedures using the syntax as shown below: PROCEDURE name [(parameter[, parameter, .

EXECUTE Privilege To call an invoker-rights routine straightforwardly, the users should have the EXECUTE privilege on that routine. By yielding the privilege, you permit a user