Casting between data types Dynamic dispatch of methods Assignments and comparisons Rules for result data types Expressions without operators Rules for string conversions Expressions with the concatenation Partition-compatible data types Constant 143 Expressions with arithmetic operators Integer constants Two-integer operands Floating-point constants 44 Integer and decimal operands Decimal constants Two-decimal operands r string constants Decimal arithmetic in SQL Hexadecimal constants Floating-point operands Graphic string constants User-defined types as operand 194 Special registers 146 Scalar fullselect 194 Datetime operations and durations CLIENT ACCTNG Datetime arithmetic in SQL CLIENT APPLNAME recedence CLIENT USERID CASE expression 201 CLIENT WRKSTNNAME 151 CAST specifications CURRENT DATE Dereference operations CURRENT DBPARTITIONNUM OLAP functions CURRENT DEFAULT TRANSFORM XML functions GROUP Method invocation CURRENT DEGREE 15 Subtype treatment CURRENT EXPLAIN MODE Sequence reference 220 CURRENT EXPLAIN SNAPSHOT Predicates CURRENT MAINTAINED TABLE TYPES Predicates FOR OPTIMIZATION Search conditions CURRENT PATH 159 Basic predicate CURRENT QUERY OPTIMIZATION Quantified predicate 30 CURRENT REFRESH AGE BETWeEN predicate CURRENT SCHEMA EXISTS predicate CURRENT SERVER CURRENT TIME LIKE predicate CURRENT TIMESTAMP NULL predicate 243 CURRENT TIMEZONE TYPE predicate Functions Chapter 3 Functions External, SQL, and sourced user-defined Functions overview functions Aggregate functions Scalar, column, row, and table AVG user-defined functions 168 CORRELATION Function signatures 169 COUNT Function resolution 170 COUNT BIG Function invocation COVARIANCE Conservative binding semantics 175 GROUPING Method MAX External and SQL user-defined methods 178 MIN Method signatures 179 Regression functions Method resolution STDDE SUM IV SQL ReferenceCasting between data types . . . . . 113 Assignments and comparisons . . . . 117 Rules for result data types . . . . . . 134 Rules for string conversions . . . . . 140 Partition-compatible data types . . . . 141 Constants . . . . . . . . . . . . 143 Integer constants . . . . . . . . . 143 Floating-point constants. . . . . . . 144 Decimal constants. . . . . . . . . 144 Character string constants . . . . . . 144 Hexadecimal constants . . . . . . . 145 Graphic string constants . . . . . . 145 Special registers . . . . . . . . . . 146 Special registers . . . . . . . . . 146 CLIENT ACCTNG . . . . . . . . 148 CLIENT APPLNAME . . . . . . . 149 CLIENT USERID . . . . . . . . . 150 CLIENT WRKSTNNAME . . . . . . 151 CURRENT DATE . . . . . . . . . 152 CURRENT DBPARTITIONNUM . . . . 153 CURRENT DEFAULT TRANSFORM GROUP . . . . . . . . . . . . 154 CURRENT DEGREE . . . . . . . . 155 CURRENT EXPLAIN MODE . . . . . 156 CURRENT EXPLAIN SNAPSHOT . . . 157 CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION . . . . . . . 158 CURRENT PATH . . . . . . . . . 159 CURRENT QUERY OPTIMIZATION . . 160 CURRENT REFRESH AGE. . . . . . 161 CURRENT SCHEMA . . . . . . . 162 CURRENT SERVER . . . . . . . . 163 CURRENT TIME . . . . . . . . . 164 CURRENT TIMESTAMP . . . . . . 165 CURRENT TIMEZONE . . . . . . . 166 USER . . . . . . . . . . . . . 167 Functions . . . . . . . . . . . . 168 External, SQL, and sourced user-defined functions. . . . . . . . . . . . 168 Scalar, column, row, and table user-defined functions . . . . . . . 168 Function signatures . . . . . . . . 169 Function resolution . . . . . . . . 170 Function invocation . . . . . . . . 174 Conservative binding semantics . . . . 175 Methods . . . . . . . . . . . . . 178 External and SQL user-defined methods 178 Method signatures . . . . . . . . 179 Method resolution . . . . . . . . 180 Method invocation . . . . . . . . 183 Dynamic dispatch of methods . . . . 184 Expressions . . . . . . . . . . . . 187 Expressions without operators . . . . 188 Expressions with the concatenation operator . . . . . . . . . . . . 188 Expressions with arithmetic operators . . 191 Two-integer operands . . . . . . . 192 Integer and decimal operands. . . . . 193 Two-decimal operands . . . . . . . 193 Decimal arithmetic in SQL . . . . . . 193 Floating-point operands. . . . . . . 194 User-defined types as operands . . . . 194 Scalar fullselect . . . . . . . . . 194 Datetime operations and durations . . . 194 Datetime arithmetic in SQL . . . . . 196 Precedence of operations . . . . . . 200 CASE expressions. . . . . . . . . 201 CAST specifications . . . . . . . . 203 Dereference operations . . . . . . . 206 OLAP functions . . . . . . . . . 207 XML functions . . . . . . . . . . 214 Method invocation . . . . . . . . 218 Subtype treatment . . . . . . . . 219 Sequence reference . . . . . . . . 220 Predicates . . . . . . . . . . . . 225 Predicates . . . . . . . . . . . 225 Search conditions . . . . . . . . . 226 Basic predicate . . . . . . . . . . 229 Quantified predicate . . . . . . . . 230 BETWEEN predicate . . . . . . . . 233 EXISTS predicate . . . . . . . . . 234 IN predicate . . . . . . . . . . 235 LIKE predicate . . . . . . . . . . 238 NULL predicate . . . . . . . . . 243 TYPE predicate . . . . . . . . . 244 Chapter 3. Functions . . . . . . . . 247 Functions overview . . . . . . . . . 247 Aggregate functions . . . . . . . . . 269 AVG . . . . . . . . . . . . . . 270 CORRELATION . . . . . . . . . . 272 COUNT . . . . . . . . . . . . . 273 COUNT_BIG . . . . . . . . . . . 275 COVARIANCE. . . . . . . . . . . 277 GROUPING . . . . . . . . . . . 278 MAX . . . . . . . . . . . . . . 280 MIN . . . . . . . . . . . . . . 282 Regression functions . . . . . . . . . 284 STDDEV . . . . . . . . . . . . . 288 SUM . . . . . . . . . . . . . . 289 iv SQL Reference, Volume 1