Download DB2 SQL Cookbook (Revised v 9.7) PDF

TitleDB2 SQL Cookbook (Revised v 9.7)
File Size1.3 MB
Total Pages470
Table of Contents
	Disclaimer & Copyright
	Tools Used
	Book Binding
	Author / Book
Author Notes
	Book History
	Why Free
	Other Free Documents
	Answering Questions
	Software Whines
Book Editions
	Upload Dates
Table of Contents
Quick Find
	Index of Concepts
		Join Rows
			Figure 1, Join example
			Outer Join
				Figure 2, Left-outer-join example
			Null Values - Replace
			Select Where No Match
				Figure 3, Sub-query example
			Append Rows
				Figure 4, Union example
			Assign Output Numbers
				Figure 5, Assign row-numbers example
			Assign Unique Key Numbers
			If-Then-Else Logic
				Figure 6, Case stmt example
			Get Dependents
				Figure 7, Recursion example
			Convert String to Rows
				Figure 8, Convert string to rows
			Convert Rows to String
				Figure 9, Convert rows to string
			Fetch First "n" Rows
				Figure 10, Fetch first "n" rows example
			Fetch Subsequent "n" Rows
			Fetch Uncommitted Data
				Figure 11, Fetch WITH UR example
			Summarize Column Contents
				Figure 12, Column Functions example
			Subtotals and Grand Totals
				Figure 13, Subtotal and Grand-total example
			Enforcing Data Integrity
			Hide Complex SQL
			Summary Table
Introduction to SQL
	Syntax Diagram Conventions
		Figure 14, Syntax Diagram Conventions
			SQL Comments
				Figure 15, SQL Comment example
				Figure 16, Set Delimiter example
			Statement Delimiter
	SQL Components
		DB2 Objects
				Figure 17, DB2 sample table - EMPLOYEE
				Figure 18, DB2 sample view - EMPLOYEE_VIEW
				Figure 19, Define a view using a VALUES clause
				Figure 20, SELECT from a view that has its own data
				Figure 21, Define a view that creates data on the fly
				Figure 22, Define three aliases, the latter on the earlier
				Figure 23, Define a nickname
				Figure 24, TABLESAMPLE example
		DB2 Data Types
			Figure 25, Sample table definition
			Default Lengths
				Figure 26, Table with default column lengths
			Data Type Usage
		DECFLOAT Arithmetic
			NaN Usage
				Figure 27, NaN arithmetic usage
			Infinity Usage
				Figure 28, Infinity arithmetic usage
				Figure 29, DECFLOAT arithmetic results
			DECFLOAT Value Order
				Figure 30, DECFLOAT value order
				Figure 31, Equal values that may have different orders
				Figure 32, Remove trailing zeros
			DECFLOAT Scalar Functions
		Date/Time Arithmetic
			Figure 33, Labeled Durations and Date/Time Types
			Usage Notes
				Figure 34, Example, Labeled Duration usage
				Figure 35, Adding Months - Varying Results
			Date/Time Duration Usage
				Figure 36, Date/Time Durations
				Figure 37, Date Duration Generation
				Figure 38, Subtracting a Date Duration
			Date/Time Subtraction
		DB2 Special Registers
			Figure 39, DB2 Special Registers
			Usage Notes
			Sample SQL
				Figure 40, Using Special Registers
		Distinct Types
			Figure 41, Create Distinct Type Syntax
			Figure 42, Create and drop distinct type
			Usage Example
				Figure 43, Sample table, without distinct types
				Figure 44, Silly query, but works
				Figure 45, Create Distinct Type examples
				Figure 46, Sample table, with distinct types
				Figure 47, Silly query, now fails
				Figure 48, Silly query, works again
		Fullselect, Subselect, & Common Table Expression
			Figure 49, Query components
			Query Components
		SELECT Statement
			Figure 50, SELECT Statement Syntax (general)
			SELECT Clause
				Figure 51, SELECT Statement Syntax
			SELECT Items
			FROM Objects
			Sample SQL
				Figure 52, Sample SELECT statement
				Figure 53, Use "*" to select all columns in table
				Figure 54, Select an individual column, and all columns
				Figure 55, Select all columns twice
			Figure 56, Fetch First clause Syntax
			Figure 57, FETCH FIRST without ORDER BY, gets random rows
			Figure 58, FETCH FIRST with ORDER BY, gets wrong answer
			Figure 59, FETCH FIRST with ORDER BY, gets right answer
		Correlation Name
			Figure 60, Correlation Name usage example
			Figure 61, Correlation name usage example
		Renaming Fields
			Figure 62, Renaming fields using AS
			Figure 63, View field names defined using AS
		Working with Nulls
			Figure 64, AVG of data containing null values
			Figure 65, Getting a NULL value from a field defined NOT NULL
			Why Nulls Exist
			Locating Null Values
				Figure 66, AVG of those rows that are not null
		Quotes and Double-quotes
			Figure 67, Quote usage
			Figure 68, Double-quote usage
	SQL Predicates
		Predicate Precedence
		Basic Predicate
			Figure 69, Basic Predicate syntax, 1 of 2
			Figure 70, Basic Predicate examples
			Figure 71, Basic Predicate syntax, 2 of 2
			Figure 72, Basic Predicate example, multi-value check
			Figure 73, Same query as prior, using individual predicates
		Quantified Predicate
			Figure 74, Quantified Predicate syntax
			Figure 75, Quantified Predicate example, two single-value sub-queries
			Figure 76, Quantified Predicate example, multi-value sub-query
		BETWEEN Predicate
			Figure 77, BETWEEN Predicate syntax
			Figure 78, BETWEEN Predicate examples
		EXISTS Predicate
			Figure 79, EXISTS Predicate syntax
			Figure 80, EXISTS Predicate example
		IN Predicate
			Figure 81, IN Predicate syntax
			Figure 82, IN Predicate examples, single values
			Figure 83, IN Predicate example, multi-value
		LIKE Predicate
			Figure 84, LIKE Predicate syntax
			Figure 85, LIKE Predicate examples
			The ESCAPE Phrase
				Figure 86, LIKE and ESCAPE examples
				Figure 87, LIKE and ESCAPE examples
		LIKE_COLUMN Function
			Figure 88, Create LIKE_COLUMN function
			Figure 89, Use LIKE_COLUMN function
		NULL Predicate
			Figure 90, NULL Predicate syntax
			Figure 91, NULL predicate examples
		Special Character Usage
			Figure 92, Refer to semi-colon in SQL text
		Precedence Rules
			Figure 93, Precedence rules example
			Figure 94, Precedence rules, integer example
			Figure 95, Precedence rules, decimal example
			AND/OR Precedence
				Figure 96, Use of OR and parenthesis
		Processing Sequence
			Figure 97, Query Processing Sequence
	CAST Expression
		Figure 98, CAST expression syntax
			Input vs. Output Rules
				Figure 99, Use CAST expression to convert Decimal to Integer
				Figure 100, Use CAST expression to truncate Char field
				Figure 101, Use CAST expression to define SMALLINT field with null values
				Figure 102, CAST expression in join
				Figure 103, Function usage in join
	VALUES Statement
		Figure 104, VALUES expression syntax
		Figure 105, VALUES usage examples
			Sample SQL
				Figure 106, Logically equivalent VALUES statements
				Figure 107, VALUES running selects
				Figure 108, Use VALUES to define a temporary table (1 of 4)
				Figure 109, Use VALUES to define a temporary table (2 of 4)
				Figure 110, Use VALUES to define a temporary table (3 of 4)
				Figure 111, Use VALUES to define a temporary table (4 of 4)
			More Sample SQL
				Figure 112, Derive one temporary table from another
				Figure 113, Define a view using a VALUES clause
				Figure 114, Use VALUES defined data to seed a recursive SQL statement
				Figure 115, Generate table with unnamed columns
			Combine Columns
				Figure 116, Combine columns example
	CASE Expression
		CASE Syntax Styles
			CASE Expression, 1st Type
				Figure 117, CASE expression syntax - 1st type
				Figure 118, Use CASE (1st type) to expand a value
			CASE Expression, Type 2
				Figure 119, CASE expression syntax - 2nd type
				Figure 120, Use CASE (2nd type) to expand a value
			Notes & Restrictions
		Sample SQL
			Figure 121, Use CASE to display the higher of two values
			Figure 122, Use CASE to get multiple counts in one pass
			Figure 123, Use CASE inside a function
			Figure 124, UPDATE statement with nested CASE expressions
			Figure 125, Use CASE to avoid divide by zero
			Problematic CASE Statements
				Figure 126, Use CASE to derive a value (correct)
				Figure 127, Use CASE to derive a value (incorrect)
			CASE in Predicate
				Figure 128, Use CASE in a predicate
				Figure 129, Same stmt as prior, without CASE predicate
	Miscellaneous SQL Statements
			Declare Cursor Syntax
				Figure 130, DECLARE CURSOR statement syntax
			Syntax Notes
			Usage Notes
				Figure 131, Sample cursor
				Figure 132, Use cursor in program
		Select Into
				Figure 133, Singleton select
			Figure 134, PREPARE statement syntax
			Syntax Notes
				Figure 135, What statements can use prepared statement
			Figure 136, DESCRIBE statement syntax
			Figure 137, DESCRIBE the output columns in a select statement
			Figure 138, DESCRIBE the columns in a table
		Execute Immediate
		Set Variable
				Figure 139, SET single host-variable
				Figure 140, SET multiple host-variables
				Figure 141, SET using row-fullselect
		Set DB2 Control Structures
			Figure 142, Other SET statements
	Unit-of-Work Processing
			Figure 143, SAVEPOINT statement syntax
			Savepoint Levels
				Figure 144, Example of savepoint usage
			Savepoints vs. Commits
		Release Savepoint
			Figure 145, RELEASE SAVEPOINT statement syntax
			Figure 146, ROLLBACK statement syntax
Data Manipulation Language
	Select DML Changes
		Figure 147, EMP_ACT_COPY sample table - DDL
			Insert Syntax
				Figure 148, INSERT statement syntax
			Target Objects
			Usage Notes
			Direct Insert
				Figure 149, Single row insert
				Figure 150, Multi row insert
				Figure 151,Using null and default values
				Figure 152, Explicitly listing columns being populated during insert
			Insert into Full-Select
				Figure 153, Insert into a fullselect
			Insert from Select
				Figure 154,Insert result of select statement
				Figure 155, Insert result of select - specified columns only
				Figure 156, Stupid - insert - doubles rows
				Figure 157, Inserting result of union
				Figure 158, Insert from common table expression
				Figure 159, Insert with irrelevant sub-query
			Insert into Multiple Tables
				Figure 160, Customer tables - for insert usage
				Figure 161, Insert into multiple tables
		Figure 162, Single row update
			Update Syntax
				Figure 163, UPDATE statement syntax
			Usage Notes
			Update Examples
				Figure 164, Mass update
				Figure 165, Two columns get same value
				Figure 166, Update using select
				Figure 167, Multi-row update using select
				Figure 168, Multi-row update using correlated select
			Use Full-Select
				Figure 169, Direct update of table
				Figure 170, Update of fullselect
			Update First "n" Rows
				Figure 171, Update first "n" rows
			Use OLAP Function
				Figure 172, Set employee-time in row to MAX - for given employee
				Figure 173, Use OLAP function to get max-time, then apply (correct)
				Figure 174, Use OLAP function to get max-time, then apply (wrong)
			Correlated and Uncorrelated Update
				Figure 175, Update with correlated query
				Figure 176, Update with uncorrelated query
		Figure 177, Single-row delete
			Delete Syntax
				Figure 178, DELETE statement syntax
			Usage Notes
			Basic Delete
				Figure 179, Mass delete
				Figure 180, Selective delete
			Correlated Delete
				Figure 181, Correlated delete (1 of 2)
				Figure 182, Correlated delete (2 of 2)
				Figure 183, Delete using fullselect and OLAP function
			Delete First "n" Rows
				Figure 184, Delete first "n" rows
	Select DML Changes
		Select DML Syntax
			Figure 185, Select DML statement syntax
			Table Types
			Usage Notes
			Insert Examples
				Figure 186, Select rows inserted
				Figure 187, Include column to get insert sequence
				Figure 188, Select rows in insert order
				Figure 189, Select from an insert that has unknown values
			Update Examples
				Figure 190, Select values - from before update
				Figure 191, Select values - before and after update
			Delete Examples
				Figure 192, List deleted rows
				Figure 193, Assign row numbers to deleted rows
				Figure 194, Join result to another table
		Figure 195, MERGE statement syntax
			Usage Rules
			Sample Tables
				Figure 196, Sample tables for merge
			Update or Insert Merge
				Figure 197, Merge - do update or insert
			Delete-only Merge
				Figure 198, Merge - delete if match
			Complex Merge
				Figure 199, Merge with multiple options
			Using a Fullselect
				Figure 200, Merge MAX row into table
				Figure 201, Merge logic - done using insert
				Figure 202, Merge using two fullselects
			Listing Columns
				Figure 203, Listing columns and values in insert
Compound SQL
		Figure 204, Compound SQL Statement syntax
		Figure 205, Sample Compound SQL statement
		Statement Delimiter
			Figure 206, Set Delimiter example
			Figure 207, Set Terminator example
	SQL Statement Usage
		DECLARE Variables
			Figure 208, DECLARE examples
		FOR Statement
			Figure 209, FOR statement syntax
			Figure 210, FOR statement example
			Figure 211, GET DIAGNOSTICS statement syntax
			Figure 212, GET DIAGNOSTICS statement example
		IF Statement
			Figure 213, IF statement syntax
			Figure 214, IF statement example
		ITERATE Statement
			Figure 215, ITERATE statement syntax
			Figure 216, ITERATE statement example
		LEAVE Statement
			Figure 217, LEAVE statement syntax
			Figure 218, LEAVE statement example
		SIGNAL Statement
			Figure 219, SIGNAL statement syntax
			Figure 220, SIGNAL statement example
		WHILE Statement
			Figure 221, WHILE statement syntax
			Figure 222, WHILE statement example
	Other Usage
		Test Query
			Figure 223, List departments in STAFF table
			Figure 224, Trigger with compound SQL
		Scalar Function
			Figure 225, Scalar Function with compound SQL
			Figure 226, Scalar Function with compound SQL
		Table Function
			Figure 227, Table Function with compound SQL
Column Functions
	Column Functions, Definitions
			Figure 228, ARRAY_AGG function syntax
			Figure 229, AVG function syntax
			Figure 230, AVG function examples
			Averaging Null and Not-Null Values
				Figure 231, Convert zero to null before doing AVG
			Dealing with Null Output
				Figure 232, Convert null output (from AVG) to zero
			AVG Date/Time Values
				Figure 233, AVG of date column
			Average of an Average
				Figure 234, Select average of average
			Figure 235, CORRELATION function syntax
			Figure 236, CORRELATION function examples
			Figure 237, COUNT function syntax
			Figure 238, COUNT function examples
			Figure 239, COUNT function with and without GROUP BY
			Figure 240, COUNT_BIG function syntax
			Figure 241, COUNT_BIG function examples
			Figure 242, COVARIANCE function syntax
			Figure 243, COVARIANCE function examples
			Figure 244, GROUPING function syntax
			Figure 245, GROUPING function example
			Figure 246, MAX function syntax
			Figure 247, MAX function examples
			MAX and MIN usage with Scalar Functions
				Figure 248, MAX function with dates
				Figure 249, MAX function with numbers, 1 of 2
				Figure 250, MAX function with numbers, 2 of 2
			Figure 251, MIN function syntax
			Figure 252, MIN function examples
		Regression Functions
			Figure 253, REGRESSION functions syntax
				Figure 254, REGRESSION functions examples
			Figure 255, STDDEV function syntax
			Figure 256, STDDEV function examples
			Figure 257, SUM function syntax
			Figure 258, SUM function examples
			Figure 259, VARIANCE function syntax
			Figure 260, VARIANCE function examples
OLAP Functions
		The Bad Old Days
			Figure 261, Select rows from STAFF table
			Figure 262, Using OLAP functions to get additional fields
			Write Query without OLAP Functions
				Figure 263, Running counts without OLAP functions
		Figure 264, Sample OLAP query
		Figure 265, Sample OLAP query
		PARTITION Expression
			Figure 266, PARTITION BY syntax
			Figure 267, PARTITION BY examples
				Figure 268, Sample query using GROUP BY
				Figure 269, Sample query using PARTITION
				Figure 270, Sample query using PARTITION and DISTINCT
		Window Definition
			Figure 271, Moving window definition syntax
			Window Size Partitions
			Sample Queries
				Figure 272, Different window sizes
				Figure 273, Different window sizes
			Figure 274, ROW vs. RANGE example
			Usage Notes
		ORDER BY Expression
			Figure 275, ORDER BY syntax
			Usage Notes
			Sample Query
				Figure 276, ORDER BY example
		Table Designator
			Figure 277, ORDER BY table designator examples
		Nulls Processing
			Figure 278, Overriding the default null ordering sequence
			Counting Nulls
				Figure 279, Counting distinct values - comparison
	OLAP Functions
				Figure 280, Ranking functions syntax
			Usage Notes
			Compare Functions
				Figure 281, Ranking functions example
			ORDER BY Usage
				Figure 282, ORDER BY usage
				Figure 283, Values ranked by subset of rows
			Multiple Rankings
				Figure 284, Multiple rankings in same query
			Dumb Rankings
				Figure 285, Dumb rankings, SQL
				Figure 286, Dumb ranking, Answer
			Subsequent Processing
				Figure 287, Subsequent processing of ranked data
			Ordering Rows by Rank
				Figure 288, Ordering rows by rank, using RANK function
				Figure 289, Ordering rows by rank, using sub-query
			Selecting the Highest Value
				Figure 290, Get highest salary in each department, use RANK function
				Figure 291, Get highest salary in each department, use correlated sub-query
				Figure 292, Get highest salary in each department, use uncorrelated sub-query
				Figure 293, Numbering function syntax
			ORDER BY Usage
				Figure 294, ORDER BY example, 1 of 3
				Figure 295, ORDER BY example, 2 of 3
				Figure 296, ORDER BY example, 3 of 3
				Figure 297, Use of PARTITION phrase
			Selecting "n" Rows
				Figure 298, Select first 3 rows, using ROW_NUMBER function
				Figure 299, Select first 3 rows, using FETCH FIRST notation
				Figure 300, Select 3rd through 6th rows
				Figure 301, Select every 5th matching row
				Figure 302, Select last two rows
			Selecting "n" or more Rows
				Figure 303, Select first "n" rows, or more if needed
			Selecting "n" Rows - Efficiently
				Figure 304, Performance test table - definition
				Figure 305, Performance test table - insert 1,000,000 rows
				Figure 306, Fetch first 5 rows - 0.000 elapsed seconds
				Figure 307, Fetch first 5 rows - 0.000 elapsed seconds
				Figure 308, Fetch first 5 rows+ number rows - 0.000 elapsed seconds
				Figure 309, Fetch first 5 rows+ number rows - 0.000 elapsed seconds
				Figure 310, Process and number all rows - 0.049 elapsed seconds
				Figure 311, Process and number 5 rows only - 0.000 elapsed seconds
				Figure 312, Fetch first 5 rows - 0.000 elapsed seconds
				Figure 313, Function syntax
			Usage Notes
				Figure 314, FIRST_NAME function example
				Figure 315, Function examples
				Figure 316, Null value processing
		LAG and LEAD
				Figure 317, Function syntax
			Usage Notes
				Figure 318, LAG and LEAD function Examples
			Figure 319, Aggregation function syntax
			Syntax Notes
			Basic Usage
				Figure 320, Aggregation function, basic usage
				Figure 321, Logically equivalent aggregation functions
			ORDER BY Usage
				Figure 322, Aggregation function, ORDER BY usage
			ROWS Usage
				Figure 323, ROWS usage examples
			RANGE Usage
				Figure 324, RANGE usage
				Figure 325,BETWEEN and ORDER BY usage
				Figure 326, Explanation of query
Scalar Functions
	Sample Data
		Figure 327, Sample View DDL - Scalar functions
		Figure 328, SCALAR view, contents (3 rows)
	Scalar Functions, Definitions
			Figure 329, ABS function examples
			Figure 330, ASCII function examples
			Figure 331, BIGINT function example
			Figure 332, Convert FLOAT to DECIMAL and BIGINT, SQL
			Figure 333, Convert FLOAT to DECIMAL and BIGINT, answer
		BIT Functions
			Figure 334, BIT functions syntax
			Figure 335, BIT functions examples
			Displaying BIT Values
				Figure 336, Function to display SMALLINT bits
				Figure 337, BIT_DISPLAY function example
			Updating BIT Values
				Figure 338, Update bits #1, query
				Figure 339, Update bits #1, answer
				Figure 340, Update bits #2, query
				Figure 341, Update bits #2, answer
			Figure 342, BLOB function syntax
			Figure 343, CEILING function syntax
			Figure 344, CEIL function examples
			Figure 345, CHAR function syntax
			Figure 346, CHAR function examples - characters and numbers
			Figure 347, CHAR function examples - positive numbers
			Figure 348, Align CHAR function output - numbers
			DATE-TIME Conversion
				Figure 349, CHAR function examples - date value
				Figure 350, CHAR function examples - time value
				Figure 351, CHAR function example - timestamp value
			CHAR vs. DIGITS - A Comparison
				Figure 352, DIGITS vs. CHAR
			Figure 353, CHARACTER_LENGTH function syntax
			Figure 354,CHARACTER_LENGTH function example
			Figure 355, CHR function examples
			Figure 356, CLOB function examples
			Figure 357, COALESCE function example
			Figure 358, COALESCE and equivalent CASE expression
			Figure 359, NOT NULL field returning null value
			Figure 360, COLLATION_KEY_BIT function example
			Figure 361, COLLATION_KEY_BIT function answer
			Figure 362, COMPARE_DECFLOAT function example
			Figure 363, CONCAT function examples
			Using CONCAT with ORDER BY
				Figure 364, CONCAT used with ORDER BY - wrong output sequence
				Figure 365, CONCAT used with ORDER BY - correct output sequence
			Figure 366, RADIAN, COS, and SIN functions example
			Figure 367, DATE function syntax
			Figure 368, DATE function example - timestamp input
			Figure 369, DATE function example - numeric input
			Figure 370, DAY function examples
			Figure 371, DAY function, using date-duration input
			Figure 372, DAYNAME function example
			Figure 373, DAYOFWEEK function example
			Figure 374, DAYOFWEEK_ISO function example
			Figure 375, DAYOFYEAR function example
			Figure 376, DAYS function example
			Figure 377, DBPARTITIONNUM function syntax
			Figure 378, DBPARTITIONNUM function example
			Figure 379, DECFLOAT function syntax
			Figure 380, DECFLOAT function example
			Figure 381, DECIMAL function syntax
			Figure 382, DECIMAL function examples
			Figure 383, DECODE function example
			Figure 384, DECRYPT function syntax
			Figure 385, DECRYPT_CHAR function example
			Figure 386, DIFFERENCE function example
			Figure 387, DIGITS function examples
			Figure 388, DOUBLE function examples
			Figure 389, DECRYPT function syntax
			Figure 390, ENCRYPT function example
			Figure 391, EXP function examples
			Figure 392, FLOOR function examples
			Figure 393, GENERATE_UNIQUE function examples
			Generate Unique Timestamps
				Figure 394, Covert GENERATE_UNIQUE output to timestamp
			Making Random
				Figure 395, GENERATE_UNIQUE output, characters reversed to make pseudo-random
			Using REVERSE Function
				Figure 396, GENERATE_UNIQUE output, characters reversed using function
			Figure 397, GETHINT function example
			Figure 398, HASHEDVALUE function example
			Figure 399, HEX function examples, numeric data
			Figure 400, HEX function examples, character & varchar
			Figure 401, HEX function examples, date & time
			Figure 402, HOUR function example
			Figure 403, IDENTITY_VAL_LOCAL function usage
			Figure 404, INSERT function syntax
			Usage Notes
				Figure 405, INSERT function examples
			Figure 406, INTEGER function examples
			Figure 407, JULIAN_DAY function example
			Julian Days, A History
				Figure 408, JULIAN_DAY function examples
			Julian Dates
				Figure 409, Julian Date outputs
			Figure 410, LCASE function example
			Figure 411, LEFT function examples
			Figure 412, LENGTH function examples
		LN or LOG
			Figure 413, LOG function example
			Figure 414, LOCATE function syntax
			Figure 415, LOCATE function examples
		LOG or LN
			Figure 416, LOG10 function example
			Figure 417, LTRIM function example
			Figure 418, MAX scalar function
			Figure 419, Sample Views used in Join Examples
			Null Processing
			Figure 420, MICROSECOND function example
			Figure 421, MIDNIGHT_SECONDS function example
			Figure 422, Convert MIDNIGHT_SECONDS output back to a time value
			Figure 423, MIN scalar function
			Figure 424, MINUTE function example
			Figure 425, MOD function example
			Figure 426, MONTH and MONTHNAME functions example
			Figure 427, Multiplying numbers - examples
			Figure 428, Decimal multiplication - same output lengths
			Figure 429, NORMALIZE_DECFLOAT function examples
			Figure 430, NULLIF function examples
			Figure 431, OCTET_LENGTH example
			Figure 432, OVERLAY function syntax
			Figure 433, OVERLAY function example
			Figure 434, PARTITION function example
			Figure 435, POSITION function syntax
			Figure 436, POSITION function syntax
			Figure 437, POSSTR function example
				Figure 438, POSSTR vs. LOCATE functions
			Figure 439, POWER function examples
			Figure 440, QUANTIZE function examples
			Figure 441, DECFLOAT conversion example
			Figure 442, RAISE_ERROR function syntax
			Figure 443, RAISE_ERROR function example
			Usage Notes
			Typical Output Values
				Figure 444, Sample output from RAND function
			Reproducible Random Numbers
				Figure 445, Make reproducible random numbers (use seed)
				Figure 446, Make non-reproducible random numbers (no seed)
			Generating Random Values
				Figure 447, Use RAND to make sample data
			Making Many Distinct Random Values
				Figure 448, Use RAND to make many distinct random values
			Selecting Random Rows, Percentage
				Figure 449, Randomly select 10% of matching rows
			Selecting Random Rows, Number
				Figure 450, Select five random rows
			Use in DML
				Figure 451, Use RAND to assign random salaries
			Figure 452, REAL and other numeric function examples
			Figure 453, REPEAT function syntax
			Figure 454, REPEAT function example
			Figure 455, REPLACE function syntax
			Figure 456, REPLACE function examples
			Figure 457, Nested REPLACE functions
			Figure 458, RID function example
			Figure 459, RID_BIT function example – single table
			Figure 460, RID_BIT function example – multiple tables
			Figure 461, RID_BIT function example – select row to update
			Figure 462, RID_BIT function example – update row
			Usage Notes
			Figure 463, RIGHT function examples
			Figure 464, ROUND function examples
			Figure 465, RTRIM function example
		SECLABEL Functions
			Figure 466, SIGN function examples
			Figure 467, SIN function example
			Figure 468, SMALLINT function examples
		SNAPSHOT Functions
			Figure 469, SOUNDEX function example
			SOUNDEX Formula
			Figure 470, SPACE function examples
			Figure 471, SQRT function example
			Figure 472, STRIP function syntax
			Figure 473, STRIP function example
			Figure 474, SUBSTR function syntax
			Figure 475, SUBSTR function - error because length parm too long
			Figure 476, SUBSTR function - avoid error using CASE (see previous)
			Figure 477, SUBSTR function - fixed length output if third parm. used
			Figure 478, Fullselect with external table reference
			Figure 479, TABLE_NAME function example
			Resolving non-existent Objects
				Figure 480, TABLE_SCHEMA and TABLE_NAME functions example
			Argument Options
				Figure 481, TIMESTAMP function examples
			Figure 482, TIMESTAMP_FORMAT function example
			Figure 483, TIMESTAMP_ISO function example
				Figure 484, TIMESTAMPDIFF function example
			Roll Your Own
				Figure 485, Function to get difference between two timestamps
			Figure 486, TOTALORDER function example
			Figure 487, TRANSLATE function syntax
			Usage Notes
				Figure 488, TRANSLATE function examples
			REPLACE vs. TRANSLATE - A Comparison
				Figure 489, REPLACE vs. TRANSLATE
			Figure 490, TRUNCATE function examples
			Figure 491, UCASE function example
			Figure 492, VARCHAR function examples
			Figure 493, VARCHAR_FORMAT function example
			Figure 494, WEEK function examples
			Figure 495, WEEK_ISO function example
			Figure 496, YEAR and WEEK functions example
		"+" PLUS
			Figure 497, PLUS function examples
			Figure 498, Adding one year to date value
		"-" MINUS
			Figure 499, MINUS function examples
			Figure 500, MULTIPLY function examples
		"/" DIVIDE
			Figure 501, DIVIDE function examples
		"||" CONCAT
			Figure 502, CONCAT function examples
User Defined Functions
	Sourced Functions
		Figure 503, Sourced function syntax
		Figure 504, Create sourced function
		Figure 505, Using sourced function - works
		Figure 506, Using sourced function - fails
		Figure 507, Create distinct type and test table
		Figure 508, Do multiply - fails
		Figure 509, Create sourced function
		Figure 510, Do multiply - works
		Figure 511, Do multiply - works
	Scalar Functions
		Figure 512, Scalar and Table function syntax
			Null Output
				Figure 513, Function returns nullable, but never null, value
			Input and Output Limits
			Figure 514, Simple function usage
			Figure 515, Two functions with same name
			Figure 516, Not deterministic function
			Figure 517, Function using query
			Figure 518, Function using common table expression
			Figure 519, Function used in update
			Compound SQL Usage
				Figure 520, Function using compound SQL
				Figure 521, Function with error checking logic
	Table Functions
		Figure 522, Simple table function
			Figure 523, Table function usage - syntax
			Figure 524, Table function with parameters
			Figure 525, Table function that creates data
			Figure 526, Table function with compound SQL
	Useful User-Defined Functions
		Julian Date Functions
			Figure 527, Convert Date into Julian Date
			Figure 528, Convert Julian Date into Date
		Get Prior Date
			Figure 529, Select rows where hire-date = prior year
			Get Prior Month
				Figure 530, Create year-month function
				Figure 531, Select rows where hire-date = prior month
			Get Prior Week
				Figure 532, Create week-number function
				Figure 533, Create week-number function
				Figure 534, Use week-number functions
		Generating Numbers
			Figure 535, Create num-list function
			Figure 536, Using num-list function
			Figure 537, Select activity start & end date
			Figure 538, Generate one row per date between start & end dates (1 of 2)
			Figure 539, Generate one row per date between start & end dates (2 of 2)
		Check Data Value Type
			Figure 540, Check if input value is character
			Figure 541, Check if input value is numeric
			Figure 542, Example of functions in use
			Figure 543, Check if input value is numeric - part 1 of 2
			Figure 544, Check if input value is numeric - part 2 of 2
			Figure 545, Example of function in use
		Hash Function
			Figure 546, Create HASH_STRING function
			Figure 547, HASH_STRING function usage
			Figure 548, HASH_FUNCTION test
Order By, Group By, and Having
	Order By
		Figure 549, ORDER BY syntax
		Sample Data
			Figure 550, ORDER BY sample data definition
		Order by Examples
			Figure 551, Simple ORDER BY
			Figure 552, Case insensitive ORDER BY
			Figure 553, ORDER BY on not-displayed column
			Figure 554, ORDER BY second byte of first column
			Figure 555, ORDER BY in bit-data sequence
			ORDER BY subselect
				Figure 556, ORDER BY nested ORDER BY
				Figure 557, Multiple nested ORDER BY statements
			ORDER BY inserted rows
				Figure 558, ORDER BY insert input sequence
	Group By and Having
		Figure 559, GROUP BY syntax
		Rules and Restrictions
		GROUP BY Flavors
			Figure 560, Possible groupings
			Figure 561, Three ways to write the same GROUP BY
			Usage Warnings
		GROUP BY Sample Data
			Figure 562, GROUP BY Sample Data
		Simple GROUP BY Statements
			Sample Queries
				Figure 563, Simple GROUP BY
				Figure 564, GROUP BY on non-displayed field
				Figure 565, GROUP BY on derived field, not shown
				Figure 566, GROUP BY on derived field, shown
			Figure 567, GROUPING SETS in parenthesis vs. not
			Figure 568, Multiple GROUPING SETS
			Figure 569, Simple GROUP BY expression and GROUPING SETS combined
			Figure 570, Mixing simple GROUP BY expressions and GROUPING SETS
			Figure 571, GROUPING SETS with multiple components
			Figure 572, GROUPING SET with multiple components, using grand-total
			Figure 573, GROUPING SET with multiple components, using grand-total
			SQL Examples
				Figure 574, Multiple GROUPING SETS, making one GROUP BY
				Figure 575, Multiple GROUPING SETS, making two GROUP BY results
				Figure 576, Repeated field essentially ignored
				Figure 577, Repeated field impacts query result
				Figure 578, Repeated field impacts query result
		ROLLUP Statement
			Figure 579, ROLLUP vs. GROUPING SETS
			Figure 580, ROLLUP vs. GROUPING SETS
			Figure 581, ROLLUP vs. GROUPING SETS
			Figure 582, Multiplying GROUPING SETS
			SQL Examples
				Figure 583, Simple GROUP BY
				Figure 584, GROUP BY with ROLLUP
				Figure 585, ROLLUP done the old-fashioned way
				Figure 586, Repeating a field in GROUP BY and ROLLUP (error)
				Figure 587, Repeating a field, explanation
				Figure 588, GROUP BY on 1st field, ROLLUP on 2nd
				Figure 589, ROLLUP on DEPT, then SEX
				Figure 590, ROLLUP on SEX, then DEPT
				Figure 591, ROLLUP on SEX, then DEPT
				Figure 592, Two independent ROLLUPS
				Figure 593, Combined-field ROLLUP
				Figure 594, Use HAVING to get only grand-total row
				Figure 595, Use GROUPING SETS to get grand-total row
				Figure 596, Use GROUP BY to get grand-total row
				Figure 597, Get grand-total row directly
		CUBE Statement
			Figure 598, CUBE vs. GROUPING SETS
			Figure 599, CUBE vs. GROUPING SETS
			Figure 600, CUBE vs. GROUPING SETS
			SQL Examples
				Figure 601, CUBE example
				Figure 602, CUBE expressed using multiple GROUPING SETS
				Figure 603, CUBE on compound fields
				Figure 604, CUBE on compound field, explanation
		Complex Grouping Sets - Done Easy
			Figure 605, Basic GROUP BY example
			Figure 606, Sub-totals that we want to get
			Figure 607, Get lots of sub-totals, using CUBE
			Figure 608, Predicates used - explanation
			Figure 609, Get lots of sub-totals, using ROLLUP
		Group By and Order By
			Figure 610, GROUP BY with ORDER BY
		Group By in Join
			Figure 611, GROUP BY on one side of join - using common table expression
			Figure 612, GROUP BY on one side of join - using fullselect
		COUNT and No Rows
			Figure 613, COUNT and No Rows
	Why Joins Matter
	Sample Views
		Figure 614, Sample Views used in Join Examples
	Join Syntax
		Figure 615, Join Syntax #1
		Figure 616, Sample two-table join
		Figure 617, Sample three-table join
		Figure 618, Join Syntax #2
		Figure 619, Sample two-table inner join
		Figure 620, Sample three-table inner join
		Query Processing Sequence
			Figure 621, Query Processing Sequence
		ON vs. WHERE
			Figure 622, Sample Views used in Join Examples
			Figure 623, Sample Views used in Join Examples
	Join Types
		Inner Join
			Figure 624, Example of Inner Join
			Figure 625, Inner Join SQL (1 of 2)
			Figure 626, Inner Join SQL (2 of 2)
			ON and WHERE Usage
				Figure 627, Inner join, using ON check
				Figure 628, Inner join, using WHERE check
		Left Outer Join
			Figure 629, Example of Left Outer Join
			Figure 630, Left Outer Join SQL (1 of 2)
			Figure 631, Left Outer Join SQL (2 of 2)
			ON and WHERE Usage
				Figure 632, ON check on table being joined to
				Figure 633, WHERE check on table being joined to (1 of 2)
				Figure 634, WHERE check on table being joined to (2 of 2)
				Figure 635, ON check on table being joined from
				Figure 636, WHERE check on table being joined from
		Right Outer Join
			Figure 637, Example of Right Outer Join
			Figure 638, Right Outer Join SQL (1 of 2)
			Figure 639, Right Outer Join SQL (2 of 2)
			ON and WHERE Usage
		Full Outer Joins
			Figure 640, Example of Full Outer Join
			Figure 641, Full Outer Join SQL
			Figure 642, Full Outer Join SQL
			ON and WHERE Usage
				Figure 643, Full Outer Join, match on keys
				Figure 644, Full Outer Join, match on keys > 20
				Figure 645, Full Outer Join, match on keys (no rows match)
				Figure 646, Full Outer Join, don't match on keys (no rows match)
				Figure 647, Full Outer Join, don't match on keys (all rows match)
				Figure 648, Full Outer Join, turned into an inner join by WHERE
				Figure 649, Outer join V1.ID < 30, sample data
				Figure 650, Outer join V1.ID < 30, check applied in WHERE (after join)
				Figure 651, Outer join V1.ID < 30, check applied in ON (during join)
				Figure 652, Outer join V1.ID < 30, check applied in WHERE (before join)
				Figure 653, Outer join V1.ID < 30, (gives wrong answer - see text)
				Figure 654, Outer join V1.ID < 30, (gives wrong answer - see text)
		Cartesian Product
			Figure 655, Example of Cartesian Product
			Figure 656, Cartesian Product SQL (1 of 2)
			Figure 657, Cartesian Product SQL (2 of 2)
			Figure 658, Partial Cartesian Product SQL
			Figure 659, Partial Cartesian Product SQL, with GROUP BY
	Join Notes
		Using the COALESCE Function
			Figure 660, Use of COALESCE function in outer join
		Listing non-matching rows only
			Figure 661, Example of outer join, only getting the non-matching rows
			Figure 662, Outer Join SQL, getting only non-matching rows
			Figure 663, Outer Join SQL, getting only non-matching rows
			Figure 664, Outer Join SQL, getting only non-matching rows
			Figure 665, Outer Join SQL, getting only non-matching rows
		Join in SELECT Phrase
			Figure 666, Left outer join example
			Figure 667, Outer Join done in FROM phrase of SQL
			Figure 668, Outer Join done in SELECT phrase of SQL
			Figure 669, Outer Join done in SELECT phrase of SQL - gets error
			Figure 670, Outer Join done in SELECT phrase of SQL - fixed
			Figure 671, Same as prior query - using join and GROUP BY
			CASE Usage
				Figure 672, Sample Views used in Join Examples
			Multiple Columns
				Figure 673, Outer Join done in SELECT, 2 columns
				Figure 674, Outer Join done in FROM, 2 columns
			Column Functions
				Figure 675, Running total, using JOIN in SELECT
				Figure 676, Running total, using OLAP function
		Predicates and Joins, a Lesson
			Figure 677, Outer join, with WHERE filter
			Figure 678, Outer Join, WHERE done after - wrong
			Figure 679, Outer Join, WHERE done before - correct
			Figure 680, Outer Join, WHERE done independently - correct
		Joins - Things to Remember
		Complex Joins
			Figure 681, Join from Employee to Activity and Photo
			Figure 682, Join from Employee to Activity, then from Activity to Photo
			Outer Join followed by Inner Join
				Figure 683, Complex join - wrong
				Figure 684, Complex join - right
			Simplified Nested Table Expression
				Figure 685, Complex join - right
	Sample Tables
		Figure 686, Sample tables used in sub-query examples
	Sub-query Flavors
		Sub-query Syntax
			Figure 687, Sub-query syntax diagram
			No Keyword Sub-Query
				Figure 688, No keyword sub-query, works
				Figure 689, No keyword sub-query, fails
			SOME/ANY Keyword Sub-Query
				Figure 690, ANY sub-query
				Figure 691, ANY and ALL vs. column functions
			All Keyword Sub-Query
				Figure 692, ALL sub-query, with non-empty sub-query result
				Figure 693, ALL sub-query, with empty sub-query result
				Figure 694, ALL sub-query, with extra check for empty set
			EXISTS Keyword Sub-Query
				Figure 695, EXISTS sub-query, always returns a match
				Figure 696, EXISTS sub-query, always returns a non-match
				Figure 697, EXISTS sub-query, always returns a match
			NOT EXISTS Keyword Sub-query
				Figure 698, NOT EXISTS vs. ALL, ignore nulls, find match
				Figure 699, NOT EXISTS vs. ALL, ignore nulls, no match
				Figure 700, NOT EXISTS vs. ALL, process nulls
				Figure 701, List of values in T2C <> T1A value
				Figure 702, NOT EXISTS - same as ALL
			IN Keyword Sub-Query
				Figure 703, IN sub-query example, two matches
				Figure 704, IN sub-query example, no matches
				Figure 705, IN and = ANY sub-query examples, with nulls
			NOT IN Keyword Sub-Queries
				Figure 706, NOT IN sub-query example, no matches
				Figure 707, NOT IN sub-query example, matches
				Figure 708, NOT EXISTS sub-query example, matches
		Correlated vs. Uncorrelated Sub-Queries
			Figure 709, Uncorrelated sub-query
			Figure 710, Correlated sub-query
			Figure 711,Correlated sub-query, with correlation names
			Which is Faster
		Multi-Field Sub-Queries
			Figure 712, Multi-field sub-queries, equal checks
			Figure 713, Multi-field sub-query, with non-equal check
		Nested Sub-Queries
			Figure 714, Nested Sub-Queries
	Usage Examples
		Beware of Nulls
			Figure 715, Getting a null value from a not null field
		True if NONE Match
			Figure 716, Sub-queries, true if none match
			Using a Join
				Figure 717, Outer join, true if none match
		True if ANY Match
			Figure 718, Sub-queries, true if any match
			Using a Join
				Figure 719, Joins, true if any match
		True if TEN Match
			Figure 720, Sub-queries, true if ten match (1 of 2)
			Figure 721, Sub-queries, true if ten match (2 of 2)
			Using a Join
				Figure 722, Joins, true if ten match
		True if ALL match
			Figure 723, Sub-queries, true if all match, find rows
			Figure 724, Sub-queries, true if all match, empty set
			False if no Matching Rows
				Figure 725, Sub-queries, true if all match, and at least one value found
Union, Intersect, and Except
	Figure 726, Examples of Union, Except, and Intersect
		Syntax Diagram
			Figure 727, Union, Except, and Intersect syntax
		Sample Views
			Figure 728, Query sample views
	Usage Notes
		Union & Union All
			Figure 729, Union and Union All SQL
		Intersect & Intersect All
			Figure 730, Intersect and Intersect All SQL
		Except, Except All, & Minus
			Figure 731, Except and Except All SQL (R1 on top)
			Figure 732, Except and Except All SQL (R2 on top)
		Precedence Rules
			Figure 733, Use of parenthesis in Union
		Unions and Views
			Figure 734, Define view to combine yearly tables
			Figure 735, Insert, update, and delete using view
			Figure 736, View contents after insert, update, delete
Materialized Query Tables
		Figure 737, Sample materialized query table DDL
		Figure 738, Original and optimized queries
			DB2 Optimizer Issues
	Usage Notes
		Figure 739, Materialized query table DDL, syntax diagram
		Syntax Options
			Query Optimization
			Maintained By
			Options vs. Actions
				Figure 740, Materialized query table options vs. allowable actions
		Select Statement
			Refresh Deferred Tables
			Refresh Immediate Tables
		Optimizer Options
				Figure 741, Changing default refresh age for database
				Figure 742, Set refresh age command, syntax
				Figure 743, Set refresh age command, examples
				Figure 744, Changing default maintained type for database
				Figure 745,Set maintained type command, syntax
				Figure 746, Set maintained type command, examples
				Figure 747, Changing default maintained type for database
				Figure 748,Set maintained type command, syntax
				figure 749, Set query optimization, example
			What Matches What
				Figure 750, When DB2 will consider using a materialized query table
			Selecting Special Registers
				Figure 751, Selecting special registers
		Refresh Deferred Tables
			Figure 752, Refresh deferred materialized query table DDL
		Refresh Immediate Tables
			Figure 753, Refresh immediate materialized query table DDL
			Figure 754, Query that uses materialized query table (1 of 3)
			Figure 755, Query that uses materialized query table (2 of 3)
			Figure 756, Query that uses materialized query table (3 of 3)
			Using Materialized Query Tables to Duplicate Data
				Figure 757, Create source table
				Figure 758, Create duplicate data table
				Figure 759, Create table - duplicate certain rows only
				Figure 760, Create source table
				Figure 761, Materialized query table on join
			Queries that don't use Materialized Query Table
				Figure 762, Query that doesn't use materialized query table (1 of 2)
				Figure 763, Query that doesn't use materialized query table (2 of 2)
		Usage Notes and Restrictions
			Figure 764, Materialized query table refresh commands
		Multi-table Materialized Query Tables
			Figure 765, Multi-table materialized query table DDL
			Figure 766, Query that uses materialized query table
			Figure 767, DB2 generated query to use materialized query table
			Rules and Restrictions
			Three-table Example
				Figure 768, Three-table materialized query table DDL
				Figure 769, Query that uses materialized query table
				Figure 770, DB2 generated query to use materialized query table
		Indexes on Materialized Query Tables
			Figure 771, Indexes for DPT_EMP_ACT_SUMRY materialized query table
			Figure 772, Sample query that use WORKDEPT index
			Figure 773, Sample query that uses NUM_ROWS index
		Organizing by Dimensions
			Figure 774, Materialized query table organized by dimensions
		Using Staging Tables
			Figure 775, Sample materialized query table
			Figure 776, Staging table for the above materialized query table
			Additional Columns
			Using a Staging Table
				Figure 777, Enabling and the using a staging table
Identity Columns and Sequences
	Living With Gaps
	Living With Sequence Errors
	Identity Columns
		Figure 778, Identity Column syntax
		Figure 779, Identity column, sample table
		Rules and Restrictions
			Syntax Notes
			Identity Column Examples
				Figure 780, Identity column, ascending sequence
				Figure 781, Identity column, descending sequence
				Figure 782, Identity column, dumb sequence
				Figure 783, Identity column, odd values, then even, then stuck
			Usage Examples
				Figure 784, Identity column, definition
				Figure 785, Invoice table, sample inserts
				Figure 786, Invoice table, after inserts
		Altering Identity Column Options
			Figure 787, Invoice table, restart identity column value
			Figure 788, Invoice table, more sample inserts
			Figure 789, Invoice table, after second inserts
			Alter Usage Notes
				Figure 790, Identity Column alter syntax
		Gaps in Identity Column Values
			Figure 791, Gaps in Values, example
		Find Gaps in Values
			Figure 792, Find gaps in values
			Figure 793, Find gaps in values
			Figure 794, Find gaps in values
			Figure 795, Selecting identity column values inserted
			Figure 796, IDENTITY_VAL_LOCAL function examples
			Figure 797, IDENTITY_VAL_LOCAL function examples
			Figure 798, IDENTITY_VAL_LOCAL usage in predicate
		Figure 799, Create sequence
		Figure 800, Alter sequence attributes
			Constant Sequence
				Figure 801, Sequence that doesn't change
		Getting the Sequence Value
			Figure 802, Selecting the NEXTVAL
			NEXTVAL and PREVVAL - Usage Notes
			NEXTVAL and PREVVAL - Usable Statements
			NEXTVAL - Usable Statements
			NEXTVAL and PREVVAL - Not Allowed In
			NEXTVAL - Not Allowed In
			PREVVAL - Not Allowed In
			Usage Examples
				Figure 803, Use of NEXTVAL and PREVVAL expressions
				Figure 804, NEXTVAL values used but not retrieved
		Multi-table Usage
			Figure 805, Create tables that use a common sequence
			Figure 806, Insert into tables with common sequence
			Figure 807, Get previous value - select
			Figure 808, Get previous value - into host-variable
		Counting Deletes
			Figure 809, Count deletes done to table
		Identity Columns vs. Sequences - a Comparison
	Roll Your Own
		Figure 810, Sample table, roll your own sequence#
		Figure 811, Sample trigger, roll your own sequence#
		Figure 812, Sample inserts, roll your own sequence#
		Support Multi-row Inserts
			Control Table
				Figure 813, Control Table, DDL
				Figure 814, Control Table, sample inserts
			Data Table
				Figure 815, Sample Data Table, DDL
				Figure 816, Before trigger
				Figure 817, After trigger
				Figure 818, Update trigger
			Design Comments
Temporary Tables
		Single Use in Single Statement
			Figure 819, Nested Table Expression
			Figure 820, Common Table Expression
			Multiple Use in Single Statement
				Figure 821, Common Table Expression
			Multiple Use in Multiple Statements
				Figure 822, Declared Global Temporary Table
	Temporary Tables - in Statement
		Figure 823, Identical query (1 of 3) - using Common Table Expression
		Figure 824, Identical query (2 of 3) - using fullselect in FROM
		Figure 825, Identical query (3 of 3) - using fullselect in SELECT
		Common Table Expression
			Figure 826, Common Table Expression Syntax
			Select Examples
				Figure 827, Common Table Expression, using named fields
				Figure 828, Common Table Expression, using unnamed fields
				Figure 829, Query with two common table expressions
				Figure 830, Same as prior example, but using nested table expressions
				Figure 831, Deriving second temporary table from first
			Insert Usage
				Figure 832, Insert using common table expression
				Figure 833, Equivalent insert (to above) without common table expression
			Full-Select in FROM Phrase
				Figure 834, Nested column function usage
				Figure 835, Nested fullselects
				Figure 836, Join fullselect to real table
			Table Function Usage
				Figure 837, Fullselect with external table reference
				Figure 838, Fullselect without external table reference
			Full-Select in SELECT Phrase
				Figure 839, Use an uncorrelated Full-Select in a SELECT list
				Figure 840, Use a correlated Full-Select in a SELECT list
				Figure 841, Use correlated and uncorrelated Full-Selects in a SELECT list
			INSERT Usage
				Figure 842, Fullselect in INSERT
			UPDATE Usage
				Figure 843, Use uncorrelated Full-Select to give workers company AVG salary (+$2000)
				Figure 844, Use correlated Full-Select to give workers department AVG salary (+$2000)
				Figure 845, Update two fields by referencing Full-Select
	Declared Global Temporary Tables
		Figure 846, Declared Global Temporary Table syntax
			Usage Notes
			Sample SQL
				Figure 847, Declare Global Temporary Table - define columns
				Figure 848, Declare Global Temporary Table - like another table
				Figure 849, Declare Global Temporary Table - like query output
				Figure 850, Temporary table with index
				Figure 851, Dropping a temporary table
				Figure 852, Create USER TEMPORARY tablespace
			Do NOT use to Hold Output
Recursive SQL
	Use Recursion To
	When (Not) to Use Recursion
	How Recursion Works
		Figure 853, Sample Table description - Recursion
		List Dependents of AAA
			Figure 854, SQL that does Recursion
			Figure 855, Recursive processing sequence
		Notes & Restrictions
		Sample Table DDL & DML
			Figure 856, Sample Table DDL - Recursion
	Introductory Recursion
		List all Children #1
			Figure 857, List of children of AAA
		List all Children #2
			Figure 858, List all children of AAA
		List Distinct Children
			Figure 859, List distinct children of AAA
			Figure 860, List distinct children of AAA
		Show Item Level
			Figure 861, Show item level in hierarchy
		Select Certain Levels
			Figure 862, Select rows where LEVEL < 3
			Figure 863, Select rows where LEVEL < 3
		Select Explicit Level
			Figure 864, Select rows where LEVEL = 2
		Trace a Path - Use Multiple Recursions
			Figure 865, Find all children and parents of DDD
		Extraneous Warning Message
			Figure 866, Recursion - with warning message
			Figure 867, Recursion - without warning message
	Logical Hierarchy Flavours
		Figure 868, Hierarchy Flavours
		Divergent Hierarchy
			Figure 869, Divergent Hierarchy - Table and Layout
		Convergent Hierarchy
			Figure 870, Convergent Hierarchy - Tables and Layout
		Recursive Hierarchy
			Figure 871, Recursive Hierarchy - Tables and Layout
		Balanced & Unbalanced Hierarchies
			Figure 872, Balanced and Unbalanced Hierarchies
		Data & Pointer Hierarchies
	Halting Recursive Processing
		Sample Table DDL & DML
			Figure 873, Recursive Hierarchy - Sample Table and Layout
			Figure 874, Sample Table DDL - Recursive Hierarchy
			Other Loop Types
		Stop After "n" Levels
			Figure 875, Stop Recursive SQL after "n" levels
		Stop When Loop Found
			Define Function
				Figure 876, LOCATE_BLOCK user defined function
				Figure 877, LOCATE_BLOCK function example
			Use Function
				Figure 878, Show path, and rows in loop
				Figure 879, Use LOCATE_BLOCK function to stop recursion
				Figure 880, Use LOCATE_BLOCK function to stop recursion
				Figure 881,List rows that point back to a parent
				Figure 882, Delete rows that loop back to a parent
			Working with Other Key Types
		Keeping the Hierarchy Clean
			Figure 883, INSERT trigger
			Figure 884, UPDATE trigger
			Figure 885, Invalid DML statements
	Clean Hierarchies and Efficient Joins
		Limited Update Solution
			Figure 886, Data Hierarchy, with normalized and exploded representations
			Figure 887, Hierarchy table that does not allow updates to PKEY
			Creating an Exploded Equivalent
				Figure 888, Exploded table CREATE statement
				Figure 889, Trigger to maintain exploded table after delete in hierarchy table
				Figure 890, Trigger to maintain exploded table after insert in hierarchy table
			Querying the Exploded Table
				Figure 891, Querying the exploded table
		Full Update Solution
			Figure 892, Hierarchy table that allows updates to PKEY
			Figure 893, Trigger to check for recursive data structures before update of PKEY
			Creating an Exploded Equivalent
				Figure 894, Exploded table CREATE statement
				Figure 895, Trigger to maintain exploded table after delete in hierarchy table
				Figure 896, Trigger to maintain exploded table after insert in hierarchy table
				Figure 897, Trigger to run after update of PKEY in hierarchy table (part 1 of 2)
				Figure 898, Trigger to run after update of PKEY in hierarchy table (part 2 of 2)
			Querying the Exploded Table
				Figure 899, Querying the exploded table
	Trigger Syntax
		Figure 900, Create Trigger syntax
		Usage Notes
			Trigger Types
			Action Type
			Object Type
			Application Scope
			When Check
		Trigger Usage
	Trigger Examples
		Sample Tables
			Figure 901, Sample Tables
		Before Row Triggers - Set Values
			Figure 902, Before insert trigger - set values
			Figure 903, Before update trigger - set values
		Before Row Trigger - Signal Error
			Figure 904, Before Trigger - flag error
		After Row Triggers - Record Data States
			Figure 905, After Trigger - record insert
			Figure 906, After Trigger - record update
				Figure 907, After Trigger - record delete
		After Statement Triggers - Record Changes
			Figure 908, After Trigger - record insert
			Figure 909, After Trigger - record update
			Figure 910, After Trigger - record delete
		Examples of Usage
			Figure 911, Sample DML statements
			Tables After DML
				Figure 912, Customer-balance table rows
				Figure 913, Customer-history table rows
				Figure 914, Customer-transaction table rows
Protecting Your Data
	Issues Covered
	Issues Not Covered
	Sample Application
		Figure 915, Sample application tables
			Customer Balance Table
			US Sales Table
		Enforcement Tools
		Distinct Data Types
			Figure 916, Create US-dollars data type
		Customer-Balance Table
			Figure 917, Customer-Balance table DDL
		US-Sales Table
			Figure 918, US-Sales table DDL
			Generated Always Timestamp Columns
				Figure 919, Select run after multi-row insert
				Figure 920, Row change timestamp usage
				Figure 921, Update that checks for intervening updates
			Customer-Balance - Insert Trigger
				Figure 922, Set values during insert
			Customer-Balance - Update Triggers
				Figure 923, Set update-timestamp during update
				Figure 924, Prevent update of insert-timestamp
				Figure 925, Prevent update of sales fields
			US-Sales - Insert Triggers
				Figure 926, Define sequence
				Figure 927, Insert trigger
				Figure 928, Propagate change to Customer-Balance table
			US-Sales - Update Triggers
				Figure 929, Prevent updates to selected columns
				Figure 930, Propagate change to Customer-Balance table
			Tools Used
Retaining a Record
	Schema Design
		Recording Changes
			Figure 931, Customer table
			Figure 932, Customer-history table
			Table Design
				Figure 933, Insert trigger
				Figure 934, Update trigger
				Figure 935, Delete trigger
				Figure 936, Profile table
				Figure 937, View of Customer history
		Multiple Versions of the World
			Version Table
				Figure 938, Version table
			Profile Table
				Figure 939, Profile table
			Customer (data) Table
				Figure 940, Customer table
				Figure 941, Customer view - 1 of 2
				Figure 942, Customer view - 2 of 2
			Insert Trigger
				Figure 943, Insert trigger
			Update Trigger
				Figure 944, Update trigger
			Delete Trigger
				Figure 945, Delete trigger
			Sample Code
Using SQL to Make SQL
	Export Command
		Figure 946, Generate SQL to count rows
		Figure 947, Export generated SQL statements
			Export Command Notes
		SQL to Make SQL
			Figure 948, Generate SQL to count rows
			Figure 949, Generate SQL to count rows
			Figure 950, Generate SQL to count rows (all tables)
Running SQL Within SQL
		Figure 951, Sample query
		Figure 952, Sample pseudo-query
		Generate SQL within SQL
			Figure 953, Sample pseudo-query
			Figure 954, Count matching rows in all matching tables
		Make Query Column-Independent
			Figure 955, Sample pseudo-query
			Figure 956, Select all matching columns/rows in all matching tables
			Figure 957, Transpose query output
		Business Uses
			Frictionless Query
			Adaptive Query
			Meta-Data to Real-Data Join
		Meta Data Dictionaries
	DB2 SQL Functions
		Figure 958, List tables, and count rows in same
		Function and Stored Procedure Used
			Figure 959, return_INTEGER function
			Figure 960, return_INTEGER stored procedure
		Different Data Types
			Figure 961, return_DECIMAL function
			Figure 962, return_DECIMAL stored procedure
		Usage Examples
			Figure 963, List tables never had RUNSTATS
			Efficient Queries
				Figure 964, List tables with a row for A00 department
				Figure 965, List suitably-indexed tables with a row for A00 department
	Java Functions
		Scalar Functions
			Figure 966, CREATE FUNCTION code
			Figure 967, CREATE FUNCTION java code
			Java Logic
			Usage Example
				Figure 968, Java function usage example
		Tabular Functions
			Figure 969, CREATE FUNCTION code
			Figure 970, CREATE FUNCTION java code
			Java Logic
			Usage Example
				Figure 971, Use Tabular Function
		Transpose Function
			Figure 972, Cool query pseudo-code
			Figure 973, Select rows
			Figure 974, Select rows – then transpose
			Figure 975, Select rows in any table – then transpose
			Figure 976, Select rows in any table – answer
			Query Logic
				Figure 977, Select rows in any table – then transpose (part 1 of 2)
				Figure 978, Select rows in any table – then transpose (part 2 of 2)
				Figure 979, Select rows in any table – answer
				Figure 980, Queries generated above
			Function Definition
				Figure 981, Create transpose function
			Java Code
				Figure 982, CREATE FUNCTION java code (part 1 of 3)
				Figure 983, CREATE FUNCTION java code (part 2 of 3)
				Figure 984, CREATE FUNCTION java code (part 3 of 3)
			Java Logic
	Update Real Data using Meta-Data
		Figure 985, Define function and stored-procedure
		Usage Examples
			Figure 986, Refresh matching tables
			Figure 987, Refresh matching tables - answer
			Figure 988, Create copies of tables - wrong
			Figure 989, Create copies of tables - right
			Figure 990, Create copies of tables, then populate
			Query Processing Sequence
				Figure 991, Query Processing Sequence
Fun with SQL
	Creating Sample Data
		Good Sample Data is
		Data Generation
			Figure 992, Use recursion to get list of 100 numbers
			Figure 993, Use user-defined-function to get list of 100 numbers
		Make Reproducible Random Data
			Figure 994, Use RAND to create pseudo-random numbers
			Using the GENERATE_UNIQUE function
		Make Random Data - Different Ranges
			Figure 995, Make differing ranges of random numbers
		Make Random Data - Varying Distribution
			Figure 996, Create RAND data with different distributions
		Make Random Data - Different Flavours
			Figure 997, Converting RAND output from number to character
		Make Test Table & Data
			Figure 998, Production-like test table DDL
			Figure 999, Production-like test table INSERT
			Figure 1000, Production-like test table, Sample Output
	Time-Series Processing
		Figure 1001, Sample Table DDL - Time Series
		Find Overlapping Rows
			Figure 1002, Overlapping Time-Series rows - Definition
			Figure 1003, Find overlapping rows in time-series
		Find Gaps in Time-Series
			Figure 1004, Find gap in Time-Series, SQL
			Figure 1005, Find gap in Time-Series, Answer
			Figure 1006, Find gap in Time-Series
		Show Each Day in Gap
			Figure 1007, Show each day in Time-Series gap
	Other Fun Things
		Randomly Sample Data
			Figure 1008, TABLESAMPLE Syntax
				Figure 1009, Sample rows in STAFF table
				Figure 1010, Sample rows in two tables
				Figure 1011, Sample Views used in Join Examples
		Convert Character to Numeric
			Figure 1012, Convert Character to Numeric - SQL
			Figure 1013, Acceptable conversion values
			Checking the Input
				Figure 1014, Checking for non-digits
				Figure 1015, Check Numeric function, part 1 of 2
				Figure 1016, Check Numeric function, part 2 of 2
		Convert Number to Character
			Figure 1017, CHAR and DIGITS function usage
			Figure 1018, User-defined functions - convert integer to character
			Figure 1019, Convert SMALLINT to CHAR
			Decimal Input
				Figure 1020, User-defined function - convert decimal to character
				Figure 1021, Convert DECIMAL to CHAR
			Adding Commas
				Figure 1022, User-defined function - convert decimal to character - with commas
				Figure 1023, Convert DECIMAL to CHAR with commas
		Convert Timestamp to Numeric
			Figure 1024, Convert Timestamp to number
		Selective Column Output
			Figure 1025, Sample query with no column control
			Figure 1026, Sample query with column control
		Making Charts Using SQL
			Figure 1027, Make chart using SQL
			Figure 1028, Make chart of fixed length
			Figure 1029, Make two fixed length charts in the same column
		Multiple Counts in One Pass
			Figure 1030, Use GROUP BY to get counts
			Figure 1031, Use Common Table Expression to get counts
			Figure 1032, Use CASE and SUM to get counts
			Figure 1033, Use CASE and SUM to get counts
		Find Missing Rows in Series / Count all Values
			Figure 1034, Count staff joined per year
			Figure 1035, Count staff joined per year, all years
			Figure 1036, Count staff joined per year, all years
			Figure 1037, List years when no staff joined
		Multiple Counts from the Same Row
			Figure 1038, Multiple counts in one pass, SQL
			Figure 1039, Multiple counts in one pass, Answer
		Normalize Denormalized Data
			Figure 1040, Break text into words - SQL
			Figure 1041, Break text into words - Answer
		Denormalize Normalized Data
			Figure 1042, Denormalize Normalized Data - SQL
			Figure 1043, Denormalize Normalized Data - Answer
			Figure 1044, Denormalize Normalized Data - SQL
			Figure 1045, Creating a function to denormalize names
		Transpose Numeric Data
			Figure 1046, Data Transformation Functions
			Figure 1047, Transform numeric data - part 1 of 2
			Figure 1048, Transform numeric data - part 2 of 2
			Figure 1049, Contents of first temporary table and final output
		Reversing Field Contents
			Input vs. Output
				Figure 1050, Reversing character field
				Figure 1051, Reversing numeric field
		Fibonacci Series
			Figure 1052, Fibonacci Series function
			Figure 1053, Fibonacci Series generation
		Business Day Calculation
			Figure 1054, Calculate number of business days between two dates
			Figure 1055, Use business-day function
		Query Runs for "n" Seconds
			Figure 1056, Run query for four seconds
			Function to Pause for "n" Seconds
				Figure 1057, Function that pauses for "n" seconds
				Figure 1058, Query that uses pause function
		Sort Character Field Contents
			Figure 1059, Define sort-char function
			Figure 1060, Use sort-char function
		Calculating the Median
			Using Formula #1
				Figure 1061, Calculating the median
				Figure 1062, Get median plus average
			Using Formula #2
				Figure 1063, Calculating the median
				Figure 1064, Calculating the median
				Figure 1065, List matching rows and median
		Converting HEX Data to Number
			Figure 1066, Trigger to convert HEX value to integer
			Trigger Logic
			Usage Examples
				Figure 1067, Using trigger to convert data
				Figure 1068, Using trigger to convert data
			Usage Notes
Quirks in SQL
	Trouble with Timestamps
		Figure 1069, Timestamp comparison - Incorrect
		Figure 1070, Timestamp comparison - Correct
			Using 24 Hour Notation
				Figure 1071, Sample Table
				Figure 1072, Insert row
				Figure 1073, Select rows for given date
				Figure 1074, Select rows for given date
		No Rows Match
			Figure 1075, Query with no matching rows (1 of 8)
			Figure 1076, Query with no matching rows (2 of 8)
			Figure 1077, Query with no matching rows (3 of 8)
			Figure 1078, Query with no matching rows (4 of 8)
			Figure 1079, Query with no matching rows (5 of 8)
			Figure 1080, Query with no matching rows (6 of 8)
			Figure 1081, Query with no matching rows (7 of 8)
			Figure 1082, Query with no matching rows (8 of 8)
			Figure 1083, Always get a row, example 1 of 2
			Figure 1084, Always get a row, example 2 of 2
		Dumb Date Usage
			Figure 1085, Convert value to DB2 date, right
			Figure 1086, Convert value to DB2 date, wrong
		RAND in Predicate
			Figure 1087, Get random rows - Incorrect
			Figure 1088, Get random rows - Explanation
			Getting "n" Random Rows
				Figure 1089, Get random rows - Non-distinct
				Figure 1090, Get random rows - Distinct
			Summary of Issues
		Date/Time Manipulation
			Figure 1091, Date/Time manipulation - wrong
			Figure 1092, Date/Time manipulation - right
			The Solution
		Use of LIKE on VARCHAR
			Figure 1093, Use LIKE on CHAR field
			Figure 1094, Use LIKE on VARCHAR field
			Figure 1095, Use RTRIM to remove trailing blanks
		Comparing Weeks
			Figure 1096, Comparing week 33 over 10 years
		DB2 Truncates, not Rounds
			Figure 1097, DB2 data truncation
			Figure 1098, DB2 data rounding
		CASE Checks in Wrong Sequence
			Figure 1099, Case WHEN Processing - Incorrect
			Figure 1100, Case WHEN Processing - Correct
		Division and Average
			Figure 1101, Division and Average
		Date Output Order
			Figure 1102, DATE output in year, month, day order
			Figure 1103, DATE output in month, day, year order
		Ambiguous Cursors
			Figure 1104, Ambiguous Cursor
		Multiple User Interactions
			Figure 1105, Select from ORDER table
			Figure 1106, Sample query output
			WITH RR - Repeatable Read
			WITH RS - Read Stability
			WITH CS - Cursor Stability
			WITH UR - Uncommitted Read
				Figure 1107, WITH Option vs. Actions
			Check for Changes, Using Trigger
				Figure 1108, Select from ORDER table
				Figure 1109, Sequence of events required to fetch same row twice
			Check for Changes, Using Generated TS
				Figure 1110, Table with ROW CHANGE TIMESTAMP column
				Figure 1111, Select from ORDER table
				Figure 1112, SELECT from INSERT
			Other Solutions - Good and Bad
				Figure 1113, Two-part query
		What Time is It
			Figure 1114, Create table to hold timestamp values
			Figure 1115, Insert four rows
			Figure 1116, Table after insert
		Floating Point Numbers
			Figure 1117, Multiply floating-point number by ten, SQL
			Figure 1118, Multiply floating-point number by ten, answer
			Figure 1119, Two numbers that look equal, but aren't equal
			Figure 1120, Two numbers that look equal, but aren't equal, shown in HEX
			Figure 1121, Comparing float and decimal division
			Figure 1122, Comparing float and decimal multiplication, SQL
			Figure 1123, Comparing float and decimal multiplication, answer
			Figure 1124, Internal representation of "one tenth" in floating-point
				Figure 1125, Multiply DECFLOAT number by ten, SQL
				Figure 1126, Multiply DECFLOAT number by ten, answer
	DB2 Sample Tables
			Figure 1127, ACT sample table – DDL
			Figure 1128, ACT sample table – data
			Figure 1129, CATALOG sample table – DDL
			Figure 1130, CL_SCHED sample table – DDL
			Figure 1131, CL_SCHED sample table – data
			Figure 1132, CUSTOMER sample table – DDL
			Figure 1133, CUSTOMER sample table – data
			Figure 1134, DATA_FILE_NAMES sample table – DDL
			Figure 1135, DATA_FILE_NAMES sample table – data
			Figure 1136, DATA_FILE_NAMES sample table – data
			Figure 1137, DEPARTMENT sample table – DDL
			Figure 1138, DEPARTMENT sample table – data (part 1 of 2)
			Figure 1139, DEPARTMENT sample table – data (part 1 of 2)
			Figure 1140, EMPLOYEE sample table – DDL
			Figure 1141, EMPLOYEE sample table – data
			Figure 1142, EMPLOYEE sample table – data
			Figure 1143, EMPMDC sample table – DDL
			Figure 1144, EMPMDC sample table – data
			Figure 1145, EMPPROJACT sample table – DDL
			Figure 1146, EMPPROJACT sample table – data (part 1 of 2)
			Figure 1147, EMPPROJACT sample table – data (part 2 of 2)
			Figure 1148, EMP_PHOTO sample table – DDL
			Figure 1149, EMP_PHOTO sample table – data
			Figure 1150, EMP_RESUME sample table – DDL
			Figure 1151, EMP_RESUME sample table – data
			Figure 1152, IN_TRAY sample table – DDL
			Figure 1153, IN_TRAY sample table – data
			Figure 1154, INVENTORY sample table – DDL
			Figure 1155, INVENTORY sample table – data
			Figure 1156, ORG sample table – DDL
			Figure 1157, ORG sample table – data
			Figure 1158, PRODUCT sample table – DDL
			Figure 1159, PRODUCT sample table – data
			Figure 1160, PRODUCTSUPPLIER sample table – DDL
			Figure 1161, PROJACT sample table – DDL
			Figure 1162, PROJACT sample table – data (part 1 of 2)
			Figure 1163, PROJACT sample table – data (part 2 of 2)
			Figure 1164, PROJECT sample table – DDL
			Figure 1165, PROJECT sample table – data
			Figure 1166, PURCHASEORDER sample table – DDL
			Figure 1167, PURCHASEORDER sample table – data
			Figure 1168, SALES sample table – DDL
			Figure 1169, SALES sample table – data
			Figure 1170, STAFF sample table – DDL
			Figure 1171, STAFF sample table – data
			Figure 1172, SUPPLIERS sample table – DDL
Book Binding
	Tools Required
	Before you Start
	More Information
Document Text Contents
Page 235

DB2 V9.7 Cookbook ©

Joins 235

+-----------+ +---------+ ====================
|--|--------| |--|------| =========> -- -------- -- -----
|10|Sanders | |20|Sales | 10 Sanders 20 Sales
|20|Pernal | |30|Clerk | 10 Sanders 30 Clerk
|30|Marenghi| |30|Mgr | 10 Sanders 30 Mgr
+-----------+ |40|Sales | 10 Sanders 40 Sales
|50|Mgr | 10 Sanders 50 Mgr
+---------+ 20 Pernal 20 Sales
20 Pernal 30 Clerk
20 Pernal 30 Mgr
20 Pernal 40 Sales
20 Pernal 50 Mgr
30 Marenghi 20 Sales
30 Marenghi 30 Clerk
30 Marenghi 30 Mgr
30 Marenghi 40 Sales
30 Marenghi 50 Mgr

Figure 655, Example of Cartesian Product

Writing a Cartesian Product is simplicity itself. One simply omits the WHERE conditions:

FROM staff_v1 v1
,staff_v2 v2

Figure 656, Cartesian Product SQL (1 of 2)

One way to reduce the likelihood of writing a full Cartesian Product is to always use the in-
ner/outer join style. With this syntax, an ON predicate is always required. There is however
no guarantee that the ON will do any good. Witness the following example:

FROM staff_v1 v1
staff_v2 v2
ON 'A' <> 'B'

Figure 657, Cartesian Product SQL (2 of 2)

A Cartesian Product is almost always the wrong result. There are very few business situations
where it makes sense to use the kind of SQL shown above. The good news is that few people
ever make the mistake of writing the above. But partial Cartesian Products are very common,
and they are also almost always incorrect. Here is an example:

,v2a.job ===========
FROM staff_v2 v2a -- ----- --
,staff_v2 v2b 20 Sales 20
WHERE v2a.job = v2b.job 20 Sales 40
AND < 40 30 Clerk 30
ORDER BY 30 Mgr 30
,; 30 Mgr 50

Figure 658, Partial Cartesian Product SQL

In the above example we joined the two views by JOB, which is not a unique key. The result
was that for each JOB value, we got a mini Cartesian Product.

Page 236

Graeme Birchall ©

236 Join Notes

Cartesian Products are at their most insidious when the result of the (invalid) join is feed into
a GROUP BY or DISTINCT statement that removes all of the duplicate rows. Below is an
example where the only clue that things are wrong is that the count is incorrect:

,COUNT(*) AS #rows ===========
FROM staff_v1 v1 JOB #ROWS
,staff_v2 v2 ----- -----
GROUP BY v2.job Clerk 3
ORDER BY #rows Mgr 6
,v2.job; Sales 6

Figure 659, Partial Cartesian Product SQL, with GROUP BY

To really mess up with a Cartesian Product you may have to join more than one table. Note
however that big tables are not required. For example, a Cartesian Product of five 100-row
tables will result in 10,000,000,000 rows being returned.

HINT: A good rule of thumb to use when writing a join is that for all of the tables (except
one) there should be equal conditions on all of the fields that make up the various unique
keys. If this is not true then it is probable that some kind Cartesian Product is being done
and the answer may be wrong.

Join Notes

Using the COALESCE Function

If you don't like working with nulls, but you need to do outer joins, then life is tough. In an
outer join, fields in non-matching rows are given null values as placeholders. Fortunately,
these nulls can be eliminated using the COALESCE function.

The COALESCE function can be used to combine multiple fields into one, and/or to elimi-
nate null values where they occur. The result of the COALESCE is always the first non-null
value encountered. In the following example, the two ID fields are combined, and any null
NAME values are replaced with a question mark.

,COALESCE(,'?') AS name =================
,v2.job ID NAME JOB
FROM staff_v1 v1 -- -------- -----
FULL OUTER JOIN 10 Sanders -
staff_v2 v2 20 Pernal Sales
ON = 30 Marenghi Clerk
ORDER BY 30 Marenghi Mgr
,v2.job; 40 ? Sales
50 ? Mgr

Figure 660, Use of COALESCE function in outer join

Listing non-matching rows only

Imagine that we wanted to do an outer join on our two test views, only getting those rows that
do not match. This is a surprisingly hard query to write.

Page 469

DB2 V9.7 Cookbook ©

Index 469

TABLE function, 303
Terminator, statement, 22, 79
Test Data. See Sample Data

Convert to CHAR, 134
Datatype, 24
Duration, 29
Function, 176
Manipulation, 27

Time Series data, 393

24-hour notation, 427
Convert to CHAR, 134
Data type, 24
Function, 176
Generate unique, 148
Generated always, 344
Manipulation, 427, 432
ROW CHANGE TIMESTAMP special register,

Unique generation, 148

TIMESTAMP_FORMAT function, 177
TIMESTAMP_ISO function, 177
TIMESTAMPDIFF function, 177
TOTALORDER function, 27, 178
TRANSLATE function, 179, 197

Data. See Denormalize data
User-defined function, 376

BEFORE vs. AFTER triggers, 333, 345
Definition, 333
Delete counting, 290
History tables, 352, 357
Identity column, 292
INSTEAD OF triggers, 333, 357
Propagate changes, 348
Recursive hierarchies, 328, 330
ROW vs. STATEMENT triggers, 334
Sequence, 289
SIGNAL statement, 336, 347, 348
Syntax diagram, 333
Validate input, 336, 347, 348

TRIM function. See STRIP function
TRUNCATE function, 180
Truncation, numeric, 434

UCASE function, 180
Unbalanced hierarchy, 319
Uncommitted Read, 439
Uncorrelated sub-query, 252

Nested, 253

Definition, 260
Outer join usage, 228, 230
Precedence Rules, 261

Definition, 260
INSERT usage, 63, 64, 262
Recursion, 310

View usage, 262
Unique value generation

GENERATE_UNIQUE function, 147
Identity column, 277
Sequence, 286
Timestamp column, 148, 344

CASE usage, 52
Definition, 65
Fetch first n rows, 66
Fullselect, 66, 67, 305
MERGE usage, 75
Meta-data to real data, 383
Multiple tables usage, 262
Nested table expression, 66
OLAP functions, 67
RID_BIT function usage, 169
Select results, 72
Stop after n rows, 66

User defined function
Data-type conversion example, 398, 400
Denormalize example, 410
Fibonacci Series, 416
Hash string example, 199
Like predicate example, 43
Locate Block example, 322
Month number example, 195
Nullable output, 188
Numbered list generate example, 196
Pause query example, 419
Recursion usage, 322
Reverse example, 415
Scalar function, 187
Sort string example, 419
Sourced function, 185
Table function, 192
Week number example, 195

VALUE function, 181
VALUES expression

Convert columns to rows, 49
VALUES statement

Definition, 47
SELECT embedding, 48
View usage, 49

VARCHAR function, 181
VARCHAR_BIT_FORMAT function, 181
VARCHAR_FORMAT function, 181
VARCHAR_FORMAT_BIT function, 181
VARGRAPHIC function, 181
VARIANCE function, 96
Versions (history tables), 354

Data in definition, 23
DDL example, 23, 49
History tables, 353, 356
UNION ALL usage, 262

Wait. See PAUSE function

Page 470

Graeme Birchall ©


Function, 182, 434
Get prior, 195
User defined function example, 195

WEEK_ISO function, 182
WHEN. See CASE expression
WHERE vs. ON, joins, 225, 227, 228, 230, 231
WHILE statement, 84
WITH statement

Cursor Stability, 438
Defintion, 300
Insert usage, 64, 302
Isolation level, 437
MAX values, getting, 114
Multiple tables, 301
Read Stability, 438
Recursion, 310

Repeatable Read, 438
Uncommitted Read, 439
VALUES expression, 48

X (hex) notation, 44

YEAR function, 182
You are lost. See Lousy Index

Zero divide (avoid), 52
Zero rows match, 428

Similer Documents