Fujitsu J2X0-1634-01EN manuals

227 pages27.02 Mb

2Preface
- Purpose
- Intended Readers
- Organization of This Manual
- 3How to Use This Manual
- Related manuals
  - SymfoWARE Server
- 6Precautions
  - Operating systems supporting SymfoWARE Programmer's Kit
  - UNIX release version
  - Print examples in this manual
  - Explanatory models
- 7Abbreviations
10Chapter 1 Overview of SQL
- 1.1 Types of SQL
- 121.2 Common SQL Statement Rules
  - 1.2.1 Names specified in SQL statements
  - 1.2.2 Data base used in examples
  - STOCK table
  - 13ORDER table
  - COMPANY table
    - [Figure: Inventory management data base]
  - 14Attributes of table columns in inventory management data base
    - [Table: Attributes of table columns in the inventory management data base]
  - 15Relationship between STOCK table, ORDER table, and COMPANY table
18Chapter 2 Retrieving Data
- 2.1 Fetching Column Values without Modifications
  - 2.1.1 Fetching values from multiple columns
    - [Figure: Example of specifying multiple columns in a single row SELECT statement]
  - 192.1.2 Fetching data containing null values
    - 20[Table: Column values and values stored in indicator variables]
  - 212.1.3 Fetching data from multiple tables
    - [Figure: Example of joining two tables to fetch data]
  - 2.1.4 Deleting duplicate rows and fetching data
    - 22[Figure: Example of deleting duplicate rows and fetching data]
    - [Figure: Example of specifying DISTINCT incorrectly]
- 232.2 Fetching Columns that are Undefined
  - 2.2.1 Performing arithmetic operations on data
  - Specifying an operational expression
    - Monadic operators
    - Dyadic operators
    - [Table: Method of specifying and meaning of operational expressions]
  - 24Priority of operations
  - Data types for results of operational expressions
    - Data type for results of monadic operators
    - Data types for results of dyadic operators
    - [Table: Data type for results of dyadic operators]
  - 252.2.2 Obtaining total, average, maximum, and minimum value, and row count for column values
    - 26[Figure: Example of applying a set function to the results of an operational expression]
  - 27Types of set functions and how to specify them
    - [Figure: Rows processed by ALL and DISTINCT set functions]
    - [Table: Methods of specifying set functions]
  - 28Data type of results of set functions
30Chapter 3 Modifying Data
- 3.1 Adding data to a Data Base
  - 3.1.1 Specifying a null value in added data
    - 31[Figure: Example of adding a null value]
  - 323.1.2 Using default values in data to be added
  - Setting a default value for a column in which data is to be added
    - [Figure: Adding data using default values]
  - 33Specifying null values for all columns in data to be added
    - [Figure: Adding data in which default values are used for all columns]
  - 3.1.3 Setting the current date and time in data to be added
    - [Figure: Adding data using the current time]
  - 343.1.4 Adding data from another table
    - 35[Figure: Using a query specification to add data (where the column layout is the same)]
    - 36[Figure: Using a query specification to add data (when column layout does not correspond)]
- 383.2 Updating data in a Data Base
  - 393.2.1 Updating values in multiple columns
    - [Figure: Example of updating multiple columns]
  - 3.2.2 Specifying a null value for data to be updated
    - [Figure: Example in which data is updated to a null value]
  - 403.2.3 Using default values in data to be updated
    - [Figure: Updating data using a default value]
  - 413.2.4 Specifying the current date and time in data to be updated
    - [Figure: Updating data using the current date and time]
  - 423.2.5 Using values from other columns in data to be updated
    - [Figure: Example of using values from other columns to update data]
  - 433.2.6 Performing arithmetic operations on data to be updated
    - [Figure: Example in which arithmetic operations are performed on data to be updated]
  - 3.2.7 Updating all rows in a table
    - [Figure: Example of updating all rows in a table]
- 443.3 Deleting Data from a Data Base
  - 453.3.1 Deleting all rows from a table
48Chapter 4 Using a Cursor to Manipulate Data
- 4.1 Cursor Overview
  - 50Sequence of data manipulations using a cursor
    - 51[Figure: Flowchart of using cursor to manipulation data]
- 534.2 Declaring a Cursor
  - Query expression
  - Query specification
    - [Figure: Deriving a cursor table]
  - 54Sequence of rows fetched using a cursor
    - [Figure: Example of rows in a cursor table when the sequence is undefined]
  - 55Reordering the sequence of rows
    - 56[Figure: Example of specifying multiple sort keys]
    - [Figure: Example of specifying column numbers in sort specification]
  - 57Updatable cursors and read-only cursors
- 594.3 Opening and Closing a Cursor
  - Opening a cursor
  - Closing a cursor
- 4.4 Positioning a Cursor and Fetching Data
  - 60[Figure: Example of using a FETCH statement to position a cursor]
  - 61Host variable in which fetched data is stored
  - Fetching data containing null values
  - Moving the cursor in different directions
    - [Figure: Example of updating data using a cursor (column to be updated is in the cursor tab le)]
- 624.5 Updating Data Using a Cursor
  - [Figure: Example of updating data using a cursor (column to be updated is not in the curso r table)]
- 644.6 Deleting Data Using a Cursor
  - 65[Figure: Example of deleting data using a cursor]
66Chapter 5 Joining Multiple Tables and Manipulating data
- 5.1 Deriving a New Table from Multiple tables
  - [Figure: Table derived when two table names are specified in a FROM clause]
- 675.2 Specifying Conditions to Join Tables
  - 68[Figure: Example of specifying conditions in WHERE clause for joining tables]
  - 70Join tables
    - 71[Figure: Example in which multiple tables are joined using a join table]
- 725.3 Manipulating Data Using Aliases in Tables
- 735.4 Joining a Table to Itself and Manipulating Data
  - Comparing different rows
  - 75Operations on different rows
    - [Figure: Example of performing an operation on different rows]
- 765.5 Obtaining the Aggregate for Rows from Multiple Table s
  - Specifying duplicate rows as one row during aggregation
    - [Figure: Example of specifying UNION in query expression]
  - 77Including duplicate rows in aggregate without modification
    - [Figure: Example of specifying UNION ALL in query specification]
  - 78Aggregate sequence due to UNION
80Chapter 6 Methods of Manipulating Data
- 6.1 Grouping Tables and Manipulating Data
  - 6.1.1 Grouping tables and fetching data Grouping tables
    - 81[Figure: Example of a table derived from GROUP BY clause]
    - GROUP BY clause
  - 82Fetching data from a grouped table
    - 83[Figure: Example of fetching data by grouping a table]
    - [Figure: Example of using a set function incorrectly]
  - 84Grouping tables by using parts of character strings
    - [Figure: Example of grouping tables by using parts of character strings]
  - 85Case-splitting data and grouping tables
    - [Figure: Case-splitting data and grouping tables]
  - 86Grouping tables by month
    - [Figure: Example of grouping tables by month]
  - 876.1.2 Specifying groups to be processed from a grouped table
    - [Figure: Example of a table derived from a HAVING clause]
  - 89HAVING clause
  - 906.1.3 Grouping multiple tables that are joined and manipulating data
    - [Figure: Example of grouping multiple tables that are joined and manipulating data]
    - [Figure: Example of a common error when grouping multiple joined tables]
  - 936.1.4 Recalculating calculation results for a grouped table
- 946.2 Specifying Various Search Conditions
  - Specifying logical operators
    - 95[Table: Truth table for AND]
    - [Table: Truth table for OR]
    - [Table: Truth table for NOT]
  - 96Evaluation sequence of logical operators
  - 6.2.1 Comparing two values Using a comparison operator to compare two values
    - 100Using a subquery in a comparison predicate
    - 101[Figure: Example of specifying a subquery in a comparison predicate to retrieve data]
    - [Figure: Example of specifying a subquery in a comparison predicate to delete data]
  - 102Checking whether a value is a null value
  - 1036.2.2 Checking whether a value is in a certain range
  - 105[Figure: Example of specifying IN predicate]
  - Using a subquery in the IN predicate
  - [Figure: Example of using a subquery in the IN predicate]
  - 106Comparing a set of values
  - 108Checking whether rows that satisfy certain conditions exist
    - [Figure: Example of specifying the EXISTS predicate]
  - 1096.2.4 Checking whether values match a character-string pattern
    - [Figure: Using the LIKE predicate to specify rows]
    - [Table: Arbitrary string specifier and arbitrary character specifier]
  - 110Examples of using arbitrary string specifier %
    - [Figure: Example of LIKE predicate specification (using an arbitrary string specifier)]
  - 112Examples of using arbitrary string specifier "_"
    - [Figure: Example of LIKE predicate specifica tion (using an arbitrary string specifier) ]
  - 113Escape characters
    - [Figure: Example of specifying a LIKE predicate specification using escape characters]
- 1146.3 Manipulating Numeric Data
  - [Table: Processing for manipulating numeric data]
- 1166.4 Manipulating Character String Data
  - 117[Table: Processing for manipulating parts of character strings]
  - 118[Figure: Example of combining character value functions to fetch data]
- 1206.5 Manipulating Date Data
  - 121[Table: Processing for manipulating date data]
- 1266.6 Converting the Data Type to Manipulate Data
  - 127[Figure: Converting data from character string type to time interval type (year and month)]
  - [Figure: Converting data from character string type to time interval type (date and time)]
  - [Figure: Converting data from numeric type to character string type]
- 1286.7 Using CASE Expression to Manipulate Data
- 1316.8 Specifying Row IDs to Manipulate Data
- 6.9 Manipulating Data using Parallel Query
- 1336.10 Omitting Schema Names
- 1346.11 Changing the User of the Current Session
- 6.12 Adding Name to Results Column in Query Specificati on
- 1356.13 Manipulating Data Using Sequence
140Chapter 7 Executing Dynamic SQL Statements When Application Programs are Executed
- 7.1 Overview of Dynamic SQL
  - SQLDA structure and SQL descriptor area
    - SQLDA structure
  - 141SQL descriptor area
  - Preparable statements
  - 142Dynamic parameter specification
  - USING clause
    - [Table: Uses of the USING clause and its specification format]
- 1437.2 Modifying and Executing SQL Statements Dynamically
  - 7.2.1 Preparing SQL statements and manipulating the SQLDA structure
  - 145SQLN
  - SQLD
  - SQLVAR
    - [Figure: Procedure for fetching execution results for a prepared statement]
  - 148Preparing the SQL statement
  - 149Deallocating an SQL statement
  - SQL statements used to manipulate the SQLDA structure
  - 1557.2.2 Preparing SQL statements and manipulating the SQL descriptor area
    - [Figure: Procedure for fetching execution results for a prepared statement]
  - 159Preparing the SQL statement
  - 160Deallocating an SQL statement
  - SQL statements used to manipulate the SQL descriptor area
    - ALLOCATE DESCRIPTOR statement
    - DEALLOCATE DESCRIPTOR statement
    - 162GET DESCRIPTOR statement
    - SET DESCRIPTOR statement
- 1687.3 Dynamically Modifying and Executing SQL Statement Conditions
  - 7.3.1 Manipulating the SQLDA structure
  - 169Procedure for setting dynamic parameter specification values
    - [Figure: Procedure for setting dynamic parameter specification values for prepared statement s]
  - 170SQL statements used to manipulate the SQLDA structure
    - DESCRIBE statement
  - 1717.3.1.1 Preparing and executing dynamic SELECT statements (for SQLDA stru cture)
  - 1787.3.2 Using the SQL descriptor area
  - 179Procedure for setting dynamic parameter specification values
    - [Figure: Procedure for setting dynamic parameter specification values for prepared statement s]
  - 180SQL statements used to manipulate the SQL descriptor area
    - DESCRIBE statement
    - 181GET DESCRIPTOR statement
    - SET DESCRIPTOR statement
  - 1827.3.2.1 Preparing and executing dynamic SELECT statements (for SQL descri ptor area)
  - 1927.3.3 Executing other prepared statements
  - 1937.3.4 Executing prepared statements for which variable attributes are known
  - Using the EXECUTE statement to set the values of dynamic parameter speci fications
  - 194Using the EXECUTE statement to fetch execution results
  - 195Using the dynamic FETCH statement to fetch execution results
- 1997.5 Changing Database Names and Omitted Schema Name s
  - 7.5.1 Changing database names
  - 7.5.2 Changing schema names
208Appendix B Handling RDB Messages
- When executing an RDB command
- When compiling an application program (C language)
- When compiling an application program (COBOL)
- 209When executing an application program
210Appendix C SQLSTA TE Values
- SQLSTATE
  - [Table: SQLSTATE values]
216Glossary
- ALL set function
- Application program
- Arbitrary character specifier
- Arbitrary string specifier
- Base table
- BETWEEN predicate
- Boolean operator
- 217Column
- Column name (item name)
- Commit
- Comparison predicate
- Connection
- Correlation name
- Cursor
- Cursor SQL statement
- Data manipulation SQL
- 218Database name
- DEFAULT clause
- DELETE statement
- DISTINCT set function
- DS/90 7000 series
- Dynamic SQL
- Embedded exception declaration
- Embedded SQL
- 219Escape character
- Exception condition
- EXISTS predicate
- Function
- Grouped column
- Grouped table
- Host variable
- 220IN predicate
- Indicator variable
- INSERT statement
- Join
- LIKE predicate
- Logical structure
- Logical structure definition
- 221Non-cursor SQL statement
- NOT NULL constraint
- Null (NULL)
- NULL predicate
- Parameter name
- Parameter variable
- Physical structure
- Physical structure definition
- Predicate
- 222Procedure routine
- Procedure routine definition
- Quantified predicate
- Quantified value list
- Query expression
- Query specification
- Read-only cursor
- 223Relation operation
- Rollback
- Routine name
- Row
- Row ID
- Schema
- Schema definition
- Scope
- 224Search condition
- Select column list
- Select target list
- Sequence
- Session
- Set function
- Set operation
- 225Single row SELECT statement
- Sort specification
- SQL
- SQL embedded host program
- SQL variable
- SQL variable name
- SQLDA structure
- Status variable
- 226Storage structure
- Storage structure definition
- Subquery
- Table
- Table declaration
- Table expression
- Table name
- 227Target specification
- Trigger definition
- Unique constraint
- Updatable cursor
- UPDATE statement
- Value expression
- Value specification
- WHERE clause