Manuals / Brands / Computer Equipment / Computer Accessories / Fujitsu / Computer Equipment / Computer Accessories

Fujitsu J2X0-1634-01EN 6.1.4 Recalculating calculation results for a grouped table

1 227

Download 227 pages, 27.02 Mb

[Figure: Example of a common error when grouping multiple joined tables]

The MAX function or MIN function can be used to correct the previous error, as in the following example:

Example 3:

In this example, the error in Example 2 is corrected.

Because the value of STOCKQTY is the same in each group, both the maximum and minimum values for each group

can only be one value, the STOCKQTY value. Therefore, the result of this query specification is the same as in

Figure: Example of grouping multiple tables that are joined and manipulating data. However, to improve the

processing efficiency, the SQL statement should be specified as in Example 1.

6.1.4 Recalculating calculation results for a grouped table

To recalculate results that have already been calculated for a grouped table, use a derived table. When a derived table

is used, the results can be recalculated without a view table definition. The following is a specification example:

Example:

Use the derived table used to get the total stock quantity for each product from the stock table, and get

the sums for the number of products and stock quantity.

84

Contents

Main Preface Purpose Intended Readers Organization of This Manual How to Use This Manual Related manuals SymfoWARE Server Page Page Precautions Operating systems supporting SymfoWARE Programmer's Kit UNIX release version Print examples in this manual Explanatory models Abbreviations Page Page Chapter 1 Overview of SQL 1.1 Types of SQL Page 1.2 Common SQL Statement Rules 1.2.1 Names specified in SQL statements 1.2.2 Data base used in examples STOCK table ORDER table COMPANY table [Figure: Inventory management data base] Attributes of table columns in inventory management data base [Table: Attributes of table columns in the inventory management data base] Relationship between STOCK table, ORDER table, and COMPANY table Page Page Chapter 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] 2.1.2 Fetching data containing null values [Table: Column values and values stored in indicator variables] 2.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 [Figure: Example of deleting duplicate rows and fetching data] [Figure: Example of specifying DISTINCT incorrectly] 2.2 Fetching Columns that are Undefined 2.2.1 Performing arithmetic operations on data Specifying an operational expression Monadic operators Dyadic operators Priority 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] 2.2.2 Obtaining total, average, maximum, and minimum value, and row count for column values [Figure: Example of applying a set function to the results of an operational expression] Types of set functions and how to specify them [Figure: Rows processed by ALL and DISTINCT set functions] [Table: Methods of specifying set functions] Data type of results of set functions Page Chapter 3 Modifying Data 3.1 Adding data to a Data Base 3.1.1 Specifying a null value in added data [Figure: Example of adding a null value] 3.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] Specifying 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] 3.1.4 Adding data from another table [Figure: Using a query specification to add data (where the column layout is the same)] [Figure: Using a query specification to add data (when column layout does not correspond)] Page 3.2 Updating data in a Data Base 3.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] 3.2.3 Using default values in data to be updated [Figure: Updating data using a default value] 3.2.4 Specifying the current date and time in data to be updated [Figure: Updating data using the current date and time] 3.2.5 Using values from other columns in data to be updated [Figure: Example of using values from other columns to update data] 3.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] 3.3 Deleting Data from a Data Base 3.3.1 Deleting all rows from a table Page Page Chapter 4 Using a Cursor to Manipulate Data 4.1 Cursor Overview Page Sequence of data manipulations using a cursor [Figure: Flowchart of using cursor to manipulation data] Page 4.2 Declaring a Cursor Query expression Query specification [Figure: Deriving a cursor table] Sequence of rows fetched using a cursor [Figure: Example of rows in a cursor table when the sequence is undefined] Reordering the sequence of rows [Figure: Example of specifying multiple sort keys] [Figure: Example of specifying column numbers in sort specification] Updatable cursors and read-only cursors Page 4.3 Opening and Closing a Cursor Opening a cursor Closing a cursor 4.4 Positioning a Cursor and Fetching Data [Figure: Example of using a FETCH statement to position a cursor] Host 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)] 4.5 Updating Data Using a Cursor Page [Figure: Example of updating data using a cursor (column to be updated is not in the curso r table)] 4.6 Deleting Data Using a Cursor [Figure: Example of deleting data using a cursor] Chapter 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] 5.2 Specifying Conditions to Join Tables [Figure: Example of specifying conditions in WHERE clause for joining tables] Page Join tables [Figure: Example in which multiple tables are joined using a join table] 5.3 Manipulating Data Using Aliases in Tables 5.4 Joining a Table to Itself and Manipulating Data Comparing different rows Page Operations on different rows [Figure: Example of performing an operation on different rows] 5.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] Including duplicate rows in aggregate without modification [Figure: Example of specifying UNION ALL in query specification] Aggregate sequence due to UNION Page Chapter 6 Methods of Manipulating Data 6.1 Grouping Tables and Manipulating Data 6.1.1 Grouping tables and fetching data Grouping tables [Figure: Example of a table derived from GROUP BY clause] GROUP BY clause Fetching data from a grouped table [Figure: Example of fetching data by grouping a table] [Figure: Example of using a set function incorrectly] Grouping tables by using parts of character strings [Figure: Example of grouping tables by using parts of character strings] Case-splitting data and grouping tables [Figure: Case-splitting data and grouping tables] Grouping tables by month [Figure: Example of grouping tables by month] 6.1.2 Specifying groups to be processed from a grouped table Page [Figure: Example of a table derived from a HAVING clause] HAVING clause 6.1.3 Grouping multiple tables that are joined and manipulating data [Figure: Example of grouping multiple tables that are joined and manipulating data] Page Page [Figure: Example of a common error when grouping multiple joined tables] 6.1.4 Recalculating calculation results for a grouped table 6.2 Specifying Various Search Conditions Specifying logical operators [Table: Truth table for AND] [Table: Truth table for OR] [Table: Truth table for NOT] Evaluation sequence of logical operators 6.2.1 Comparing two values Using a comparison operator to compare two values Page Page Page Using a subquery in a comparison predicate [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] Checking whether a value is a null value 6.2.2 Checking whether a value is in a certain range Page [Figure: Example of specifying IN predicate] Using a subquery in the IN predicate [Figure: Example of using a subquery in the IN predicate] Comparing a set of values Page Checking whether rows that satisfy certain conditions exist [Figure: Example of specifying the EXISTS predicate] 6.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] Examples of using arbitrary string specifier % Page [Figure: Example of LIKE predicate specification (using an arbitrary string specifier)] Examples of using arbitrary string specifier "_" [Figure: Example of LIKE predicate specifica tion (using an arbitrary string specifier) ] Escape characters [Figure: Example of specifying a LIKE predicate specification using escape characters] 6.3 Manipulating Numeric Data [Table: Processing for manipulating numeric data] Page 6.4 Manipulating Character String Data [Table: Processing for manipulating parts of character strings] [Figure: Example of combining character value functions to fetch data] Page 6.5 Manipulating Date Data [Table: Processing for manipulating date data] Page Page Page Page 6.6 Converting the Data Type to Manipulate Data [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] 6.7 Using CASE Expression to Manipulate Data Page Page 6.8 Specifying Row IDs to Manipulate Data 6.9 Manipulating Data using Parallel Query Page 6.10 Omitting Schema Names 6.11 Changing the User of the Current Session 6.12 Adding Name to Results Column in Query Specificati on 6.13 Manipulating Data Using Sequence Page Page Page Page Chapter 7 Executing Dynamic SQL Statements When Application Programs are Executed 7.1 Overview of Dynamic SQL SQLDA structure and SQL descriptor area SQLDA structure SQL descriptor area Preparable statements Dynamic parameter specification USING clause [Table: Uses of the USING clause and its specification format] 7.2 Modifying and Executing SQL Statements Dynamically 7.2.1 Preparing SQL statements and manipulating the SQLDA structure Page SQLN SQLD SQLVAR Page Page [Figure: Procedure for fetching execution results for a prepared statement] Preparing the SQL statement Deallocating an SQL statement SQL statements used to manipulate the SQLDA structure Page Page Page Page Page 7.2.2 Preparing SQL statements and manipulating the SQL descriptor area Page Page Page [Figure: Procedure for fetching execution results for a prepared statement] Preparing the SQL statement Deallocating an SQL statement SQL statements used to manipulate the SQL descriptor area ALLOCATE DESCRIPTOR statement DEALLOCATE DESCRIPTOR statement Page GET DESCRIPTOR statement SET DESCRIPTOR statement Page Page Page Page Page 7.3 Dynamically Modifying and Executing SQL Statement Conditions 7.3.1 Manipulating the SQLDA structure Procedure for setting dynamic parameter specification values [Figure: Procedure for setting dynamic parameter specification values for prepared statement s] SQL statements used to manipulate the SQLDA structure DESCRIBE statement 7.3.1.1 Preparing and executing dynamic SELECT statements (for SQLDA stru cture) Page Page Page Page Page Page 7.3.2 Using the SQL descriptor area Procedure for setting dynamic parameter specification values [Figure: Procedure for setting dynamic parameter specification values for prepared statement s] SQL statements used to manipulate the SQL descriptor area DESCRIBE statement GET DESCRIPTOR statement SET DESCRIPTOR statement 7.3.2.1 Preparing and executing dynamic SELECT statements (for SQL descri ptor area) Page Page Page Page Page Page Page Page Page 7.3.3 Executing other prepared statements 7.3.4 Executing prepared statements for which variable attributes are known Using the EXECUTE statement to set the values of dynamic parameter speci fications Using the EXECUTE statement to fetch execution results Using the dynamic FETCH statement to fetch execution results Page Page Page 7.5 Changing Database Names and Omitted Schema Name s 7.5.1 Changing database names 7.5.2 Changing schema names Page Page Page Page Page Page Page Page Appendix B Handling RDB Messages When executing an RDB command When compiling an application program (C language) When compiling an application program (COBOL) When executing an application program Appendix C SQLSTA TE Values SQLSTATE [Table: SQLSTATE values] Page Page Page Page Page Glossary ALL set function Application program Arbitrary character specifier Arbitrary string specifier Column Column name (item name) Commit Comparison predicate Connection Database name DEFAULT clause DELETE statement DISTINCT set function DS/90 7000 series Escape character Exception condition EXISTS predicate Function Grouped column IN predicate Indicator variable INSERT statement Join LIKE predicate Non-cursor SQL statement NOT NULL constraint Null (NULL) NULL predicate Parameter name Procedure routine Procedure routine definition Quantified predicate Quantified value list Query expression Relation operation Rollback Routine name Row Row ID Search condition Select column list Select target list Sequence Session Single row SELECT statement Sort specification SQL SQL embedded host program SQL variable Storage structure Storage structure definition Subquery Table Table declaration Target specification Trigger definition Unique constraint Updatable cursor UPDATE statement