Information Technology - Database Language SQL (Proposed revised text of DIS 9075) July 1992 (Second Informal Review Draft) ISO/IEC 9075:1992, Database Language SQL- July 30, 1992 Digital Equipment Corporation Maynard, Massachusetts Contents Page Foreword.........................................................xi Introduction.....................................................xiii 1 Scope ........................................................ 1 2 Normative references ......................................... 3 3 Definitions, notations, and conventions ...................... 5 3.1 Definitions ................................................ 5 3.1.1Definitions taken from ISO/IEC 10646 ....................... 5 3.1.2Definitions taken from ISO 8601 ............................ 5 3.1.3Definitions provided in this International Standard ........ 5 3.2 Notation ................................................... 7 3.3 Conventions ................................................ 9 3.3.1Informative elements ....................................... 9 3.3.2Specification of syntactic elements ........................ 9 3.3.3Specification of the Information Schema ....................10 3.3.4Use of terms ...............................................10 3.3.4Exceptions .................................................10 3.3.4Syntactic containment ......................................11 3.3.4Terms denoting rule requirements ...........................12 3.3.4Rule evaluation order ......................................12 3.3.4Conditional rules ..........................................13 3.3.4Syntactic substitution .....................................13 3.3.4Other terms ................................................14 3.3.5Descriptors ................................................14 3.3.6Index typography ...........................................15 3.4 Object identifier for Database Language SQL ................16 4 Concepts .....................................................19 4.1 Data types .................................................19 4.2 Character strings ..........................................20 4.2.1Character strings and collating sequences ..................20 4.2.2Operations involving character strings .....................22 4.2.2Operators that operate on character strings and return char- acter strings...............................................22 4.2.2Other operators involving character strings ................23 4.2.3Rules determining collating sequence usage .................23 4.3 Bit strings ................................................26 4.3.1Bit string comparison and assignment .......................27 4.3.2Operations involving bit strings ...........................27 4.3.2Operators that operate on bit strings and return bit strings ............................................................27 4.3.2Other operators involving bit strings ......................27 ii Database Language SQL 4.4 Numbers ....................................................27 4.4.1Characteristics of numbers .................................28 4.4.2Operations involving numbers ...............................29 4.5 Datetimes and intervals ....................................29 4.5.1Datetimes ..................................................30 4.5.2Intervals ..................................................32 4.5.3Operations involving datetimes and intervals ...............34 4.6 Type conversions and mixing of data types ..................34 4.7 Domains ....................................................35 4.8 Columns ....................................................36 4.9 Tables .....................................................37 4.10 Integrity constraints ......................................40 4.10.Checking of constraints ....................................41 4.10.Table constraints ..........................................41 4.10.Domain constraints .........................................43 4.10.Assertions .................................................43 4.11 SQL-schemas ................................................44 4.12 Catalogs ...................................................45 4.13 Clusters of catalogs .......................................45 4.14 SQL-data ...................................................45 4.15 SQL-environment ............................................46 4.16 Modules ....................................................46 4.17 Procedures .................................................47 4.18 Parameters .................................................47 4.18.Status parameters ..........................................47 4.18.Data parameters ............................................48 4.18.Indicator parameters .......................................48 4.19 Diagnostics area ...........................................48 4.20 Standard programming languages .............................49 4.21 Cursors ....................................................49 4.22 SQL-statements .............................................51 4.22.Classes of SQL-statements ..................................51 4.22.SQL-statements classified by function ......................52 4.22.Embeddable SQL-statements ..................................55 4.22.Preparable and immediately executable SQL-statements .......56 4.22.Directly executable SQL-statements .........................58 4.22.SQL-statements and transaction states ......................59 4.23 Embedded syntax ............................................61 4.24 SQL dynamic statements .....................................61 4.25 Direct invocation of SQL ...................................64 4.26 Privileges .................................................64 4.27 SQL-agents .................................................66 4.28 SQL-transactions ...........................................67 4.29 SQL-connections ............................................70 4.30 SQL-sessions ...............................................72 Table of Contents iii 4.31 Client-server operation ....................................74 4.32 Information Schema .........................................75 4.33 Leveling ...................................................75 4.34 SQL Flagger ................................................76 5 Lexical elements .............................................79 5.1 ...................................79 5.2 and ....................................82 5.3 ..................................................89 5.4 Names and identifiers ......................................98 6 Scalar expressions ...........................................107 6.1 ................................................107 6.2 and ...........114 6.3 ..........................................118 6.4 .........................................121 6.5 ...............................124 6.6 ...................................128 6.7 ....................................132 6.8 ..................................139 6.9 ..........................................141 6.10 .......................................144 6.11 .........................................155 6.12 .................................157 6.13 ..................................160 6.14 ................................165 6.15 ................................168 7 Query expressions ............................................173 7.1 ....................................173 7.2
..................................176 7.3
.........................................177 7.4 ..............................................178 7.5 .............................................180 7.6 .............................................185 7.7 ..........................................187 7.8 ............................................189 7.9 ......................................191 7.10 .........................................196 7.11 , , and
....203 8 Predicates ...................................................205 8.1 ................................................205 8.2 .....................................207 8.3 ........................................211 8.4 .............................................212 iv Database Language SQL 8.5 ...........................................214 8.6 ...........................................218 8.7 ..........................220 8.8 .........................................222 8.9 .........................................223 8.10 ..........................................224 8.11 .......................................227 8.12 .........................................229 9 Data assignment rules ........................................231 9.1 Retrieval assignment .......................................231 9.2 Store assignment ...........................................234 9.3 Set operation result data types ............................237 10 Additional common elements ...................................239 10.1 .......................................239 10.2 ..........................................243 10.3 ...............................................245 10.4 ..............................248 10.5 ...........................................251 10.6 and ...252 11 Schema definition and manipulation ...........................255 11.1 ........................................255 11.2 ....................................258 11.3
.........................................260 11.4 ........................................262 11.5 ...........................................266 11.6
..............................270 11.7 .............................272 11.8 ........................274 11.9 ..............................281 11.10 ....................................283 11.11 ....................................284 11.12 ..................................286 11.13 ................................287 11.14 ...............................288 11.15 ...................................289 11.16 ..........................291 11.17 .........................292 11.18 .....................................294 11.19 ..........................................296 11.20 ......................................300 11.21 ........................................301 11.22 ...................................304 11.23 ................................305 Table of Contents v 11.24 ...............................306 11.25 .........................307 11.26 ........................308 11.27 ....................................309 11.28 .................................311 11.29 .............................313 11.30 .....................................314 11.31 .................................318 11.32 ...................................320 11.33 ...............................323 11.34 .....................................325 11.35 .................................328 11.36 ..........................................329 11.37 .........................................333 12 Module .......................................................341 12.1 ...................................................341 12.2 .......................................344 12.3 ................................................346 12.4 Calls to a .....................................352 12.5 ..................................368 13 Data manipulation ............................................371 13.1 ...........................................371 13.2 ...........................................375 13.3 ..........................................377 13.4 ..........................................381 13.5
that identifies a view that is defined by a V, then is said to generally contain the contained in V. If contains , then generally contains . If generally contains and generally contains , then generally contains . An instance A1 of directly contains an instance B1 of if A1 contains B1 without an intervening or . Definitions, notations, and conventions 11 X3H2-92-154/DBL CBR-002 3.3 Conventions 3.3.4.3 Terms denoting rule requirements In the Syntax Rules, the term shall defines conditions that are required to be true of syntactically conforming SQL language. When such conditions depend on the contents of the schema, then they are required to be true just before the actions specified by the General Rules are performed. The treatment of language that does not conform to the SQL Formats and Syntax Rules is implementation- dependent. If any condition required by Syntax Rules is not sat- isfied when the evaluation of Access or General Rules is attempted and the implementation is neither processing non-conforming SQL language nor processing conforming SQL language in a non-conforming manner, then an exception condition is raised: syntax error or access rule violation (if this situation occurs during dynamic ex- ecution of an SQL-statement, then the exception that is raised is syntax error or access rule violation in dynamic SQL statement; if the situation occurs during direct invocation of an SQL-statement, then the exception that is raised is syntax error or access rule violation in direct SQL statement). In the Access Rules, the term shall defines conditions that are required to be satisfied for the successful application of the General Rules. If any such condition is not satisfied when the General Rules are applied, then an exception condition is raised: syntax error or access rule violation (if this situation occurs during dynamic execution of an SQL-statement, then the exception that is raised is syntax error or access rule violation in dynamic SQL statement; if the situation occurs during direct invocation of an SQL-statement, then the exception that is raised is syntax error or access rule violation in direct SQL statement). In the Leveling Rules, the term shall defines conditions that are required to be true of SQL language for it to syntactically conform to the specified level of conformance. 3.3.4.4 Rule evaluation order A conforming implementation is not required to perform the exact sequence of actions defined in the General Rules, but shall achieve the same effect on SQL-data and schemas as that sequence. The term effectively is used to emphasize actions whose effect might be achieved in other ways by an implementation. The Syntax Rules and Access Rules for contained syntactic elements are effectively applied at the same time as the Syntax Rules and Access Rules for the containing syntactic elements. The General Rules for contained syntactic elements are effectively applied be- fore the General Rules for the containing syntactic elements. Where the precedence of operators is determined by the Formats of this International Standard or by parentheses, those operators are ef- fectively applied in the order specified by that precedence. Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from 12 Database Language SQL X3H2-92-154/DBL CBR-002 3.3 Conventions left to right. However, it is implementation-dependent whether ex- pressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression. In general, if some syn- tactic element contains more than one other syntactic element, then the General Rules for contained elements that appear earlier in the production for the containing syntactic element are applied before the General Rules for contained elements that appear later. For example, in the production: ::= the Syntax Rules and Access Rules for , , and are ef- fectively applied simultaneously. The General Rules for are applied before the General Rules for , and the General Rules for are applied after the General Rules for both and . If the result of an expression or search condition can be deter- mined without completely evaluating all parts of the expression or search condition, then the parts of the expression or search condi- tion whose evaluation is not necessary are called the inessential parts. If the Access Rules pertaining to inessential parts are not satisfied, then the syntax error or access rule violation exception condition is raised regardless of whether or not the inessential parts are actually evaluated. If evaluation of the inessential parts would cause an exception condition to be raised, then it is implementation-dependent whether or not that exception condition is raised. 3.3.4.5 Conditional rules Conditional rules are specified with "If" or "Case" conventions. Rules specified with "Case" conventions include a list of con- ditional sub-rules using "If" conventions. The first such "If" sub-rule whose condition is true is the effective sub-rule of the "Case" rule. The last sub-rule of a "Case" rule may specify "Otherwise". Such a sub-rule is the effective sub-rule of the "Case" rule if no preceding "If" sub-rule in the "Case" rule has a true condition. 3.3.4.6 Syntactic substitution In the Syntax and General Rules, the phrase "X is implicit" indi- cates that the Syntax and General Rules are to be interpreted as if the element X had actually been specified. In the Syntax and General Rules, the phrase "the following is implicit: Y" indicates that the Syntax and General Rules are to be interpreted as if a syntactic element containing Y had actually been specified. Definitions, notations, and conventions 13 X3H2-92-154/DBL CBR-002 3.3 Conventions In the Syntax Rules and General Rules, the phrase "former is equiv- alent to latter" indicates that the Syntax Rules and General Rules are to be interpreted as if all instances of former in the element had been instances of latter. If a BNF nonterminal is referenced in a Subclause without speci- fying how it is contained in a BNF production that the Subclause defines, then Case: - If the BNF nonterminal is itself defined in the Subclause, then the reference shall be assumed to be the occurrence of that BNF nonterminal on the left side of the defining production. - Otherwise, the reference shall be assumed to be to a BNF pro- duction in which the particular BNF nonterminal is immediately contained. 3.3.4.7 Other terms Some Syntax Rules define terms, such as T1, to denote named or unnamed tables. Such terms are used as table names or correlation names. Where such a term is used as a correlation name, it does not imply that any new correlation name is actually defined for the denoted table, nor does it affect the scopes of any actual correlation names. An SQL-statement S1 is said to be executed as a direct result of executing an SQL-statement if S1 is the SQL-statement contained in a that has been executed, or if S1 is the value of an referenced by an contained in a that has been executed, or if S1 was the value of the that was associ- ated with an by a and that same is referenced by an contained in a that has been executed. 3.3.5 Descriptors A descriptor is a conceptual structured collection of data that defines the attributes of an instance of an object of a specified type. The concept of descriptor is used in specifying the seman- tics of SQL. It is not necessary that any descriptor exist in any particular form in any database or environment. Some SQL objects cannot exist except in the context of other SQL objects. For example, columns cannot exist except in tables. Those objects are independently described by descriptors, and the de- scriptors of enabling objects (e.g., tables) are said to include the descriptors of enabled objects (e.g., columns or table con- straints). Conversely, the descriptor of an enabled object is said to be included in the descriptor of an enabling object. 14 Database Language SQL X3H2-92-154/DBL CBR-002 3.3 Conventions In other cases, certain SQL objects cannot exist unless some other SQL object exists, even though there is not an inclusion relation- ship. For example, SQL does not permit an assertion to exist if the tables referenced by the assertion do not exist. Therefore, an as- sertion descriptor is dependent on or depends on zero or more table descriptors (equivalently, an assertion is dependent on or depends on zero or more tables). In general, a descriptor D1 can be said to depend on, or be dependent on, some descriptor D2. There are two ways of indicating dependency of one construct on another. In many cases, the descriptor of the dependent construct is said to "include the name of" the construct on which it is de- pendent. In this case "the name of" is to be understood as meaning "sufficient information to identify the descriptor of"; thus an implementor might choose to use a pointer or a concatenation of , , etc. Alternatively, the descrip- tor may be said to include text (e.g., , ). In such cases, whether the implementation includes ac- tual text (with defaults and implications made explicit) or its own style of parse tree is irrelevant; the validity of the descriptor is clearly "dependent on" the existence of descriptors for objects that are referred to in it. The statement that a column "is based on" a domain, is equivalent to a statement that a column "is dependent on" that domain. An attempt to destroy a descriptor may fail if other descriptors are dependent on it, depending on how the destruction is specified. Such an attempt may also fail if the descriptor to be destroyed is included in some other descriptor. Destruction of a descriptor results in the destruction of all descriptors included in it, but has no effect on descriptors on which it is dependent. 3.3.6 Index typography In the Index to this International Standard, the following conven- tions are used: - Index entries appearing in boldface indicate the page where the word, phrase, or BNF nonterminal was defined; - Index entries appearing in italics indicate a page where the BNF nonterminal was used in a Format; and - Index entries appearing in roman type indicate a page where the word, phrase, or BNF nonterminal was used in a heading, Function, Syntax Rule, Access Rule, General Rule, Leveling Rule, Table, or other descriptive text. Definitions, notations, and conventions 15 X3H2-92-154/DBL CBR-002 3.4 Object identifier for Database Language SQL 3.4 Object identifier for Database Language SQL Function The object identifier for Database Language SQL identifies the characteristics of an SQL-implementation to other entities in an open systems environment. Format ::= ::= ::= iso | 1 | iso 1 ::= standard | 0 | standard 0 ::= 9075 ::= ::= <1987> | <1989> | <1992> <1987> ::= 0 | edition1987 0 <1989> ::= <1989 base> <1989 package> <1989 base> ::= 1 | edition1989 1 <1989 package> ::= | ::= 0 | IntegrityNo 0 ::= 1 | IntegrityYes 1 <1992> ::= 2 | edition1992 2 ::= | | ::= 0 | Low 0 ::= 1 | Intermediate 1 ::= 2 | High 2 16 Database Language SQL X3H2-92-154/DBL CBR-002 3.4 Object identifier for Database Language SQL Syntax Rules 1) An of shall not be specified unless the is specified as <1992>. 2) The value of identifies the level at which conformance is claimed as follows: a) If specifies <1992>, then Case: i) , then Entry SQL level. ii) , then Intermediate SQL level. iii) , then Full SQL level. b) Otherwise: i) , then level 1. ii) , then level 2. 3) A specification of <1989 package> as implies that the integrity enhancement feature is not implemented. A specification of <1989 package> as implies that the integrity enhancement feature is implemented. Definitions, notations, and conventions 17 X3H2-92-154/DBL CBR-002 18 Database Language SQL X3H2-92-154/DBL CBR-002 4 Concepts 4.1 Data types A data type is a set of representable values. The logical represen- tation of a value is a . The physical representation of a value is implementation-dependent. A value is primitive in that it has no logical subdivision within this International Standard. A value is a null value or a non-null value. A null value is an implementation-dependent special value that is distinct from all non-null values of the associated data type. There is effectively only one null value and that value is a member of every SQL data type. There is no for a null value, although the keyword NULL is used in some places to indicate that a null value is desired. SQL defines distinct data types named by the following s: CHARACTER, CHARACTER VARYING, BIT, BIT VARYING, NUMERIC, DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION, DATE, TIME, TIMESTAMP, and INTERVAL. Subclause 6.1, "", describes the semantic properties of each data type. For reference purposes, the data types CHARACTER and CHARACTER VARYING are collectively referred to as character string types. The data types BIT and BIT VARYING are collectively referred to as bit string types. Character string types and bit string types are collectively referred to as string types and values of string types are referred to as strings. The data types NUMERIC, DECIMAL, INTEGER, and SMALLINT are collectively referred to as exact numeric types. The data types FLOAT, REAL, and DOUBLE PRECISION are col- lectively referred to as approximate numeric types. Exact numeric types and approximate numeric types are collectively referred to as numeric types. Values of numeric type are referred to as numbers. The data types DATE, TIME, and TIMESTAMP are collectively referred to as datetime types. Values of datetime types are referred to as datetimes. The data type INTERVAL is referred to as an interval type. Values of interval types are called intervals. Each data type has an associated data type descriptor. The contents of a data type descriptor are determined by the specific data type that it describes. A data type descriptor includes an identifica- tion of the data type and all information needed to characterize an instance of that data type. Concepts 19 X3H2-92-154/DBL CBR-002 4.1 Data types Each host language has its own data types, which are separate and distinct from SQL data types, even though similar names may be used to describe the data types. Mappings of SQL data types to data types in host languages are described in Subclause 12.3, "", and Subclause 19.1, "". Not every SQL data type has a corresponding data type in every host language. 4.2 Character strings A character string data type is described by a character string data type descriptor. A character string data type descriptor con- tains: - the name of the specific character string data type (CHARACTER or CHARACTER VARYING; NATIONAL CHARACTER and NATIONAL CHARACTER VARYING are represented as CHARACTER and CHARACTER VARYING, respectively); - the length or maximum length in characters of the character string data type; - the catalog name, schema name, and character set name of the character set of the character string data type; and - the catalog name, schema name, and collation name of the colla- tion of the character string data type. Character sets fall into three categories: those defined by na- tional or international standards, those provided by implemen- tations, and those defined by applications. All character sets, however defined, always contain the character. Character sets defined by applications can be defined to "reside" in any schema chosen by the application. Character sets defined by stan- dards or by implementations reside in the Information Schema (named INFORMATION_SCHEMA) in each catalog, as do collations defined by standards and collations and form-of-use conversions defined by implementations. The SQL_TEXT specifies the name of a character repertoire and implied form-of- use that can represent every character that is in and all other characters that are in character sets supported by the implementation. 4.2.1 Character strings and collating sequences A character string is a sequence of characters chosen from the same character repertoire. The character repertoire from which the characters of a particular string are chosen may be specified explicitly or implicitly. A character string has a length, which is the number of characters in the sequence. The length is 0 or a positive integer. 20 Database Language SQL X3H2-92-154/DBL CBR-002 4.2 Character strings All character strings of a given character repertoire are mutu- ally comparable, subject to the restrictions specified in Table 3, "Collating sequence usage for comparisons". A collating sequence, also known as a collation, is a set of rules determining comparison of character strings in a particular char- acter repertoire. There is a default collating sequence for each character repertoire, but additional collating sequences can be defined for any character repertoire. Note: A column may be defined as having a default collating se- quence. This default collating sequence for the column may be different from the default collating sequence for its character repertoire, e.g., if the is specified in the . It will be clear from context when the term "default collating sequence" is used whether it is meant for a column or for a character repertoire. Given a collating sequence, two character strings are identical if and only if they are equal in accordance with the comparison rules specified in Subclause 8.2, "". The collat- ing sequence used for a particular comparison is determined as in Subclause 4.2.3, "Rules determining collating sequence usage". The s NATIONAL CHARACTER are used to specify a character string data type with a particular implementation-defined character repertoire. Special syntax (N'string') is provided for representing literals in that character repertoire. A character set is described by a character set descriptor. A char- acter set descriptor includes: - the name of the character set or character repertoire, - if the character set is a character repertoire, then the name of the form-of-use, - an indication of what characters are in the character set, and - the name of the default collation of the character set. For every character set, there is at least one collation. A colla- tion is described by a collation descriptor. A collation descriptor includes: - the name of the collation, - the name of the character set on which the collation operates, - whether the collation has the NO PAD or the PAD SPACE attribute, and - an indication of how the collation is performed. Concepts 21 X3H2-92-154/DBL CBR-002 4.2 Character strings 4.2.2 Operations involving character strings 4.2.2.1 Operators that operate on character strings and return character strings is an operator, |, that returns the char- acter string made by joining its character string operands in the order given. is a triadic function, SUBSTRING, that returns a string extracted from a given string according to a given numeric starting position and a given numeric length. Truncation occurs when the implied starting and ending positions are not both within the given string. is a pair of functions for converting all the lower case characters in a given string to upper case (UPPER) or all the upper case ones to lower case (LOWER), useful only in connection with strings that may contain s. is a function that invokes an installation- supplied form-of-use conversion to return a character string S2 derived from a given character string S1. It is intended, though not enforced by this International Standard, that S2 be exactly the same sequence of characters as S1, but encoded according some dif- ferent form-of-use. A typical use might be to convert a character string from two-octet UCS to one-octet Latin1 or vice versa. is a function that returns its first string ar- gument with leading and/or trailing pad characters removed. The second argument indicates whether leading, or trailing, or both leading and trailing pad characters should be removed. The third argument specifies the pad character that is to be removed. is a function for changing each charac- ter of a given string according to some many-to-one or one-to-one mapping between two not necessarily distinct character sets. The mapping, rather than being specified as part of the function, is some external function identified by a . For any pair of character sets, there are zero or more translations that may be invoked by a . A translation is described by a translation descriptor. A translation descriptor includes: - the name of the translation, - the name of the character set from which it translates, - the name of the character set to which it translates, and - an indication of how the translation is performed. 22 Database Language SQL X3H2-92-154/DBL CBR-002 4.2 Character strings 4.2.2.2 Other operators involving character strings returns the length of a given character string, as an integer, in characters, octets, or bits according to the choice of function. determines the first position, if any, at which one string, S1, occurs within another, S2. If S1 is of length zero, then it occurs at position 1 for any value of S2. If S1 does not occur in S2, then zero is returned. uses the triadic operator LIKE (or the inverse, NOT LIKE), operating on three character strings and returning a Boolean. LIKE determines whether or not a character string "matches" a given "pattern" (also a character string). The char- acters '%' (percent) and '_' (underscore) have special meaning when they occur in the pattern. The optional third argument is a charac- ter string containing exactly one character, known as the "escape character", for use when a percent or underscore is required in the pattern without its special meaning. 4.2.3 Rules determining collating sequence usage The rules determining collating sequence usage for character strings are based on the following: - Expressions where no columns are involved (e.g., literals, host variables) are by default compared using the default collating sequence for their character repertoire. Note: The default collating sequence for a character repertoire is defined in Subclause 10.4, "", and Subclause 11.28, "". - When columns are involved (e.g., comparing two columns, or com- paring a column to a literal), by default the default collating sequence of the columns involved is used so long as the columns have the same default collating sequence. - When columns are involved having different default collating sequences, explicit specification of the collating sequence in the expression is required via the when the expression participates in a comparison. - Any explicit specification of collating sequence in an expres- sion overrides any default collating sequence. To formalize this, s effectively have a coercibility attribute. This attribute has the values Coercible, Implicit, No collating sequence, and Explicit. s with the Coercible, Implicit, or Explicit attributes have a collating sequence. Concepts 23 X3H2-92-154/DBL CBR-002 4.2 Character strings A consisting of a column reference has the Implicit attribute, with collating sequence as defined when the column was created. A consisting of a value other than a column (e.g., a host variable or a literal) has the Coercible attribute, with the default collation for its char- acter repertoire. A simply containing a has the Explicit attribute, with the collating sequence specified in the . Note: When the coercibility attribute is Coercible, the collating sequence is uniquely determined as specified in Subclause 8.2, "". The tables below define how the collating sequence and the co- ercibility attribute is determined for the result of any monadic or dyadic operation. Table 1, "Collating coercibility rules for monadic operators", shows the collating sequence and coercibility rules for monadic operators, and Table 2, "Collating coercibil- ity rules for dyadic operators", shows the collating sequence and coercibility rules for dyadic operators. Table 3, "Collating se- quence usage for comparisons", shows how the collating sequence is determined for a particular comparison. _____Table_1-Collating_coercibility_rules_for_monadic_operators____ Operand Coercibility Result Coercibility _____and_Collating_Sequence_____ _____and_Collating_Sequence___ | Collating | Collating | |_Coercibility______Sequence______|_Coercibility______Sequence_____| | | | | Coercible | default | Coercible | default | | | | | | | Implicit | X | Implicit | X | | | | | | | Explicit | X | Explicit | X | | | | | | |_______No_collati|g_sequence_____|______No_collatin|_sequence_____| | | | | | _____Table_2-Collating_coercibility_rules_for_dyadic_operators_____ Result Coercibility Operand 1 Coercibility Operand 2 Coercibility and Collating _and_Collating_Sequence _and_Collating_Sequence ___Sequence___ | Collating | Collating | Col|ating |_Coercibility_Sequence__|_Coercibility_Sequence__|__CoercibilitySe|uence | | | | | Coercible | default | Coercible | default | Coercible| def|ult | | | | | | | | Coercible | default | Implicit | Y | Implicit | Y | | | | | | | | | Coercible | default | No collati|g sequence | No colla|ing | sequence 24 Database Language SQL X3H2-92-154/DBL CBR-002 4.2 Character strings _Table_2-Collating_coercibility_rules_for_dyadic_operators_(Cont.)_ Result Coercibility Operand 1 Coercibility Operand 2 Coercibility and Collating _and_Collating_Sequence _and_Collating_Sequence ___Sequence___ | Collating | Collating | Col|ating |_Coercibility_Sequence__|_Coercibility_Sequence__|__CoercibilitySe|uence | | | | | Coercible | default | Explicit | Y | Explicit | Y | | | | | | | | | Implicit | X | Coercible | default | Implicit | X | | | | | | | | | Implicit | X | Implicit | X | Implicit | X | | | | | | | | | Implicit | X | Implicit | Y /= X | No colla|ing | sequence | Implicit | X | No collati|g sequence | No collating | | | | | | sequence | | | | | | | | Implicit | X | Explicit Y | Explicit Y | | | | | | | No collati|g sequence | Any, | Any | No colla|ing | except sequence Explicit | No collating sequence | Explicit | X | Explicit X | | | | | | | Explicit X | Coercible | default | Explicit | X | | | | | | | | Explicit | X | Implicit | Y | Explicit | X | | | | | | | | | Explicit | X | No collati|g sequence | Explicit | X | | | | | | | | | Explicit | X | Explicit X | Explicit | X | | | | | | | | Explicit | X | Explicit | Y /= X | Not permi|ted:| ____________________________________________________invalid_syntax_ |__________Ta|le_3-Collat|ng_sequence_|sage_for_co|parisons________| Comparand 1 Comparand 2 Coercibility and Coercibility and _Collating_Sequence _Collating_Sequence | | | Collating Sequence | | Collatin| Collatin| Used For The | |_CoercibilitSequence|_CoercibilitSequence|__Comparison____________| | | | | | Coercible| default | Coercible| default | default | | | | | | | | Coercible| default | Implicit | Y | Y | | | | | | | | Coercible| default | No co|lating | Not permitted: invalid| sequence syntax | Coercible| default | Explicit Y | Y | | | | | | Concepts 25 X3H2-92-154/DBL CBR-002 4.2 Character strings ______Table_3-Collating_sequence_usage_for_comparisons_(Cont.)_____ Comparand 1 Comparand 2 Coercibility and Coercibility and _Collating_Sequence _Collating_Sequence | | | Collating Sequence | | Collatin| Collatin| Used For The | |_CoercibilitSequence|_CoercibilitSequence|__Comparison____________| | | | | | Implicit | X | Coercible| default | X | | | | | | | | Implicit | X | Implicit | X | X | | | | | | | | Implicit | X | Implicit | Y /= X | Not permitted: invalid| syntax | Implicit | X | No co|lating | Not permitted: invalid| | | | seq|ence | syntax | | | | | | | | Implicit | X | Explicit Y | Y | | | | | | | No co|lating | Any | Any | Not permitted: invalid| sequence except syntax Explicit | No collating | Explicit | X | X | | sequence | | | | | | | | | | Explicit X | Coercible| default | X | | | | | | | Explicit | X | Implicit | Y | X | | | | | | | | Explicit | X | No co|lating | X | sequence | Explicit | X | Explicit X | X | | | | | | | Explicit | X | Explicit | Y /= X | Not permitted: invalid| ____________________________________________syntax_________________ |For n-adic|operation| (e.g., ) with operands X1, | X2, . . . , n , the collating sequence is effectively determined by considering X1 and X2, then combining this result with X3, and so on. 4.3 Bit strings A bit string is a sequence of bits, each having the value of 0 or 1. A bit string has a length, which is the number of bits in the string. The length is 0 or a positive integer. A bit string data type is described by a bit string data type de- scriptor. A bit string data type descriptor contains: - the name of the specific bit string data type (BIT or BIT VARYING); and 26 Database Language SQL X3H2-92-154/DBL CBR-002 4.3 Bit strings - the length of the bit string data type (in bits). 4.3.1 Bit string comparison and assignment All bit strings are mutually comparable. A bit string is identical to another bit string if and only if it is equal to that bit string in accordance with the comparison rules specified in Subclause 8.2, "". Assignment of a bit string to a bit string variable is performed from the most significant bit to the least significant bit in the source string to the most significant bit in the target string, one bit at a time. 4.3.2 Operations involving bit strings 4.3.2.1 Operators that operate on bit strings and return bit strings is an operator, |, that returns the bit string made by concatenating the two bit string operands in the order given. is a triadic function identical in syntax and semantics to except that the first argument and the returned value are both bit strings. 4.3.2.2 Other operators involving bit strings returns the length (as an integer number of octets or bits according to the choice of function) of a given bit string. determines the first position, if any, at which one string, S1, occurs within another, S2. If S1 is of length zero, then it occurs at position 1 for any value of S2. If S1 does not occur in S2, then zero is returned. 4.4 Numbers A number is either an exact numeric value or an approximate numeric value. Any two numbers are mutually comparable to each other. A numeric data type is described by a numeric data type descriptor. A numeric data type descriptor contains: - the name of the specific numeric data type (NUMERIC, DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, or DOUBLE PRECISION); - the precision of the numeric data type; Concepts 27 X3H2-92-154/DBL CBR-002 4.4 Numbers - the scale of the numeric data type, if it is an exact numeric data type; and - an indication of whether the precision (and scale) are expressed in decimal or binary terms. 4.4.1 Characteristics of numbers An exact numeric value has a precision and a scale. The precision is a positive integer that determines the number of significant digits in a particular radix (binary or decimal). The scale is a non-negative integer. A scale of 0 indicates that the number is an integer. For a scale of S, the exact numeric value is the integer value of the significant digits multiplied by 10-S. An approximate numeric value consists of a mantissa and an expo- nent. The mantissa is a signed numeric value, and the exponent is a signed integer that specifies the magnitude of the mantissa. An approximate numeric value has a precision. The precision is a posi- tive integer that specifies the number of significant binary digits in the mantissa. The value of an approximate numeric value is the mantissa multiplied by 10exponent. Whenever an exact or approximate numeric value is assigned to a data item or parameter representing an exact numeric value, an approximation of its value that preserves leading significant dig- its after rounding or truncating is represented in the data type of the target. The value is converted to have the precision and scale of the target. The choice of whether to truncate or round is implementation-defined. An approximation obtained by truncation of a numerical value N for an T is a value V representable in T such that N is not closer to zero than the numerical value of V and such that the absolute value of the difference between N and the numer- ical value of V is less than the absolute value of the difference between two successive numerical values representable in T. An approximation obtained by rounding of a numerical value N for an T is a value V representable in T such that the absolute value of the difference between N and the nu- merical value of V is not greater than half the absolute value of the difference between two successive numerical values repre- sentable in T. If there are more than one such values V, then it is implementation-defined which one is taken. All numerical values between the smallest and the largest value, inclusive, representable in a given exact numeric type have an approximation obtained by rounding or truncation for that type; it is implementation-defined which other numerical values have such approximations. 28 Database Language SQL X3H2-92-154/DBL CBR-002 4.4 Numbers An approximation obtained by truncation or rounding of a numerical value N for an T is a value V repre- sentable in T such that there is no numerical value representable in T and distinct from that of V that lies between the numerical value of V and N, inclusive. If there are more than one such values V then it is implementation- defined which one is taken. It is implementation-defined which numerical values have approximations obtained by rounding or trun- cation for a given approximate numeric type. Whenever an exact or approximate numeric value is assigned to a data item or parameter representing an approximate numeric value, an approximation of its value is represented in the data type of the target. The value is converted to have the precision of the target. Operations on numbers are performed according to the normal rules of arithmetic, within implementation-defined limits, except as provided for in Subclause 6.12, "". 4.4.2 Operations involving numbers As well as the usual arithmetic operators, plus, minus, times, divide, unary plus, and unary minus, there are the following func- tions that return numbers: - (see Subclause 4.2.2, "Operations involv- ing character strings", and Subclause 4.3.2, "Operations involv- ing bit strings") takes two strings as arguments and returns an integer; - (see Subclause 4.2.2, "Operations involving character strings", and Subclause 4.3.2, "Operations involv- ing bit strings") operates on a string argument and returns an integer; - (see Subclause 4.5.3, "Operations involving datetimes and intervals") operates on a datetime or interval argument and returns an integer. 4.5 Datetimes and intervals A datetime data type is described by a datetime data type descrip- tor. An interval data type is described by an interval data type descriptor. A datetime data type descriptor contains: - the name of the specific datetime data type (DATE, TIME, TIMESTAMP, TIME WITH TIME ZONE, or TIMESTAMP WITH TIME ZONE); and Concepts 29 X3H2-92-154/DBL CBR-002 4.5 Datetimes and intervals - the value of the
that does not specify TEMPORARY. A derived table is a table derived directly or indirectly from one or more other tables by the evaluation of a . The values of a derived table are derived from the values of the underlying tables when the is evaluated. A viewed table is a named derived table defined by a . A viewed table is sometimes called a view. Concepts 37 X3H2-92-154/DBL CBR-002 4.9 Tables The terms simply underlying table, underlying table, leaf underly- ing table, generally underlying table, and leaf generally underly- ing table define a relationship between a derived table or cursor and other tables. The simply underlying tables of derived tables and cursors are defined in Subclause 7.9, "", Subclause 7.10, "", and Subclause 13.1, "". A viewed table has no simply underlying tables. The underlying tables of a derived table or cursor are the simply underlying tables of the derived table or cursor and the underlying tables of the simply underlying tables of the derived table or cursor. The leaf underlying tables of a derived table or cursor are the underlying tables of the derived table or cursor that do not them- selves have any underlying tables. The generally underlying tables of a derived table or cursor are the underlying tables of the derived table or cursor and, for those underlying tables of the derived table or cursor that are viewed tables, the of each viewed table and the gen- erally underlying tables of the of each viewed table. The leaf generally underlying tables of a derived table or cursor are the generally underlying tables of the derived table or cursor that do not themselves have any generally underlying tables. All base tables are updatable. Derived tables are either updatable or read-only. The operations of insert, update, and delete are permitted for updatable tables, subject to constraining Access Rules. The operations of insert, update, and delete are not allowed for read-only tables. A grouped table is a set of groups derived during the evaluation of a or a . A group is a multiset of rows in which all values of the grouping column or columns are equal if a is specified, or the group is the entire table if no is specified. A grouped table may be considered as a collection of tables. Set functions may operate on the individual tables within the grouped table. A global temporary table is a named table defined by a
that specifies GLOBAL TEMPORARY. A created local temporary table is a named table defined by a
that speci- fies LOCAL TEMPORARY. Global and created local temporary tables are effectively materialized only when referenced in an SQL-session. Every in every SQL-session that references a created local temporary table causes a distinct instance of that created local temporary table to be materialized. That is, the contents of a global temporary table or a created local temporary table cannot be shared between SQL-sessions. In addition, the contents of a cre- ated local temporary table cannot be shared between s of a single SQL-session. The definition of a global temporary table or a created local temporary table appears in a schema. In SQL language, 38 Database Language SQL X3H2-92-154/DBL CBR-002 4.9 Tables the name and the scope of the name of a global temporary table or a created local temporary table are indistinguishable from those of a persistent base table. However, because global temporary ta- ble contents are distinct within SQL-sessions, and created local temporary tables are distinct within s within SQL-sessions, the effective of the schema in which the global tem- porary table or the created local temporary table is instantiated is an implementation-dependent that may be thought of as having been effectively derived from the of the schema in which the global temporary table or created local temporary table is defined and the implementation-dependent SQL- session identifier associated with the SQL-session. In addition, the effective of the schema in which the created local temporary table is instantiated may be thought of as being further qualified by a unique implementation-dependent name associ- ated with the in which the created local temporary table is referenced. A declared local temporary table is a named table defined by a that is effectively materialized the first time any in the that contains the is executed. A declared local tem- porary table is accessible only by s in the that contains the . The effective of the of the declared local tem- porary table may be thought of as the implementation-dependent SQL-session identifier associated with the SQL-session and a unique implementation-dependent name associated with the that contains the . All references to a declared local temporary table are prefixed by "MODULE.". The materialization of a temporary table does not persist beyond the end of the SQL-session in which the table was materialized. Temporary tables are effectively empty at the start of an SQL- session. A table is described by a table descriptor. A table descriptor is either a base table descriptor, a view descriptor, or a derived table descriptor (for a derived table that is not a view). Every table descriptor includes: - the degree of the table (the number of column descriptors); and - the column descriptor of each column in the table. A base table descriptor describes a base table. In addition to the components of every table descriptor, a base table descriptor includes: - the name of the base table; - an indication of whether the table is a persistent base table, a global temporary table, a created local temporary table, or a declared local temporary table; and Concepts 39 X3H2-92-154/DBL CBR-002 4.9 Tables - the descriptor of each table constraint specified for the table. A derived table descriptor describes a derived table. In addi- tion to the components of every table descriptor, a derived table descriptor includes: - if the table is named, then the name of the table; - the that defines how the table is to be de- rived; and - an indication of whether the derived table is updatable or read- only (this is derived from the ); A view descriptor describes a view. In addition to the components of a derived table descriptor, a view descriptor includes: - an indication of whether the view has the CHECK OPTION; if so, whether it is to be applied as CASCADED or LOCAL. 4.10 Integrity constraints Integrity constraints, generally referred to simply as constraints, define the valid states of SQL-data by constraining the values in the base tables. A constraint is either a table constraint, a domain constraint or an assertion. A constraint is described by a constraint descriptor. A constraint descriptor is either a table constraint descriptor, a domain constraint descriptor or an assertion descriptor. Every constraint descriptor includes: - the name of the constraint; - an indication of whether or not the constraint is deferrable; - an indication of whether the initial constraint mode is deferred or immediate; A or is possibly non- deterministic if an implementation might, at two different times where the state of the SQL-data is the same, produce results that differ by more than the order of the rows due to General Rules that specify implementation-dependent behavior. No integrity constraint shall be defined using a or a that is possibly non-deterministic. 40 Database Language SQL X3H2-92-154/DBL CBR-002 4.10 Integrity constraints 4.10.1 Checking of constraints Every constraint is either deferrable or non-deferrable. Within a transaction, every constraint has a constraint mode; if a con- straint is non-deferrable, then its constraint mode is always im- mediate, otherwise it is either or immediate or deferred. Every constraint has an initial constraint mode that specifies the constraint mode for that constraint at the start of each SQL- transaction and immediately after definition of that constraint. If a constraint is deferrable, then its constraint mode may be changed (from immediate to deferred, or from deferred to immediate) by execution of a . The checking of a constraint depends on its constraint mode within the current SQL-transaction. If the constraint mode is immedi- ate, then the constraint is effectively checked at the end of each SQL-statement. If the constraint mode is deferred, then the constraint is effectively checked when the constraint mode is changed to immediate either explicitly by execution of a , or implicitly at the end of the current SQL-transaction. When a constraint is checked other than at the end of an SQL- transaction, if it is not satisfied, then an exception condition is raised and the SQL-statement that caused the constraint to be checked has no effect other than entering the exception information into the diagnostics area. When a is executed, all constraints are effectively checked and, if any constraint is not satisfied, then an exception condition is raised and the transaction is terminated by an implicit . 4.10.2 Table constraints A table constraint is either a unique constraint, a referential constraint or a table check constraint. A table constraint is de- scribed by a table constraint descriptor which is either a unique constraint descriptor, a referential constraint descriptor or a table check constraint descriptor. A unique constraint is described by a unique constraint descriptor. In addition to the components of every table constraint descriptor, a unique constraint descriptor includes: - an indication of whether it was defined with PRIMARY KEY or UNIQUE, and - the names and positions of the unique columns specified in the ; A referential constraint is described by a referential constraint descriptor. In addition to the components of every table constraint descriptor, a referential constraint descriptor includes: Concepts 41 X3H2-92-154/DBL CBR-002 4.10 Integrity constraints - the names of the referencing columns specified in the , - the names of the referenced columns and referenced table speci- fied in the , and - the value of the , if specified, and the , if specified. Note: If MATCH FULL or MATCH PARTIAL is specified for a referential constraint and if the referencing table has only one column spec- ified in for that referential constraint, or if the referencing table has more than one specified column for that , but none of those columns is nullable, then the effect is the same as if no were specified. A table check constraint is described by a table check constraint descriptor. In addition to the components of every table constraint descriptor, a table check constraint descriptor includes: - the . A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns. In addition, if the unique constraint was defined with PRIMARY KEY, then it requires that none of the values in the specified column or columns be the null value. In the case that a table constraint is a referential constraint, the table is referred to as the referencing table. The referenced columns of a referential constraint shall be the unique columns of some unique constraint of the referenced table. A referential constraint is satisfied if one of the following con- ditions is true, depending on the specified in the : - If no was specified then, for each row R1 of the referencing table, either at least one of the values of the referencing columns in R1 shall be a null value, or the value of each referencing column in R1 shall be equal to the value of the corresponding referenced column in some row of the referenced table. - If MATCH FULL was specified then, for each row R1 of the refer- encing table, either the value of every referencing column in R1 shall be a null value, or the value of every referencing column in R1 shall not be null and there shall be some row R2 of the referenced table such that the value of each referencing col- umn in R1 is equal to the value of the corresponding referenced column in R2. 42 Database Language SQL X3H2-92-154/DBL CBR-002 4.10 Integrity constraints - If MATCH PARTIAL was specified then, for each row R1 of the referencing table, there shall be some row R2 of the refer- enced table such that the value of each referencing column in R1 is either null or is equal to the value of the corresponding referenced column in R2. The referencing table may be the same table as the referenced ta- ble. A table check constraint is satisfied if and only if the specified is not false for any row of a table. 4.10.3 Domain constraints A domain constraint is a constraint that is specified for a domain. It is applied to all columns that are based on that domain, and to all values cast to that domain. A domain constraint is described by a domain constraint descriptor. In addition to the components of every constraint descriptor a domain constraint descriptor includes: - the . A domain constraint is satisfied by SQL-data if and only if, for any table T that has a column named C based on that domain, the specified , with each occurrence of VALUE re- placed by C, is not false for any row of T. A domain constraint is satisfied by the result of a if and only if the specified , with each occurrence of VALUE replaced by that result, is not false. 4.10.4 Assertions An assertion is a named constraint that may relate to the content of individual rows of a table, to the entire contents of a table, or to a state required to exist among a number of tables. An assertion is described by an assertion descriptor. In addi- tion to the components of every constraint descriptor an assertion descriptor includes: - the . An assertion is satisfied if and only if the specified is not false. Concepts 43 X3H2-92-154/DBL CBR-002 4.11 SQL-schemas 4.11 SQL-schemas An SQL-schema is a persistent descriptor that includes: - the of the SQL-schema; - the of the owner of the SQL-schema; - The of the default character set for the SQL-schema; and - the descriptor of every component of the SQL-schema. In this International Standard, the term "schema" is used only in the sense of SQL-schema. Each component descriptor is either a domain descriptor, a base table descriptor, a view descriptor, an assertion descriptor, a privilege descriptor, a character set descriptor, a collation descriptor, or a translation descriptor. The persistent objects described by the descriptors are said to be owned by or to have been created by the of the schema. A schema is created initially using a and may be subsequently modified incrementally over time by the execution of s. s are unique within a catalog. A is explicitly or implicitly qualified by a that identifies a catalog. Base tables and views are identified by
s. A
consists of a and an . For a per- sistent table, the identifies the schema in which the base table or view identified by the
was de- fined. Base tables and views defined in different schemas can have s that are equal according to the General Rules of Subclause 8.2, "". If a reference to a
does not explicitly contain a , then a specific is implied. The par- ticular associated with such a
depends on the context in which the
appears and is governed by the rules for . The default schema for s that are dynamically prepared in the current SQL- session through the execution of s and s is initially implementation-defined but may be changed by the use of s. 44 Database Language SQL X3H2-92-154/DBL CBR-002 4.12 Catalogs 4.12 Catalogs Catalogs are named collections of schemas in an SQL-environment. An SQL-environment contains zero or more catalogs. A catalog con- tains one or more schemas, but always contains a schema named INFORMATION_SCHEMA that contains the views and domains of the Information Schema. The method of creation and destruction of catalogs is implementation-defined. The set of catalogs that can be referenced in any SQL-statement, during any particular SQL-transaction, or during the course of an SQL-session is also implementation-defined. The default catalog for a whose does not specify an explicit to qualify the is implementation-defined. The default catalog for s that are dynami- cally prepared in the current SQL-session through the execution of s and s is ini- tially implementation-defined but may be changed by the use of s. 4.13 Clusters of catalogs A cluster is an implementation-defined collection of catalogs. Exactly one cluster is associated with an SQL-session and it defines the totality of the SQL-data that is available to that SQL-session. An instance of a cluster is described by an instance of a defi- nition schema. Given some SQL-data object, such as a view, a con- straint, a domain, or a base table, the definition of that object, and of all the objects that it directly or indirectly references, are in the same cluster of catalogs. For example, no and no can "cross" a cluster boundary. Whether or not any catalog can occur simultaneously in more than one cluster is implementation-defined. Within a cluster, no two catalogs have the same name. 4.14 SQL-data SQL-data is any data described by schemas that is under the control of an SQL-implementation in an SQL-environment. Concepts 45 X3H2-92-154/DBL CBR-002 4.15 SQL-environment 4.15 SQL-environment An SQL-environment comprises the following: - an SQL-implementation capable of processing some Level (Entry SQL, Intermediate SQL, or Full SQL) of this International Standard and at least one binding style; see Clause 23, "Conformance" for further information about binding styles; - zero or more catalogs; - zero or more s; - zero or more s; and - the SQL-data described by the schemas in the catalogs. An SQL-environment may have other implementation-defined contents. The rules determining which s are considered to be within an SQL-environment are implementation-defined. 4.16 Modules A is an object specified in the module language. A is either a persistent or an SQL-session . The mechanisms by which s are created or destroyed are implementation-defined. A consists of an optional , a , a with either or both of a and a , an optional that iden- tifies the character repertoire used for expressing the names of schema objects used in the , zero or more s, zero or more cursors specified by s, and one or more s. All s contained in the are expressed in either or the character repertoire indicated by unless they are specified with "". A compilation unit is a segment of executable code, possibly con- sisting of one or more subprograms. A is associated with a compilation unit during its execution. A single may be associated with multiple compilation units and multiple s may be associated with a single compilation unit. The manner in which this association is specified, including the possible re- quirement for execution of some implementation-defined statement, is implementation-defined. Whether a compilation unit may invoke or transfer control to other compilation units, written in the same or a different programming language, is implementation-defined. 46 Database Language SQL X3H2-92-154/DBL CBR-002 4.17 Procedures 4.17 Procedures A consists of a , a sequence of s, and a single . A in a is invoked by a compilation unit as- sociated with the by means of a host language "call" statement that specifies the of the and supplies a sequence of parameter values corresponding in number and in to the s of the . A call of a causes the that it contains to be executed. 4.18 Parameters A parameter is declared in a by a . The specifies the of its value. A parameter either assumes or supplies the value of the corresponding argument in the call of that . These s map to host language types and are not nullable except through the use of additional indicator variables. 4.18.1 Status parameters The SQLSTATE and SQLCODE parameters are status parameters. They are set to status codes that indicate either that a call of the completed successfully or that an exception condition was raised during execution of the . Note: The SQLSTATE parameter is the preferred status parameter. The SQLCODE parameter is a deprecated feature that is supported for compatibility with earlier versions of this International Standard. See Annex D, "Deprecated features". A shall specify either the SQLSTATE parameter or the SQLCODE parameter or both. The SQLSTATE parameter is a charac- ter string parameter for which exception values are defined in Clause 22, "Status codes". The SQLCODE parameter is an integer pa- rameter for which the negative exception values are implementation- defined. If a condition is raised that causes a statement to have no effect other than that associated with raising the condition (that is, not a completion condition), then the condition is said to be an exception condition or exception. If a condition is raised that permits a statement to have an effect other than that associated with raising the condition (corresponding to an SQLSTATE class value of successful completion, warning, or no data), then the condition is said to be a completion condition. Concepts 47 X3H2-92-154/DBL CBR-002 4.18 Parameters 4.18.2 Data parameters A data parameter is a parameter that is used to either assume or supply the value of data exchanged between a host program and an SQL-implementation. 4.18.3 Indicator parameters An indicator parameter is an integer parameter that is specified immediately following another parameter. Its primary use is to indicate whether the value that the other parameter assumes or supplies is a null value. An indicator parameter cannot immediately follow another indicator parameter. The other use for indicator parameters is to indicate whether string data truncation occurred during a transfer between a host program and an SQL-implementation in parameters or host variables. If a non-null string value is transferred and the length of the target data item is sufficient to accept the entire source data item, then the indicator parameter or variable is set to 0 to in- dicate that truncation did not occur. However, if the length of the target data item is insufficient, then the indicator parame- ter or variable is set to the length of the source data item (in characters or bits, as appropriate) to indicate that truncation occurred and to indicate the original length in characters or bits, as appropriate, of the source. 4.19 Diagnostics area The diagnostics area is a place where completion and exception con- dition information is stored when an SQL-statement is executed. There is one diagnostics area associated with an SQL-agent, regard- less of the number of s that the SQL-agent includes or the number of connections in use. At the beginning of the execution of any statement that is not an , the diagnostics area is emptied. An implementation shall place information about a completion condition or an exception condition reported by SQLCODE or SQLSTATE into this area. If other conditions are raised, an implementation may place information about them into this area. s containing s return a code indicating completion or exception conditions for that statement via SQLCODE or SQLSTATE, but do not modify the diagnostics area. An SQL-agent may choose the size of the diagnostics area with the ; if an SQL-agent does not specify the size of the diagnostics area, then the size of the diagnostics area is implementation-dependent, but shall always be able to hold information about at least one condition. An implementation may place information into this area about fewer conditions than are specified. The ordering of the information about conditions placed 48 Database Language SQL X3H2-92-154/DBL CBR-002 4.19 Diagnostics area into the diagnostics area is implementation-dependent, except that the first condition in the diagnostics area always corresponds to the condition specified by the SQLSTATE or SQLCODE value. 4.20 Standard programming languages This International Standard specifies the actions of s in s when those s are called by programs that conform to certain specified programming language standards. The term "standard PLN program", where PLN is the name of a program- ming language, refers to a program that conforms to the standard for that programming language as specified in Clause 2, "Normative references". This International Standard also specifies a mechanism whereby SQL language may be embedded in programs that otherwise conform to any of the same specified programming language stan- dards. Note: In this International Standard, for the purposes of inter- facing with programming languages, the data types DATE, TIME, TIMESTAMP, and INTERVAL shall be converted to or from character strings in those programming languages by means of a . It is anticipated that future evolution of programming language standards will support data types corresponding to these four SQL data types; this standard will then be amended to reflect the availability of those corresponding data types. The data type CHARACTER is also mapped to character strings in the programming languages. However, because the facilities available in the pro- gramming languages do not provide the same capabilities as those available in SQL, there shall be agreement between the host pro- gram and SQL regarding the specific format of the character data being exchanged. Specific syntax for this agreement is provided in this International standard. For standard programming lan- guages, C, COBOL, Fortran, and Pascal, bit strings are mapped to character variables in the host language in a manner described in Subclause 19.1, "". For standard pro- gramming languages Ada and PL/I, bit string variables are directly supported. 4.21 Cursors A cursor is specified by a , , or . For every or in a , a cursor is effectively created when an SQL-transaction (see Subclause 4.28, "SQL-transactions") referencing the is initiated, and destroyed when that SQL-transaction is terminated. A cursor is also effectively created when an is executed within a SQL-transaction and destroyed when that SQL-transaction is terminated. In addition, an extended dynamic Concepts 49 X3H2-92-154/DBL CBR-002 4.21 Cursors cursor is destroyed when a is exe- cuted that deallocates the prepared statement on which the extended dynamic cursor is based. A cursor is in either the open state or the closed state. The ini- tial state of a cursor is the closed state. A cursor is placed in the open state by an or and returned to the closed state by a or , a , or a . A cursor in the open state identifies a table, an ordering of the rows of that table, and a position relative to that ordering. If the does not include an , or includes an that does not specify the order of the rows completely, then the rows of the table have an order that is defined only to the extent that the specifies an order and is otherwise implementation-dependent. When the ordering of a cursor is not defined by an , the relative positions of two rows is implementation- dependent. When the ordering of a cursor is partially determined by an , then the relative positions of two rows are determined only by the ; if the two rows have equal values for the purpose of evaluating the , then their relative positions are implementation-dependent. A cursor is either read-only or updatable. If the table identified by a cursor is not updatable or if INSENSITIVE is specified for the cursor, then the cursor is read-only; otherwise, the cursor is updatable. The operations of update and delete are not allowed for read-only cursors. The position of a cursor in the open state is either before a cer- tain row, on a certain row, or after the last row. If a cursor is on a row, then that row is the current row of the cursor. A cursor may be before the first row or after the last row of a table even though the table is empty. When a cursor is initially opened, the position of the cursor is before the first row. A or positions an open cursor on a specified row of the cursor's ordering and retrieves the values of the columns of that row. An or updates the current row of the cursor. A or deletes the current row of the cursor. If an error occurs during the execution of an SQL-statement that identifies an open cursor, then, except where otherwise explic- itly defined, the effect, if any, on the position or state of that cursor is implementation-dependent. If a cursor is open, and the current SQL-transaction makes a change to SQL-data other than through that cursor, and the for that cursor specified INSENSITIVE, then the effect of that change will not be visible through that cursor before it is closed. Otherwise, whether the effect of such a change will be 50 Database Language SQL X3H2-92-154/DBL CBR-002 4.21 Cursors visible through that cursor before it is closed is implementation- dependent. 4.22 SQL-statements 4.22.1 Classes of SQL-statements An SQL-statement is a string of characters that conforms to the format and syntax rules specified in this international standard. Most SQL-statements can be prepared for execution and executed in one of a number of ways. These are: - in a , in which case it is prepared when the is created (see Subclause 4.16, "Modules") and executed when the containing procedure is called. - in an embedded SQL host program, in which case it is pre- pared when the embedded SQL host program is preprocessed (see Subclause 4.23, "Embedded syntax"). - being prepared and executed by the use of SQL-dynamic statements (which are themselves executed in one of the foregoing two ways- see Subclause 4.24, "SQL dynamic statements"). - direct invocation, in which case it is effectively prepared immediately prior to execution (see Subclause 4.25, "Direct invocation of SQL"). There are at least five ways of classifying SQL-statements: - According to their effect on SQL objects, whether persistent objects, i.e., SQL-data and schemas, or transient objects, such as SQL-sessions and other SQL-statements. - According to whether or not they start a transaction, or can, or must, be executed when no transaction is active. - According to whether or not they may be embedded. - According to whether they may be dynamically prepared and exe- cuted. - According to whether or not they may be directly executed. This International Standard permits implementations to provide ad- ditional, implementation-defined, statements that may fall into any of these categories. This Subclause will not mention those state- ments again, as their classification is entirely implementation- defined. Concepts 51 X3H2-92-154/DBL CBR-002 4.22 SQL-statements 4.22.2 SQL-statements classified by function The following are the main classes of SQL-statements: - SQL-schema statements; these may have a persistent effect on schemas - SQL-data statements; some of these, the SQL-data change state- ments, may have a persistent effect on SQL-data - SQL-transaction statements; except for the , these, and the following classes, have no effects that persist when a session is terminated - SQL-connection statements - SQL-session statements - SQL-dynamic statements - SQL-diagnostics statements - SQL embedded exception declaration The following are the SQL-schema statements: - - - - -
- - - - - - - - - - - 52 Database Language SQL X3H2-92-154/DBL CBR-002 4.22 SQL-statements - - - - The following are the SQL-data statements: - - - - - - - - - - - - o o o o o o o Concepts 55 X3H2-92-154/DBL CBR-002 4.22 SQL-statements The following SQL-statements are embeddable in an embedded SQL host program, and may occur in a , though not in a : - - - The following SQL-statements are embeddable in an embedded SQL host program, but may not occur in a : - SQL embedded exception declarations Consequently, the following SQL-data statements are not embeddable in an embedded SQL host program, nor may they occur in a , nor be the in a in a : - - - - - 4.22.4 Preparable and immediately executable SQL-statements The following SQL-statements are preparable: - All SQL-schema statements - All SQL-transaction statements - All SQL-session statements - The following SQL-data statements: o o o o o o o o 56 Database Language SQL X3H2-92-154/DBL CBR-002 4.22 SQL-statements Consequently, the following SQL-statements are not preparable: - All SQL-connection statements - All SQL-dynamic statements - All SQL-diagnostics statements - SQL embedded exception declarations - The following SQL-data statements: o o o o o o o o o o 58 Database Language SQL X3H2-92-154/DBL CBR-002 4.22 SQL-statements o o o o o o 4.22.6 SQL-statements and transaction states Whether an starts a transaction de- pends on what SQL-statement is the value of . Whether an starts a transaction depends on what SQL-statement was the value of when the prepared statement identified by was prepared. The following SQL-statements are transaction initiating SQL- statements, i.e., if there is no current transaction, and a state- ment of this class is executed, a transaction is initiated: - All SQL-schema statements - The following SQL-data statements: o o o o o o o o o
::= | ::= MODULE ::= ::= ::= [ ] ::= ::= ::= [ ] ::= ::= 98 Database Language SQL X3H2-92-154/DBL CBR-002 5.4 Names and identifiers ::= ::= ::= ::= ::= | ::= ::= [ ] ::= | ::= [ ] ::= [ ] ::= GLOBAL | LOCAL ::= ::= ::= ::= [ ] ::= ::= ::= ::= ::= Lexical elements 99 X3H2-92-154/DBL CBR-002 5.4 Names and identifiers Syntax Rules 1) If a is not specified in an , then the set of characters contained in the shall be wholly contained in either or the character repertoire identified by: Case: a) If the is contained in a , then the , b) If the is contained in a that is not contained in a , then the , c) If the is contained in a that is prepared in the current SQL-session by an or a or in a that is invoked directly, then the default character set name for the SQL-session. 2) If a is specified in an , then: a) There shall be no between the and the . b) The set of characters contained in the or shall be wholly contained in the character repertoire indicated by the . 3) The sum of the number of s and the number of s in an shall not be greater than 128. 4) An is equivalent to an in which every letter that is a lower-case letter is replaced by the equivalent upper-case letter or letters. This treatment includes determination of equivalence, representation in the Information and Definition Schemas, representation in the diagnostics area, and similar uses. 5) An (with every letter that is a lower- case letter replaced by the equivalent upper-case letter), treated as the repetition of a that specifies a of SQL_TEXT, shall not be equal, according to the comparison rules in Subclause 8.2, "", to any (with every letter that is a lower-case letter replaced by the equivalent upper-case letter), treated as the repetition of a that specifies a of SQL_TEXT. 100 Database Language SQL X3H2-92-154/DBL CBR-002 5.4 Names and identifiers Note: It is the intention that no specified in this International standard or revisions thereto shall end with an . 6) If
is not a , then the table identified by
shall not be a declared local temporary table. 7) No shall specify DEFINITION_SCHEMA. 8) If a does not contain a , then Case: a) If the is contained in a , then the that is specified or implicit in the is implicit. b) If the is contained in a that is prepared in the current SQL-session by an or a or in a that is invoked directly, then the default for the SQL-session is implicit. c) Otherwise, the that is specified or implicit for the is implicit. 9) If a does not contain a , then Case: a) If the is contained in a , then an implementation-defined is implicit. b) If the is contained in a other than in a , then the that is specified or implicit in the is implicit. c) If the is contained in a that is prepared in the current SQL-session by an or a or in a that is invoked directly, then the default catalog name for the SQL-session is implicit. d) If the is contained in a , then Case: i) If the is contained in a , then the explicit or implicit contained in the is implicit. Lexical elements 101 X3H2-92-154/DBL CBR-002 5.4 Names and identifiers ii) Otherwise, an implementation-defined is implicit. e) Otherwise, the explicit or implicit contained in the is implicit. 10)Two s are equal if and only if they have the same and the same , regard- less of whether the s are implicit or explicit. 11)Two s are equal if and only if they have the same and the same , regard- less of whether the s are implicit or explicit. 12)An that is a is associated with a table within a particular scope. The scope of a is either a
"). Scopes may be nested. In different scopes, the same may be associated with different tables or with the same table. 13)The of or shall not be a . 14)The data type of the of shall be character string with an implementation-defined character set and shall have an octet length of 128 octets or less. 15)The data type of the of shall be character string with an implementation- defined character set and shall have an octet length of 128 octets or less. 16)The data type of the of shall be character string with an implementation-defined character set and shall have an octet length of 128 octets or less. 17)In a , , or , if a is not specified, then a of LOCAL is implicit. 18)No shall specify "PUBLIC". 19)Those s that are valid s are implementation-defined. 20)Those s that are valid s are implementation- defined. 102 Database Language SQL X3H2-92-154/DBL CBR-002 5.4 Names and identifiers 21)If a does not specify a , then INFORMATION_SCHEMA is implicit. 22)If a does not specify a , then INFORMATION_SCHEMA is implicit. 23)If a does not specify a , then INFORMATION_SCHEMA is implicit. 24)The of , , and shall be character string with an implementation- defined character set and shall have an octet length of 128 octets or less. 25)If a does not specify a , then INFORMATION_SCHEMA is implicit; otherwise, INFORMATION_ SCHEMA shall be specified. Access Rules None. General Rules 1) A
identifies a table. 2) Within its scope, a identifies a table. 3) A identifies a declared local temporary ta- ble. 4) A identifies a column. 5) A identifies a domain. 6) An represents an authorization iden- tifier and identifies a set of privileges. 7) A identifies a . 8) A identifies a cursor. 9) A identifies a . 10)A identifies a parameter. 11)A identifies a table constraint, a domain constraint, or an assertion. 12)A identifies a statement prepared by the execu- tion of a . The scope of a is the in which it appears and the current SQL-session. Lexical elements 103 X3H2-92-154/DBL CBR-002 5.4 Names and identifiers 13)The value of an identifies a statement prepared by the execution of a . If a of GLOBAL is specified, then the scope of the is the current SQL-session. If a of LOCAL is specified or implicit, then the scope of the state- ment name is further restricted to the in which the appears. 14)A identifies a cursor in an . 15)The value of an identifies a cursor cre- ated by the execution of an . If a of GLOBAL is specified, then the scope of the is the current SQL-session. If a of LOCAL is specified of implicit, then the scope of the cursor name is further restricted to the in which the appears. 16)A identifies an SQL descriptor area created by the execution of an . If a of GLOBAL is specified, then the scope of the is the current SQL-session. If a of LOCAL is specified or implicit, then the scope of the is further restricted to the in which the appears. 17)A identifies a catalog. 18)A identifies a schema. 19)A identifies a collating sequence. 20)A identifies a character set. 21)A identifies a character translation. 22)A identifies a form-of-use con- version. All s are implementation- defined. 23)A identifies an SQL-connection. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any or . b) Conforming Intermediate SQL language shall not contain any explicit , , , , , or . 104 Database Language SQL X3H2-92-154/DBL CBR-002 5.4 Names and identifiers 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any , , , , , or . b) An shall not specify a . Lexical elements 105 X3H2-92-154/DBL CBR-002 106 Database Language SQL X3H2-92-154/DBL CBR-002 6 Scalar expressions 6.1 Function Specify a data type. Format ::= [ CHARACTER SET ] | | | | | ::= CHARACTER [ ] | CHAR [ ] | CHARACTER VARYING | CHAR VARYING | VARCHAR ::= NATIONAL CHARACTER [ ] | NATIONAL CHAR [ ] | NCHAR [ ] | NATIONAL CHARACTER VARYING | NATIONAL CHAR VARYING | NCHAR VARYING ::= BIT [ ] | BIT VARYING ::= | ::= NUMERIC [ [ ] ] | DECIMAL [ [ ] ] Scalar expressions 107 X3H2-92-154/DBL CBR-002 6.1 | DEC [ [ ] ] | INTEGER | INT | SMALLINT ::= FLOAT [ ] | REAL | DOUBLE PRECISION ::= ::= ::= ::= DATE | TIME [
6.3
Function Reference a table. Format
::=
[ [ AS ] [ ] ] | [ AS ] [ ] | ::=
::= ::= [ { }... ] Syntax Rules 1) A immediately contained in a
TR is exposed by TR. A
immediately contained in a
TR is exposed by TR if and only if TR does not specify a . 2) Case: a) If a
TR is contained in a FC with no intervening , then the scope clause SC of TR is the
of TR is the
of TR is the of SC and of all s contained in SC that contain TR. 3) A
that is exposed by a
TR shall not be the same as any other
that is exposed by a
with the same scope clause as TR. 118 Database Language SQL X3H2-92-154/DBL CBR-002 6.3
4) A that is exposed by a
TR shall not be the same as any other that is exposed by a
with the same scope clause as TR and shall not be the same as the of any
that is exposed by a
with the same scope clause as TR. 5) A
immediately contained in a
TR has a scope clause and scope defined by that
if and only if the
is exposed by TR. 6) The same shall not be specified more than once in a . 7) If a is specified in a
, then the number of s in the shall be the same as the degree of the table specified by the or the
of that
, and the name of the i-th column of that or the effective name of the i-th column of that
is the i-th in that . 8) A is an updatable derived table if and only if the simply contained in the of the
of the is updatable. Access Rules 1) Let T be the table identified by the
immediately contained in
. If the
is contained in any of: a) a simply contained in a , a , a , or an ; or b) a
or ; or c) a immediately contained in a or an ; or d) a immediately contained in an , then the applicable privileges shall include SELECT for T. General Rules 1) The or exposed
contained in a
defines that or
to be an identifier of the table identified by the
or of that
. Scalar expressions 119 X3H2-92-154/DBL CBR-002 6.3
Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) A
shall not be a . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A
shall not be a . b) The optional AS shall not be specified. c) shall not be specified. 120 Database Language SQL X3H2-92-154/DBL CBR-002 6.4 6.4 Function Reference a column. Format ::= [ ] ::=
| Syntax Rules 1) Let CR be the , let CN be the contained in CR, and let C be the column identified by CN. 2) If CR contains a Q, then CR shall appear within the scope of one or more
s or s that are equal to Q. If there is more than one such
or , then the one with the most local scope is specified. Let T be the table associated with Q. a) T shall include a column whose is CN. b) If T is a
in a J, then CN shall not be a common column name in J. Note: Common column name is defined in Subclause 7.5, "". 3) If CR does not contain a , then CR shall be contained within the scope of one or more
s or s whose associated tables include a column whose is CN. Let the phrase possible qualifiers denote those
s and s. a) Case: i) If the most local scope contains exactly one possible qualifier, then the qualifier Q equivalent to that unique
or is implicit. ii) If there is more than one possible qualifier with most local scope, then: 1) Each possible qualifier shall be a
or a of a
that is di- rectly contained in a J. Scalar expressions 121 X3H2-92-154/DBL CBR-002 6.4 2) CN shall be a common column name in J. Note: Common column name is defined in Subclause 7.5, "". 3) The implicit qualifier Q is implementation-dependent. The scope of Q is that which Q would have had if J had been replaced by the
: ( J ) AS Q b) Let T be the table associated with Q. 4) The data type of CR is the data type of column C of T. CN shall uniquely identify a column of T. 5) If the data type of CR is character string, then CR has the Implicit coercibility attribute and its collating sequence is the default collating sequence for column C of T. 6) If the data type of CR is TIME or TIMESTAMP, then the implicit time zone of the data is the current default time zone for the SQL-session. 7) If the data type of CR is TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE, then the time zone of the data is the time zone rep- resented in the value of CR. 8) If CR is contained in a
TE and the scope clause of the
immediately containing the or Q also contains TE, then CR is an outer reference to the table associated with Q. 9) Let CR be the and let C be the column identi- fied by CR. C is an underlying column of CR. If C is a , then every underlying column of C is an underlying column of CR. Note: The underlying columns of a are defined in Subclause 7.9, "". Access Rules 1) The applicable privileges shall include SELECT for T if CR is contained in any of: a) a immediately contained in a or an ; or b) a immediately contained in an . 122 Database Language SQL X3H2-92-154/DBL CBR-002 6.4 General Rules 1) The Q.CN references column C in a given row of T. 2) If the data type of CR is TIME, TIMESTAMP, TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE, then let TZ be an INTERVAL HOUR TO MINUTE containing the value of the time zone displacement associated with CR. The value of CR, normalized to UTC, is ef- fectively computed as CR + TZ. Leveling Rules 1) The following restrictions apply for Intermediate SQL; None. 2) The following restrictions apply for Entry SQL; None. Scalar expressions 123 X3H2-92-154/DBL CBR-002 6.5 6.5 Function Specify a value derived by the application of a function to an argument. Format ::= COUNT | ::= [ ] ::= AVG | MAX | MIN | SUM | COUNT ::= DISTINCT | ALL Syntax Rules 1) If is not specified, then ALL is implicit. 2) The argument of COUNT(*) and the argument source of a is a table or a group of a grouped table as spec- ified in Subclause 7.8, "", and Subclause 7.9, "". Note: argument source is defined in Subclause 7.8, "". 3) Let T be the argument or argument source of a . 4) The simply contained in shall not contain a or a . If the contains a that is an outer reference, then that outer reference shall be the only contained in the . Note: Outer reference is defined in Subclause 6.4, "". 5) If a contains a that is an outer reference, then the shall be contained in either: a) a
that is directly contained in the
that directly contains the . Note: Outer reference is defined in Subclause 6.4, "". 6) Let DT be the data type of the . 7) If COUNT is specified, then the data type of the result is exact numeric with implementation-defined precision and scale of 0. 8) If MAX or MIN is specified, then the data type of the result is DT. 9) If SUM or AVG is specified, then: a) DT shall not be character string, bit string, or datetime. b) If SUM is specified and DT is exact numeric with scale S, then the data type of the result is exact numeric with implementation-defined precision and scale S. c) If AVG is specified and DT is exact numeric, then the data type of the result is exact numeric with implementation- defined precision not less than the precision of DT and implementation-defined scale not less than the scale of DT. d) If DT is approximate numeric, then the data type of the result is approximate numeric with implementation-defined precision not less than the precision of DT. e) If DT is interval, then the data type of the result is inter- val with the same precision as DT. 10)If the data type of the result is character string, then the collating sequence and the coercibility attribute are determined as in Subclause 4.2.3, "Rules determining collating sequence usage". Access Rules None. General Rules 1) Case: a) If COUNT(*) is specified, then the result is the cardinality of T. Scalar expressions 125 X3H2-92-154/DBL CBR-002 6.5 b) Otherwise, let TX be the single-column table that is the result of applying the to each row of T and eliminating null values. If one or more null values are eliminated, then a completion condition is raised: warning- null value eliminated in set function. 2) If DISTINCT is specified, then let TXA be the result of elimi- nating redundant duplicate values from TX. Otherwise, let TXA be TX. Case: a) If the COUNT is specified, then the result is the cardinality of TXA. b) If AVG, MAX, MIN, or SUM is specified, then Case: i) If TXA is empty, then the result is the null value. ii) If AVG is specified, then the result is the average of the values in TXA. iii) If MAX or MIN is specified, then the result is respec- tively the maximum or minimum value in TXA. These results are determined using the comparison rules specified in Subclause 8.2, "". iv) If SUM is specified, then the result is the sum of the values in TXA. If the sum is not within the range of the data type of the result, then an exception condition is raised: data exception-numeric value out of range. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) If a specifies DISTINCT, then the shall be a . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) If a specifies or implies ALL, then COUNT shall not be specified. b) If a specifies or implies ALL, then the shall include a that references a column of T. c) If the contains a that is an outer reference, then the shall be a . 126 Database Language SQL X3H2-92-154/DBL CBR-002 6.5 d) No contained in a shall reference a column derived from a that generally contains a . Scalar expressions 127 X3H2-92-154/DBL CBR-002 6.6 6.6 Function Specify a function yielding a value of type numeric. Format ::= | | ::= POSITION IN ::= | | ::= { CHAR_LENGTH | CHARACTER_LENGTH } ::= OCTET_LENGTH ::= BIT_LENGTH ::= EXTRACT FROM ::= |
shall not contain more than one . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A shall not specify DEFAULT. Query expressions 175 X3H2-92-154/DBL CBR-002 7.2
7.2
Function Specify a set of s to be constructed into a table. Format
::= VALUES
::= [ { }... ] Syntax Rules 1) All s shall be of the same degree. Access Rules None. General Rules 1) Let Ti be a table whose j-th column has the same data type as the j-th in the i-th and let Ti contain one row whose j-th column has the same value as the j-th in the i-th . 2) The result of the
is the same as the result of T1 [ UNION ALL T2 [ . . . UNION ALL n ] . . . ] Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) A
shall contain exactly one that shall be of the form "()". b) A
shall be the of an . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 176 Database Language SQL X3H2-92-154/DBL CBR-002 7.3
7.3
Function Specify a table or a grouped table. Format
::= [ ] [ ] [ ] Syntax Rules 1) The result of a
is a derived table in which the descriptor of the i-th column is the same as the descriptor of the i-th column of the table specified by the . 2) Let C be some column. Let TE be the
. C is an underlying column of TE if and only if C is an underlying column of some contained in TE. Access Rules None. General Rules 1) If all optional clauses are omitted, then the result of the is the same as the result of the . Otherwise, each specified clause is applied to the result of the previously specified clause and the result of the
is the result of the application of the last specified clause. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) If the table identified in the is a grouped view, then the
shall not contain a , , or . Query expressions 177 X3H2-92-154/DBL CBR-002 7.4 7.4 Function Specify a table derived from one or more named tables. Format ::= FROM
[ {
}... ] Syntax Rules 1) Case: a) If the contains a single
with no intervening or , then the descriptor of the result of the is the same as the descriptor of the table identified by that
. b) If the contains more than one
with no intervening or , then the descriptors of the columns of the result of the are the descriptors of the columns of the tables identified by the
s, in the order in which the
s appear in the and in the order in which the columns are defined within each table. Access Rules None. General Rules 1) Case: a) If the contains a single
with no intervening or , then the result of the is the table identified by that
. b) If the contains more than one
with no intervening or , then the result of the is the extended Cartesian product of the tables identified by those
s. The extended Cartesian product, CP, is the multiset of all rows R such that R is the concatenation of a row from each of the identified tables in the order in which they are iden- tified. The cardinality of CP is the product of the cardi- nalities of the identified tables. The ordinal position of a 178 Database Language SQL X3H2-92-154/DBL CBR-002 7.4 column in CP is N+S, where N is the ordinal position of that column in the identified table T from which it is derived and S is the sum of the degrees of the tables identified before T in the . Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) If the table identified by
is a grouped view, then the shall contain exactly one
. Query expressions 179 X3H2-92-154/DBL CBR-002 7.5 7.5 Function Specify a table derived from a Cartesian product, inner or outer join, or union join. Format ::= | | ::=
CROSS JOIN
::=
[ NATURAL ] [ ] JOIN
[ ] ::= | ::= ON ::= USING ::= INNER | [ OUTER ] | UNION ::= LEFT | RIGHT | FULL ::= Syntax Rules 1) Let TR1 and TR2 be the first and second
s of the , respectively. Let T1 and T2 be the tables identified by TR1 and TR2, respectively. Let TA and TB be the correlation names of TR1 and TR2, respectively. Let CP be: SELECT * FROM TR1, TR2 2) If a is specified, then 180 Database Language SQL X3H2-92-154/DBL CBR-002 7.5 Case: a) If NATURAL is specified, then a shall not be specified. b) If UNION is specified, then neither NATURAL nor a shall be specified. c) Otherwise, a shall be specified. 3) If a is specified and a is not specified, then INNER is implicit. 4) If a containing a is speci- fied, then; a) Each directly contained in the shall unambiguously reference a column of T1 or T2 or be an outer reference. b) If a directly contained in the is a , then the shall be contained in a or of s of the form COALESCE ( TA.C, TB.C ) AS C for every column C that is a corresponding join column, taken in order of their ordinal positions in T1. e) Let SL1 be a of those s of T2 that are not correspond- ing join columns, taken in order of their ordinal positions in T2. f) The descriptors of the columns of the result of the are the same as the descriptors of the columns of the result of SELECT SLCC, SLT1, SLT2 FROM TR1, TR2 7) For every column CR of the result of the that is not a corresponding join column and that corresponds to a column C1 of T1, CR is possibly nullable if any of the following conditions are true: a) RIGHT, FULL, or UNION is specified, or b) INNER, LEFT, or CROSS JOIN is specified or implicit and 1 is possibly nullable. 8) For every column CR of the result of the that is not a corresponding join column and that corresponds to a column C2 of T2, CR is possibly nullable if any of the following conditions are true: a) LEFT, FULL, or UNION is specified, or b) INNER, RIGHT, or CROSS JOIN is specified or implicit and C is possibly nullable. 9) For every column CR of the result of the that is a corresponding join column and that corresponds to a column C1 of T1 and C2 of T2, CR is possibly nullable if any of the following conditions are true: a) RIGHT, FULL, or UNION is specified and 1 is possibly nul- lable, or b) LEFT, FULL, or UNION is specified and 2 is possibly nul- lable. 10)The is a read-only table. 182 Database Language SQL X3H2-92-154/DBL CBR-002 7.5 Access Rules None. General Rules 1) Case: a) If is UNION, then let T be the empty set. b) If a is specified, then let T be the multiset of rows of CP. c) If a is specified, then let T be the multi- set of rows of CP for which the specified is true. d) If NATURAL is specified or is specified, then Case: i) If there are corresponding join columns, then let T be the multiset of rows of CP for which the corresponding join columns have equal values. ii) Otherwise, let T be the multiset of rows of CP. 2) Let P1 be the multiset of rows of T1 for which there exists in T some row that is the concatenation of some row R1 of T1 and some row R2 of T2. Let P2 be the multiset of rows of T2 for which there exists in T some row that is the concatenation of some row R1 of T1 and some row R2 of T2. 3) Let U1 be those rows of T1 that are not in P1 and let U2 be those rows of T2 that are not in P2. 4) Let D1 and D2 be the degree of T1 and T2, respectively. Let X1 be U1 extended on the right with D2 columns containing the null value. Let X2 be U2 extended on the left with D1 columns containing the null value. 5) Let XN1 and XN2 be effective distinct names for X1 and X2, re- spectively. Let TN be an effective name for T. Case: a) If INNER or is specified, then let S be the multiset of rows of T. b) If LEFT is specified, then let S be the multiset of rows resulting from: SELECT * FROM TN UNION ALL SELECT * FROM XN1 Query expressions 183 X3H2-92-154/DBL CBR-002 7.5 c) If RIGHT is specified, then let S be the multiset of rows resulting from: SELECT * FROM TN UNION ALL SELECT * FROM XN2 d) If FULL is specified, then let S be the multiset of rows resulting from: SELECT * FROM TN UNION ALL SELECT * FROM XN1 UNION ALL SELECT * FROM XN2 e) If UNION is specified, then let S be the multiset of rows resulting from: SELECT * FROM XN1 UNION ALL SELECT * FROM XN2 6) Let SN be an effective name of S. Case: a) If NATURAL is specified or a is speci- fied, then the result of the is the multiset of rows resulting from: SELECT SLCC, SLT1, SLT2 FROM SN b) Otherwise, the result of the is S. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall contain no . b) Conforming Intermediate SQL language shall not specify UNION JOIN. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any . 184 Database Language SQL X3H2-92-154/DBL CBR-002 7.6 7.6 Function Specify a table derived by the application of a to the result of the preceding . Format ::= WHERE Syntax Rules 1) Let T be the result of the preceding . Each directly contained in the shall unambiguously reference a column of T or be an outer reference. Note: Outer reference is defined in Subclause 6.4, "". 2) If a directly contained in the is a , then the shall be contained in a or
. Format ::= SELECT [ ]
[ { ::= | ::= [ ] ::= [ AS ] Syntax Rules 1) Let T be the result of the
. 2) The degree of the table specified by a is equal to the cardinality of the "*" is simply contained in a that is immediately contained in an , then the "*" is equivalent to a sequence in which each is a that references a column of T and each column of T is referenced exactly once. The columns are ref- erenced in the ascending sequence of their ordinal position within T. 4) The of QS. For all i, C is an underlying column of DCi, and of any that identifies DCi, if and only if C is an underlying column of the of DCi, or C is an underlying column of the
immediately contained in QS. 6) Each directly contained in each and each contained in a directly contained in each shall unambiguously reference a column of T. 7) If T is a grouped table, then each in each that references a column of T shall refer- ence a grouping column or be specified within a . If T is not a grouped table and any contains a that contains a reference to a column of T or any directly contains a that does not contain an outer reference, then every in every that references a column of T shall be specified within a . 8) Each column of the table that is the result of a has a column descriptor that includes a data type descriptor that is the same as the data type descriptor of the from which the column was derived. 9) Case: a) If the i-th in the does not specify an and the of that is a single , then the of the i-th column of the result is C. c) Otherwise, the of the i-th column of the is implementation-dependent and different from the of any column, other than itself, of a table referenced by any
contained in the SQL-statement. 10)A column of the table that is the result of a is possibly nullable if and only if it contains a for a column C that is possibly nullable, an , an , a , CAST NULL AS X (X represents a or a ), SYSTEM_ USER, or a that does not contain COUNT. 192 Database Language SQL X3H2-92-154/DBL CBR-002 7.9 11)Let TREF be the
s that are simply contained in the of the
. The simply un- derlying tables of the are the tables identified by the
s and s contained in TREF without an intervening . 12)A QS is updatable if and only if the fol- lowing conditions hold: a) QS does not specify DISTINCT. b) Every contained in the
immediately contained in QS specifies exactly one
and that
refers either to a base table or to an updatable derived table. Note: updatable derived table is defined in Subclause 6.3, "
". d) If the
immediately contained in QS imme- diately contains a WC, then no leaf generally underlying table of QS shall be a generally underlying table of any contained in WC. e) The
immediately contained in QS does not include a or a . 13)A is possibly non-deterministic if any of the following conditions are true: a) The DISTINCT is specified and one of the columns of T has a data type of character string; or b) The directly contains a that is possibly non-deterministic; or c) The contains a that contains a reference to a column of T or di- rectly contains a that does not contain an outer reference, then T is the argument or argument source of each such and the result of the is a table con- sisting of 1 row. The i-th value of the row is the value specified by the i-th . ii) If the
contains a . b) A
of the is a grouped view, then the
| ::= TABLE
::= CORRESPONDING [ BY ] ::= 196 Database Language SQL X3H2-92-154/DBL CBR-002 7.10 Syntax Rules 1) Let T be the table specified by the . 2) The TABLE
is equivalent to the ( SELECT * FROM
) 3) Let set operator be UNION [ALL], EXCEPT [ALL], or INTERSECT [ALL]. 4) T is an updatable table and the is updatable if and only if it simply contains a QE or a QS and: a) the contains QE or QS without an inter- vening that specified UNION or EXCEPT; b) the contains QE or QS without an interven- ing that specifies INTERSECT; and c) QE or QS is updatable. 5) Case: a) If a is a , then the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . b) If a is an , then the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the table identified by the
contained in the . c) Otherwise, the column descriptor of the i-th column of the is same as the column descriptor of the i- th column of the
, except that the is implementation-dependent and different from the of any column, other than itself, of a table referenced by any
contained in the SQL-statement. 6) Case: a) If a is a , then the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . Query expressions 197 X3H2-92-154/DBL CBR-002 7.10 b) Otherwise, the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . 7) Case: a) If a is a , then the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . b) Otherwise, the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . 8) If a set operator is specified in a or a , then let T1, T2, and TR be respec- tively the first operand, the second operand, and the result of the or . Let TN1 and TN2 be the effective names for T1 and T2, respectively. 9) If a set operator is specified in a or a , then let OP be the set operator. Case: a) If CORRESPONDING is specified, then: i) Within the columns of T1, the same shall not be specified more than once and within the columns of T2, the same shall not be specified more than once. ii) At least one column of T1 shall have a that is the of some column of T2. iii) Case: 1) If is not specified, then let SL be a of those s explic- itly appearing in the in the order that these s appear in the . Every in the shall be a of both T1 and T2. iv) The or is equivalent to: ( SELECT SL FROM TN1 ) OP ( SELECT SL FROM TN2 ) 198 Database Language SQL X3H2-92-154/DBL CBR-002 7.10 b) If CORRESPONDING is not specified, then T1 and T2 shall be of the same degree. 10)Case: a) If the is a , then the column descriptor of the i-th column of the is same as the column descriptor of the i-th column of the . b) Otherwise, i) Case: 1) Let C be the of the i-th column of T1. If the of the i-th column of T2 is C, then the of the i-th column of TR is C. 2) Otherwise, the of the i-th column of TR is implementation-dependent and different from the of any column, other than itself, of any table referenced by any
contained in the SQL-statement. ii) The data type of the i-th column of TR is determined by applying Subclause 9.3, "Set operation result data types", to the data types of the i-th column of T1 and the i-th column of T2. If the i-th column of both T1 and T2 are known not nullable, then the i-th column of TR is known not nullable; otherwise, the i-th column of T is possibly nullable. 11)Case: a) If a is a , then the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . b) Otherwise, the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . 12)Case: a) If a is a , then the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . Query expressions 199 X3H2-92-154/DBL CBR-002 7.10 b) Otherwise, i) Case: 1) Let C be the of the i-th column of T1. If the of the i-th column of T2 is C, then the of the i-th column of TR is C. 2) Otherwise, the of the i-th column of TR is implementation-dependent and different from the of any column, other than itself, of any table referenced by any
contained in the SQL-statement. ii) The data type of the i-th column of TR is determined by applying Subclause 9.3, "Set operation result data types", to the data types of the i-th column of T1 and the i-th column of T2. If the i-th column of both T1 and T2 are known not nullable, then the i-th column of TR is known not nullable; otherwise, the i-th column of T is possibly nullable. 13)Case: a) If a is a , then the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . b) Otherwise, the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . 14)The simply underlying tables of a are the tables identified by those
s, s, and s contained in the without an intervening , an intervening , or an intervening . 15)A is possibly non-deterministic if a) it contains a set operator UNION and ALL is not specified, or if it contains EXCEPT or INTERSECT; and b) the first or second operand contains a column that has a data type of character string. 16)The underlying columns of each column of QE and of QE itself are defined as follows: a) A column of a
has no underlying columns. b) The underlying columns of every i-th column of a ST are the underlying columns of the i-th column of the table immediately contained in ST. 200 Database Language SQL X3H2-92-154/DBL CBR-002 7.10 c) If no set operator is specified, then the underlying columns of every i-th column of QE are the underlying columns of the i-th column of the simply contained in QE. d) If a set operator is specified, then the underlying columns of every i-th column of QE are the underlying columns of the i-th column of T1 and those of the i-th column of T2. e) Let C be some column. C is an underlying column of QE if and only if C is an underlying column of some column of QE. Access Rules None. General Rules 1) Case: a) If no set operator is specified, then T is the result of the specified or . b) If a set operator is specified, then the result of applying the set operator is a table containing the following rows: i) Let R be a row that is a duplicate of some row in T1 or of some row in T2 or both. Let m be the number of duplicates of R in T1 and let n be the number of duplicates of R in T2, where m 0 and n 0. ii) If ALL is not specified, then Case: 1) If UNION is specified, then Case: A) If m > 0 or n > 0, then T contains exactly one dupli- cate of R. B) Otherwise, T contains no duplicate of R. 2) If EXCEPT is specified, then Case: A) If m > 0 and n = 0, then T contains exactly one dupli- cate of R. B) Otherwise, T contains no duplicate of R. 3) If INTERSECT is specified, then Case: A) If m > 0 and n > 0, then T contains exactly one dupli- cate of R. Query expressions 201 X3H2-92-154/DBL CBR-002 7.10 B) Otherwise, T contains no duplicates of R. iii) If ALL is specified, then Case: 1) If UNION is specified, then the number of duplicates of R that T contains is (m + n). 2) If EXCEPT is specified, then the number of duplicates of R that T contains is the maximum of (m - n) and 0. 3) If INTERSECT is specified, then the number of duplicates of R that T contains is the minimum of m and n. Note: See the General Rules of Subclause 8.2, "". 2) If a set operator is specified, then for each column whose data type is interval, let UDT be in turn the data type of the cor- responding column of T and let SV be the value of the column in each row of the first and second operands. The value of the corresponding column of T in the corresponding row of T is CAST (SV AS UDT) Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) A shall not be a
except in an . b) Conforming Intermediate SQL shall contain no . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A shall not specify EXCEPT. b) A shall not specify INTERSECT. c) A shall not contain a . d) A shall not specify CORRESPONDING. e) If UNION is specified, then except for column names, the descriptors of the first and second operands shall be iden- tical and the descriptor of the result is identical to the descriptor of the operands. 202 Database Language SQL X3H2-92-154/DBL CBR-002 7.11 , , and
7.11 , , and
Function Specify a scalar value, a row, or a table derived from a . Format ::= ::=
::= ::= Syntax Rules 1) The degree of a shall be 1. 2) The degree of a shall be greater than 1. 3) The data type of a is the data type of the column of the immediately contained in the . 4) The data types of the columns of a or
are the data types of the respective columns of the immediately contained in the or
. Access Rules None. General Rules 1) If the cardinality of a or a is greater than 1, then an exception condition is raised: cardinal- ity violation. Leveling Rules 1) The following restrictions apply for Intermediate SQL: 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) If a is contained in a , then the
in the shall not contain a or a and shall not identify a grouped view. Query expressions 203 X3H2-92-154/DBL CBR-002 7.11 , , and
b) The contained in a shall be a . 204 Database Language SQL X3H2-92-154/DBL CBR-002 8 Predicates 8.1 Function Specify a condition that can be evaluated to give a truth value of true, false, or unknown. Format ::= | | | | | | | | | Syntax Rules None. Access Rules None. General Rules 1) The result of a is a truth value derived according to the General Rules of Subclause 8.2, "", Subclause 8.3, "", Subclause 8.4, "", Subclause 8.5, "", Subclause 8.6, "", Subclause 8.7, "", Subclause 8.8, "", Subclause 8.9, "", Subclause 8.10, "", or Subclause 8.11, "", as appropriate. Predicates 205 X3H2-92-154/DBL CBR-002 8.1 Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) A shall not be a . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any . b) Conforming Entry SQL language shall not contain any . 206 Database Language SQL X3H2-92-154/DBL CBR-002 8.2 8.2 Function Specify a comparison of two row values. Format ::= ::= | | | | | Syntax Rules 1) The two s shall be of the same degree. 2) Let corresponding values be values with the same ordinal posi- tion in the two s. 3) The data types of the corresponding values of the two s shall be comparable. 4) Let X be a value in the first and Y be the corresponding value in the second . If X and Y have data type character string, then the pair-wise comparison collating sequence used to compare X and Y is de- termined by the table for collating sequences for comparisons (Subclause 4.2.3, "Rules determining collating sequence usage"). For any pair of corresponding character strings, let CS be the identified collating sequence. Access Rules None. General Rules 1) Let X and Y be any two corresponding s. Let XV and YV be the values represented by X and Y, respectively. Case: a) If XV or YV is the null value, then "X Y" is un- known. Predicates 207 X3H2-92-154/DBL CBR-002 8.2 b) If XV and YV are non-null values, then "X Y" is true or false as follows: i) "X = Y" is true if and only if XV and YV are equal. ii) "X <> Y" is true if and only if XV and YV are not equal. iii) "X < Y" is true if and only if XV is less than YV. iv) "X > Y" is true if and only if XV is greater than YV. v) "X <= Y" is true if and only if XV is not greater than YV. vi) "X >= Y" is true if and only if XV is not less than YV. vii) "X Y" is false if and only if "X Y" is not true. 2) Numbers are compared with respect to their algebraic value. 3) The comparison of two character strings is determined as fol- lows: a) If the length in characters of X is not equal to the length in characters of Y, then the shorter string is effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to the length of the longer string by concatenation on the right of one or more pad char- acters, where the pad character is chosen based on CS. If CS has the NO PAD attribute, then the pad character is an implementation-dependent character different from any char- acter in the character set of X and Y that collates less than any string under CS. Otherwise, the pad character is a . b) The result of the comparison of X and Y is given by the col- lating sequence CS. c) Depending on the collating sequence, two strings may com- pare as equal even if they are of different lengths or con- tain different sequences of characters. When the operations MAX, MIN, DISTINCT, references to a grouping column, and the UNION, EXCEPT, and INTERSECT operators refer to character strings, the specific value selected by these operations from a set of such equal values is implementation-dependent. Note: If the coercibility attribute of the comparison is Coercible, then the collating sequence used is the default de- fined for the character repertoire. See also other Syntax Rules in this Subclause, Subclause 10.4, "", and Subclause 11.28, "". 208 Database Language SQL X3H2-92-154/DBL CBR-002 8.2 4) The comparison of two bit string values, X and Y, is determined by comparison of their bits with the same ordinal position. If Xi and Yi are the values of the i-th bits of X and Y, re- spectively, and if LX is the length in bits of X and LY is the length in bits of Y, then: a) X is equal to Y if and only if X = LY and Xi = Yi for all i. b) X is less than Y if and only if: i) LX < LY and Xi = Yi for all i less than or equal to LX; or ii) Xi = Yi for all i < n and Xn = 0 and Yn = 1 for some n less than or equal to the minimum of LX and LY. 5) The comparison of two datetimes is determined according to the interval resulting from their subtraction. Let X and Y be the two values to be compared and let H be the least significant of X and Y. The result of X Y is defined as: ( X - Y ) H INTERVAL (0) H Note: Two datetimes are comparable only if they have the same s; see Subclause 4.5.1, "Datetimes". 6) The comparison of two intervals is determined by the compari- son of their corresponding values after conversion to integers in some common base unit. Let X and Y be the two intervals to be compared. Let A TO B be the specified or implied datetime qualifier of X and C TO D be the specified or implied datetime qualifier of Y. Let T be the least significant of B and D and let U be a datetime qualifier of the form T(N), where N is an large enough so that significance is not lost in the CAST operation. X is effectively replaced by CAST (X AS INTERVAL U). Y is effectively replaced by CAST (Y AS INTERVAL U). The result of the comparison is effectively computed as: CAST ( X AS INTEGER ) CAST ( Y AS INTEGER ) 7) Let Rx and Ry be the two s of the and let RXi and RYi be the i-th s of Rx and Ry, respectively. "Rx Ry" is true, false, or unknown as follows: a) "x = Ry" is true if and only if RXi = RYi for all i. b) "x <> Ry" is true if and only if RXi <> RYi for some i. c) "x < Ry" is true if and only if RXi = RYi for all i < n and RXn < RYn for some n. d) "x > Ry" is true if and only if RXi = RYi for all i < n and RXn > RYn for some n. Predicates 209 X3H2-92-154/DBL CBR-002 8.2 e) "x <= Ry" is true if and only if Rx = Ry or Rx < Ry. f) "x >= Ry" is true if and only if Rx = Ry or Rx > Ry. g) "x = Ry" is false if and only if "Rx <> Ry" is true. h) "x <> Ry" is false if and only if "Rx = Ry" is true. i) "x < Ry" is false if and only if "Rx >= Ry" is true. j) "x > Ry" is false if and only if "Rx <= Ry" is true. k) "x <= Ry" is false if and only if "Rx > Ry" is true. l) "x >= Ry" is false if and only if "Rx < Ry" is true. m) "x Ry" is unknown if and only if "Rx Ry" is neither true nor false. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 210 Database Language SQL X3H2-92-154/DBL CBR-002 8.3 8.3 Function Specify a range comparison. Format ::= [ NOT ] BETWEEN AND Syntax Rules 1) The three s shall be of the same degree. 2) Let respective values be values with the same ordinal position in the two s. 3) The data types of the respective values of the three s shall be comparable. 4) Let X, Y, and Z be the first, second, and third s, respectively. 5) "X NOT BETWEEN Y AND Z" is equivalent to "NOT ( X BETWEEN Y AND Z )". 6) "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z". Access Rules None. General Rules None. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Predicates 211 X3H2-92-154/DBL CBR-002 8.4 8.4 Function Specify a quantified comparison. Format ::= [ NOT ] IN ::=
| ::= { }... Syntax Rules 1) Let IVL be an . ( IVL ) is equivalent to the
: ( VALUES IVL ) 2) Let RVC be the and let IPV be the . 3) The expression RVC NOT IN IPV is equivalent to NOT ( RVC IN IPV ) 4) The expression RVC IN IPV is equivalent to RVC = ANY IPV Access Rules None. General Rules None. 212 Database Language SQL X3H2-92-154/DBL CBR-002 8.4 Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain a in an that is not a . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Predicates 213 X3H2-92-154/DBL CBR-002 8.5 8.5 Function Specify a pattern-match comparison. Format ::= [ NOT ] LIKE [ ESCAPE ] ::= ::= ::= Syntax Rules 1) The data types of , , and shall be character string. , , and shall be comparable. 2) Let M be the result of the of the , let P be the result of the of the , and let E be the result of the of the if one is specified. 3) "M NOT LIKE P" is equivalent to "NOT (M LIKE P)". 4) Case: a) If is not specified, then the collat- ing sequence used for the is determined by Table 3, "Collating sequence usage for comparisons", taking as comparand 1 and as comparand 2. b) Otherwise, let C1 be the coercibility attribute and collat- ing sequence of the , and C2 be the coercibility attribute and collating sequence of the . Let C3 be the resulting coercibility attribute and collating sequence as determined by Table 2, "Collating coercibility rules for dyadic operators", taking C1 as the operand 1 coercibility and C2 as the operand 2 coercibility. The collating sequence used for the is determined by Table 3, "Collating sequence usage for comparisons", taking C3 as the coercibility attribute and collating sequence of comparand 1 and as comparand 2. 214 Database Language SQL X3H2-92-154/DBL CBR-002 8.5 Access Rules None. General Rules 1) If an is specified and M, P, or E is the null value, then M LIKE P ESCAPE E is unknown. 2) If an is not specified and M or P is the null value, then M LIKE P is unknown. 3) Case: a) If an is specified, then: i) If the length in characters of E is not equal to 1, then an exception condition is raised: data exception-invalid escape character. ii) If there is not a partitioning of the string P into sub- strings such that each substring has length 1 or 2, no substring of length 1 is the escape character E, and each substring of length 2 is the escape character E followed by either the escape character E, an character, or the character, then an exception condition is raised: data exception-invalid escape sequence. If there is such a partitioning of P, then in that parti- tioning, each substring with length 2 represents a single occurrence of the second character of that substring. Each substring with length 1 that is the character represents an arbitrary character specifier. Each substring with length 1 that is the character represents an arbitrary string specifier. Each substring with length 1 that is neither the character nor the character represents the character that it contains. b) If an is not specified, then each character in P represents an arbitrary character spec- ifier, each character in P represents an arbitrary string specifier, and each character in P that is neither the character nor the character represents itself. Predicates 215 X3H2-92-154/DBL CBR-002 8.5 4) The string P is a sequence of the minimum number of substring specifiers such that each of P is part of exactly one substring specifier. A substring specifier is an arbitrary character specifier, an arbitrary string spec- ifier, or any sequence of s other than an arbitrary character specifier or an arbitrary string specifier. 5) Case: a) If M and P are character strings whose lengths are variable and if the lengths of both M and P are 0, then M LIKE P is true. b) The M LIKE P is true if there exists a partitioning of M into substrings such that: i) A substring of M is a sequence of 0 or more contiguous s of M and each of M is part of exactly one substring. ii) If the i-th substring specifier of P is an arbitrary char- acter specifier, the i-th substring of M is any single . iii) If the i-th substring specifier of P is an arbitrary string specifier, then the i-th substring of M is any sequence of 0 or more s. iv) If the i-th substring specifier of P is neither an arbi- trary character specifier nor an arbitrary string speci- fier, then the i-th substring of M is equal to that sub- string specifier according to the collating sequence of the , without the appending of characters to M, and has the same length as that substring specifier. v) The number of substrings of M is equal to the number of substring specifiers of P. c) Otherwise, M LIKE P is false. 216 Database Language SQL X3H2-92-154/DBL CBR-002 8.5 Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) The shall be a . b) A shall be a . c) An shall be a . Predicates 217 X3H2-92-154/DBL CBR-002 8.6 8.6 Function Specify a test for a null value. Format ::= IS [ NOT ] NULL Syntax Rules None. Access Rules None. General Rules 1) Let R be the value of the . 2) If all the values in R are the null value, then "R IS NULL" is true; otherwise, it is false. 3) If none of the values in R are the null value, then "R IS NOT NULL" is true; otherwise, it is false. Note: For all R, "R IS NOT NULL" has the same result as "NOT R IS NULL" if and only if R is of degree 1. Table 12, " semantics", specifies this behavior. ________________Table_12-_semantics________________ R IS R IS NOT NOT R IS NOT R IS NOT _Expression_______NULL____NULL__________NULL__________NULL_________ | degree 1: null | true | false | false | true | | | | | | | | degree 1: not | false | true | true | false | null | degree > 1: | true | false | false | true | | all null | | | | | | | | | | | | degree > 1: | false | false | true | true | | some null | | | | | | | | | | | | degree > 1: | false | true | true | false |