Instruction/ maintenance manual of the product J2X0-1634-01EN Fujitsu
Go to page of 227
J2X0-1634-01E N SymfoWARE(R)Server SQL Beginner's Guide.
Preface Purpose This manual is a be ginner's guide for the S ymfoW ARE Serv er S tructured Query Lang uage (S QL). T he purpose of this manual is to help readers write programs for a variety of different data operations using SymfoWARE/RDB databases.
How to Use This Manual This manual is intended as ref erence material for us ers developin g application programs using S ymfoW ARE/RDB. First-time users of SymfoW ARE/RDB s hould read the RDB User's Guide: Dat abase D efinition and the RDB User's Guide: Application Program Devel opment before reading this manual.
SymfoW ARE Programmer's Kit Position of this manual The manual system and the position of this manual are as follows: iii.
SymfoW ARE Serv er In addition to the preceding manu als, SymfoWARE/RDB provides an online manual. Displaying Command Referenc es UNIX The man command is used to displa y the syntax of RDB commands. For details on the man command, refer to Answ erBook2 of the Reference Manual Collection.
Action in response to display ed messages UNIX The rdbprtmsg command (RDB command) gi ves the mean ing and user res ponse for each displayed mes sage. Windows NT/2000/XP See the online help of W indows NT/2000/XP .
· Microsoft(R) Windows XP Professional Operating systems supporting Sy mfoW ARE Programmer's Kit · Microsoft(R) Windows(R) 95 operating system · Microsoft(R) Windows(R) 98 operating system · .
July 2002 Microsoft, MS, MS-DOS, Windows, and Windows NT are trademarks or registered trademarks of Microsoft Corporation in the United St ates and other countries.
viii UNIX is a registered trademark in t he United St ates and other countries, licensed exclusivel y through X/Open Company Limited. Solaris is a trademark of Sun Micros ystems, Inc in the United S tates. Lotus is a registered trademar k of Lotus Development Corporatio n.
Chapter 1 Overview of SQL This chapter describes the types of SQL and the rules common to SQL statem ents in this manual. This chapter contains the following sections: 1.
[Figure: T ypes of SQL] This manual explains ho w to use data manipulation SQL st atements to w rite applicati on programs for manipulati ng data and how to use session management SQL st atements, required to manipulate data.
1.2 Common SQL S tatement Rules This section explains the rules comm on to SQL statement s in this manual. 1.2.1 Names specified in SQL st atements The following names can be specified in SQ L statements: a. Schema names b. T able names c. Column names d.
ITMNO: This column lists the code numbers assigned to the produc ts. PRODUCT : This column lists the product names. STOCKQTY : This column lists the quantities of stock for the products. WHCODE: This column lists the numbers of the wareho uses in which the pro ducts are stored.
[Figure: Inventory management dat a base] Attributes of t able columns in inventory management dat a base T able: Attributes of table columns in the inventory management data base lists t he attributes of table columns in the inventory managem ent data base.
[T able: Attributes of t able columns in the inv entory management dat a base] Relationship between STOCK t able, ORDER t able, and COMP ANY t able Figure: Relationship bet ween STOCK table, ORDER table, and COMP ANY table shows the relationship between the three tables.
[Figure: Relationship bet ween STOCK t a ble, ORDER t able, and COMP ANY t able] 7.
8.
Chapter 2 Retrieving Dat a This chapter describes the data manipulation that can be performed using the single row SELECT statement. This chapter contains the following sections: 2.1 Fetching Column V alu es without Modifications 2.2 Fetching Columns that are Undefined 2.
[Figure: Example of specifying multiple columns in a single row SELECT st atement] If all columns in a table are to be specified in the order in which they were defined, an asterisk can be us ed to specif y the columns instead of column names.
application program from the database, specif y in the indica tor variable whether the data to be fetched contains null values. When storing data specified b y the application pr ogra m in a database, also specif y in the indicator variabl e whether the data to be stored contains null values.
If fetched data has a null value and no indicator variable is specified, processing for the si ngle row SELECT statement ends in an error . If it is not known whether values for a column contain a null value, specify indicator variables.
FROM STOCKS. STOCK WHERE ITMNO < 120 [Figure: Example of deleting duplicate ro ws and fetching dat a] DISTINCT is used to form one row from fetched rows c ontaining equal values. DIST INCT can easily be used incorrectly if the user does not realize t hat multiple rows will be fetched, which will result in an error .
[Figure: Example of specify ing DISTINCT incorrectly] 2.2 Fetching Columns that are Undefined This section explains the following o perations that can be p erformed by the single row SELECT statement: · Performing arithmetic operations on data · Obtaining the total, average, maximum, and minimum values, and the row count for column values 2.
Dyadic operators The following are the four dyadic operators: + : Addition - : Subtraction * : Multiplication / : Division T able: Method of specifying and mea ning of operationa l expressions sho ws the method of specifyi ng, and the meaning of, operational expressions.
[T able: Dat a type for result s of dyadic operators] 2.2.2 Obt aining tot al, average, maximu m, and minimum value, and row count for column values So far , this manual has only explained th e fetching of data from required columns for one specified row from a table.
SELECT SUM (ORDERQTY) INTO :TOTALQTY FROM STOCKS. ORDER V arious set functions are available for determinin g values in co lumns. "SUM" is used to obta in the total value, "A VG" t.
T ypes of set functions and how to specify them The following types of set function exist: COUNT ( * ) function: Obtains the table row count including rows that contain null values A VG function: Obta.
[T able: Methods of spec ifying set functions] S pecify value expressions in arguments of ALL and DISTINCT set functions as shown in T able: Methods of specifying set functions. An operational expression that uses a column name can be specified in an a rgument.
[T able: Dat a type of columns t argeted by set functions and dat a type of the resu lts] 20.
Chapter 3 Modifying Dat a This chapter describes how to use the INS ERT st atem ent, UPDA TE statement (search ed) and DELE TE statement (searched) to modify data. This chapter contains the following sections: 3.1 Adding Data to a Data Base 3.2 Updating Data in a Data Base 3.
Therefore, if the data to be added contains a null valu e or some other value depen dent on the processing of the application program, use an indicator variab le.
3.1.2 Using default values in dat a to be added Default values, also referred to as fixed valu es, can be def ined for each column in a table when the table is defined.
Specify ing null values for all columns in data to be added T o set null values in all columns in data to be added, spec ify the keyword "DEF AUL T V ALUES" instead of specif ying an insert column list or an insert value list.
PROCESS_TIME. In this example, "212" has bee n specified for host variable P RODNO, and "15" has been specified for host variabl e ORDERQTY . INSERT INTO STOCKS.DAILY_ORDER (CUSTOMER, PRODNO, ORDERQTY, PROCESS_TIME) VALUES (61, :PRODNO, :ORDERQTY, CURRENT_TIME) [Figure: Adding dat a using the current time] 3.
[Figure: Using a query specification to add dat a (where the column layout is the same)] In Example 1, the layout of the columns in the table to which data is added and the table from which data is fetched have a one-to-one correspondenc e. Usually , the layout of columns in tables does not correspond so closel y .
[Figure: Using a query specification to add dat a (when column layout does not correspond)] Besides fetching specific values from a table, query specific ations can also be used to join and manip ulate multiple tables. Query specifications c an also be used to group tables and total their values.
[Figure: Adding dat a using a complex query specification ] 28.
3.2 Up dating dat a in a Dat a Base Use the UPDA T E statement to update data in a t able. The following is an example of the UPDA TE statement: Example: In this example, ORDERQT Y is updated to "50" for the data for PRODNO "215" and C USTOMER "61" in the ORDER table.
Data can also be updated using a cursor . For information on this method, see 4.5 "Updat i ng Data Using a Cursor". The UPDA T E statement explained in this section is called the UPDA T E statement (searched) because search conditions are used to specif y rows to be updated.
UPDATE STOCKS. ORDER SET PRICE = 216000, ORDERQTY = NULL WHERE CUSTOMER = 61 AND PRODNO = 215 Example 2: In this example, an indicator variable is us ed. The data to be updated is the same as in Example 1. In Example 2, "-1" is set for indicator variable ORDE RQT Y_INDICA TOR, then the UPDA T E statement is executed.
UPDATE STOCKS. ORDER SET ORDERQTY = DEFAULT WHERE PRODNO = 240 [Figure: Up dating dat a using a default value] 3.2.4 Specify ing the current date and time in dat a to be up dated T o specify the current date and time in data to be added, s pecify the appropriate keyword in the set clause instea d of a value.
[Figure: Up dating dat a using the current date an d time] 3.2.5 Using values from other columns in data to be up dated V alues from other columns ca n be used to update data. An example of this follows. Suppose that a table named SHIPMT table consisti ng of the four columns ITMN O, SHIPQTY , PREVSHIPMT , and V ARIA T ION exists.
[Figure: Example of using values from other columns to up date dat a] Note that when column values are specified for data to be u pdated, the values used are t hose pr ior to execution of the UPDA TE statement. In this example, SHIPQT Y is updat ed to "120", as specified by the literal.
3.2.7 Up dating all rows in a table T o update all rows in a table, do not specify an y search conditions or the keyword "WHERE". Review the example in Figure: Example of using va lues fr om other columns to update data. In this example, when shipment data was produced, the values for SHIPQTY and PREVSHIPMT were updated.
This section explains the following o peration that can be per formed by the DELETE statement: · Deleting all rows from a table A cursor can also be used to delete data. For information on using a c ursor to delete data, see 4.6 "Deleting D ata Using a Cursor".
[Figure: Example of deleting all rows from a t able] 37.
38.
Chapter 4 Using a Cursor to Manipulate Dat a This chapter describes how to use a cursor to manip ulate data. This chapter contains the following sections: 4.1 Cursor Overview 4.2 Declaring a Cursor 4.3 Opening and Closing a C ursor 4.4 Positioning a Cursor and Fetching D ata 4.
[Figure: Cursor overview] 40.
Sequence of dat a manipulations using a cursor Cursor SQL statements are as follows: · Cursor declaration (DECLARE CURSOR) · OPEN statement · CLOSE statement · FETCH statement · UPDA TE statement · DELETE statement The SQL statement used to declare a cursor is the cursor declaration, a non-executable statement.
declaration specifies the tables in which data is to be manipulated and the method by which d ata is to be fetched. Before data can be manipulated using a c ursor , the cursor must be declared.
· Calculates the total of STOCKQTY in the rows fo r which PRODUCT is REFRIGERA TOR and stores the value of total in variable TOT AL. [Figure: Example of an application program tha t uses a cursor to .
4.2 Declaring a Cursor Before data can be manipulated using a cursor , the curs or must be declared using a cursor declaration, a non-executable statement. The cursor declaration must be c oded before any SQL statements that use the cursor are coded, regardless of the execution se quence of the application pro gram.
[Figure: Deriving a cursor t able] Sequence of rows fetched using a cursor Note that the sequence of rows fetched us ing a cursor was not specifi ed. Alt hough the q uery expression specifi es the rows to be included in t he cursor table, the query e xpression does not spe cify the sequence of fetchi ng the rows.
[Figure: Example of ro ws in a cursor table when the sequence is undefined] Reordering the sequence of rows A cursor declaration can specify the reordering, or sorting, of rows in a cursor table. This is done by specifying the ORDER BY clause after the query expression.
Figure: Deriving a cursor table sho ws the sequence of ro ws in the cursor table if the cur sor declaration is specifi ed as in Example 3. In Figure: Deriving a cursor table, the value of IT MNO is different for each row , so the sequence of rows in the cursor table is unique.
In this example, the cursor declaration use d in Ex ample 2 is used. Here, data is fetched by the cursor using the number of CUSTOMERs as the first sort key . The data is sorted in descending order . The totals of STOCKQTY and ORDERQTY are used as the second sort ke y , and the data is sorted in ascending order .
If one of conditions a) to t) applies to the format of the cursor declaration, the cursor is read-only . If none of the conditions applies, the cursor is updatable. Conditions a) to p) are specified in quer y specifications. Condition q) is sp ecifi ed in query expr essions.
4.3 Opening and Closing a Cursor Before data can be manipulated using a cursor , the cursor must be opened using an OPEN statement. After data has been manipulated, the cursor must be close d using a CLOSE statement. Opening a cursor Use an OPEN statement to begin using a cursor .
Figure: Example of usin g a FETCH statement to position a c ursor shows the results of executing the F ETCH statement in Example 1. The cursor is positioned on the s econd row because the FE TCH statement was executed twi ce.
The cursor declaration speci fying the cursor name mu st be coded before the FE TCH statement in the coding sequence for the applicatio n program. The cursor specified by the FET CH statement must be open. If a cursor that is not open is specified, an error occurs.
d) This example fetches the value of the last row (LAST specified) regardless of the current row . The cursor is positioned to [8] in the following Figure: Exam ple of positioning a cursor in different directions.
[Figure: E xample of up dating dat a using a cursor (colu mn to be up dated is not in the curso r t able)] Data can also be updated using an UPDA T E statement without using a cursor . Fo r more information on this method, see 3.2 "Updating Data in a Data Base".
[Figure: E xample of up dating dat a using a cursor (colu mn to be up dated is not in the curso r t able)] Data can also be updated using an UPDA T E statement without using a cursor . Fo r more information on this method, see 3.2 "Updating Data in a Data Base".
[Figure: Example of deleting dat a using a cursor] Data can also be deleted us ing an UPDA TE statement without using a cursor . For more information on this met hod, see 3.
Chapter 5 Joining Multiple T ables and Manipul ating da t a This chapter describes how to join multiple tables and manipu late data when data is fetched from data bases. For example, suppose that to check on the status of orders for products, data for CUSTOMER, PRODUCT , and ORDERQTY is fetched from the stock inve ntory data base.
[Figure: T able derived when t wo table names are specifie d in a FROM clause] If two table names are specified in a FROM clause, the table derived from the FROM clause contains all columns and rows in the two specified tables. This is called the expanded direct pr oduct of the table.
[Figure: Example of specifying conditions in WHERE clause for joining t ables] In Example 1, because the "COLA = COLZ" condition is s pecified in the WHERE clause, the only rows fetched are those in which the values of co lumns COLA and COLZ are the same.
[Figure: Example in which multiple t ables are joined and dat a manipulated] 60.
Join t ables In Example 2, all rows in the ORDER table and STOCK tabl e are in the table derived from the WHERE clause. Some rows that are not in the ORDER or STOCK table may also be present depending on the c onditions. T o also fetch rows that do not satisfy the conditions, use a join table.
fetched. [Figure: Example in which multiple tables are j oined using a join table] 62.
Example 3 illustrates coding RIGHT for the outer join type. If LEFT is coded for the outer join type in the same example statements, the results are as follows: 5.3 Manipulating Dat a Using Aliases in T ables If column names are qualified b y long table names, codi ng columns may b ecome time-consuming.
manipulated. In this example, the correlation nam es "T1" and "T2" ar e specified for the ORDER ta ble and the STOCK table, respectively . One of these correlation names is used as the column name qualifier . The correlation name is valid onl y in the specified SQL statement.
[Figure: Example of joining a t able to it self and manipulating dat a] 65.
Operations on different rows The following is an example o f performing an operation on v alues in different rows of the same table: Example 2: In this example, the difference in STOCKQTY for products for w hich ITMNO is consecutive i n the STOCK table is obtained.
[Figure: Example of performing an operation on differe nt rows] 5.5 Obt aining the Aggregate for Rows from Multiple T able s The aggregate for rows contained in multi ple tables can be obt ained by joining t wo or more query specif ications with "UNION".
[Figure: Example of specifyi ng UNION in query expression] If UNION is used to join query specifications, a new t able is derived corresponding to the columns in both query specification results.
[Figure: Example of specify ing UNION ALL in query specification] Aggregate sequence due to UNION Query specifications joined b y UNION are processed in se quence from the left. However , parentheses can be used to specify a different evaluati on sequence.
70.
Chapter 6 Methods of Manipulating Dat a This chapter describes methods of using SQL statement s to manipulate data. This chapter contains the following sections: 6.1 Grouping T ables and Ma nipulating Data 6.2 S pecifying V arious S earch Conditions 6.
[Figure: Example of a t able derived from GROUP BY clause] A collection of rows derived from the FROM clause and W HER E clause in the table expression are gr ouped together. In this example, the result of the FR OM clause is eq uivalent to the STOCK table because only the STOCK table is specified in the FROM clause in the table expression.
Fetching dat a from a grouped t able A query specification is used to deriv e a table with which data is to be ma nipulated. If a query specifica tion is used to derive a table from a grouped table, the table is made up of a row for each group.
[Figure: Example of fetching dat a by grouping a table] The reader probably already notice d in Figure: Example of a table derived from GROUP BY clause that the result of specifying only a grouped column i n the select column list is the same as specifyi ng "DISTINCT" in the query specification.
[Figure: Example of using a set function incor rectly] In Example 3, the row count f or the result is one row for th e total of STOCKQTY , but n rows for PRODUCT .
[Figure: Example of grouping t ables by using p art s of character string s] Case-splitting dat a and grouping t ables S pecify a CASE expression in the GROUP BY clause to split column data into cases instead of colum ns, and to group tables for each value split into cases.
[Figure: Case-splitting dat a and grouping t ables] Grouping t ables by month T o group tables of a DA TE-type column b y year or mont h, specify the date-time val ue function. A specification example is shown below . For details on the date-time value function, see "6.
[Figure: Example of grouping t ables by month] 6.1.2 Specify ing group s to be processed from a grouped t able The WHERE clause can be used in a table expressi on to specif y ro ws to be processed in the table specifie d in the FROM clause.
Figure: Example of a table derived from a HA VING clause sho ws the table deriv ed from the HA VING clause and the query specification resu lt. 79.
[Figure: Example of a t able derived from a HA VING clause] HA VING clause S pecify the search conditions for the groups to be process ed in the HA VING clause. The columns used as conditions must either be grouped columns or must be able to be specif ie d by a set function.
specifying search conditi ons in the WHERE clause improv es the pr ocessing efficienc y of SQL statements. For example, to derive rows in which PRODUCT is TELEVISI ON, specif y PRODUCT = 'TELEVISION' as the condition in the WHERE clause of the query specif ication in the prev ious example.
82.
A common error with this method of manipulating data is that the user may mistakenly beli eve that obtaining the totals of STOCKQTY and ORDERQTY for each ITMNO will provide t he same result as in Example 1.
[Figure: Example of a common error w hen grouping multiple joined t ables] The MAX function or MIN function can be used to correct the previo us error , as in the following example: Example 3: In this example, the error in Example 2 is corrected.
6.2 S pecifying V arious Search Conditions Search conditions can be specified in SQL st atements to specify the manipulati on of rows that satisfy the search condition.
STOCK table for w hich ITMNO is 100 or greater or WHCODE is 1 are fetched. Example 3: In this example, the logical operator "NOT" is spec ified in the search condition. The ro ws from the STOCK table for all items ex cept TELEVISION are fetched.
[T able: T ruth table for NOT ] Evaluation sequence of logical operators Multiple logical oper ators can be specified in a se arch condi tion. In this case, logica l operators are evaluat ed in the following sequence: 1. NOT 2. AND 3. OR However , parentheses can be used to in the specification of the evaluation sequence.
[T able: Comp arison operators] The data types of the left and right value expressions of a comparison operator must be comparable. T able: Comparable data types lists comparable data types. For more information on data types, see Appen dix A "SQL Data T ypes and Equivalent Host V ariable Data T ypes".
[T able: Comp arable dat a ty pes] The following is an example o f specifying a comparison predicate: Example 1: 89.
In this example, rows for which ITMNO is 123 are fetched fr om the STOCK table. Example 2: In this example, rows for which STOCKQTY is less than 50 are fetched from the STOCK t able.
WHERE PRODUCT = 'TELEVISION' AND STOCKQTY >= 90 Example 8: In this example, rows for which STOCKQTY is 10 or greater and PRODUCT is VIDEO CASSETTE PLA YER, and rows for which STOCKQTY is 200 or greater and PRODUCT is TELEVISION are fetched from the STOCK table.
[Figure: Example of specifying a subquery in a comparison predicate to retrieve dat a] Example 1 1: In this example, the maximu m va lue for ITMNO is fetched from the STOCK table, and the PRODNO rows for that value are dele ted from the ORDER table. DELETE FROM STOCKS.
[Figure: Example of specifying a subquery in a comparison predicate to delete dat a] Checking whether a value is a null value Use the NULL predicate to check whether a column value is a null value.
6.2.2 Checking whether a val ue is in a certain range Use the BETWEEN predicate to check whether a value is in a certain range. The follo wing are examples of specifying the BETWEEN predicate: Example 1: In this example, ro ws for which STOCKQTY is greater than or equal to 50 but less than or equal to 100 are fetched from the STOCK table.
A comp arison predicate can also be used to check whether a value is in a certain rang e. In the following example, the specified comparison predicate has the same effe ct as the BETWEEN predicate in .
[Figure: Example of specifyi ng IN predicate] The values to be compared in the val ue expression are en closed in parentheses and spe cified in a quantified value l ist. Use host variables or literals t o specify values in a quantified value list. Col umn names cannot be specifi ed.
[Figure: Example of using a subquery in the IN predica te] Comp aring a set of values Use a quantified predicate as a searc h condition to sp ecif y comparison with a set of quantified values. Use a comparison operator and quantifier (ALL, SOME, or ANY) in specifying the quantified predicate.
[Figure: Example of specifyi ng a quantified predicate] 98.
Checking whether rows that satis f y cert ain conditions exist The EXISTS predicat e can be specified in a subquery . W hether rows are specified de pends on whether or not the result of the subquery is null. In other words, the EXISTS predicat e is used when the row count for the table derived from the result of the subquer y can be 0.
[Figure: Example of specifying the EXISTS predicate] 6.2.4 Checking whethe r values match a character-string pattern Use the LIKE predicate to check whether data in a char acter-string t ype or national-language charac ter-string type matches a certain character-string pattern.
[Figure: Using the LIKE predicate to specify rows] Use a character string, arbitrary string sp ecifier , or arbitrary character spec ifier to represent the pattern. T able: Arbitrary string specifier and arbitrary character specifier explains the arbitr ary string specifier and the arbitrar y character specifier .
Rows on which the value in column COL 1 in table TBL5 is "ABC" are specified. SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE 'A BC' Example 2: In this example, rows for which the value in colum n COL1 in table TBL5 begins wit h "ABC" are specified.
[Figure: Example of LIKE predicate specifi c ation (using an arbitrary string specifier)] Examples of using arbitr ary string specifier "_" Examples of using arbitrar y string specifier "_" are sh own belo w .
Rows of four characters for which the val ue in column COL1 i n TBL5 begins with any character followed by "A" and ends with "BC". SELECT .
Example 8: In this example, rows for which the value in column COL2 in table TBL5 begins with any two characters and ends with "_ _ABC_ _" are specified. [Figure: Example of specify ing a LIKE predicate specification using escape ch aracters] 6.
For products with a high variation of shipment qua ntity (assuming that the difference bet ween the preceding and current shipment quantities is 10 or greater), t he correspondi ng product numbers are obtained from the SHIPTBL table. Example 2: The maximum and minimum temperat ures in each re gion are rounded do wn to the next integers.
Example 3: The maximum temperature in each region is rounded off to two decimal places. 6.4 Manipulating Character S tring Dat a When manipuling table data, users can manipulate a nd join char acter stri ngs. The table below lists the processing for manipulating parts of character strings.
[T able: Processing for manipulating p art s of charac ter strings] Example 1: Company names and addresses are fetched from t he company table. For each com pany name, a null character at the end is removed, "Co., Ltd.," is c oncatenated, and then only t he metropolis and district names are fetched from the address.
[Figure: Example of combining character value functions to fetc h data ] Example 2: Codes and office names are fetched from the offi ce list table, and character string "OFF ICE" is removed from each office name.
[Figure: Example of combining a character subs tring function with a numeric function to fetc h dat a] Example 3: The last three characters are fetched from t he product nam e character string in 2NDTBL.
Example 5: Character string "VIDEO" in COL2 of tabl e 2NDTBL is replaced with "VIDEOT APE". 6.5 Manipulating Date Dat a Users can manipulate table data to calculate dates and times, convert date-time value expression data to characters, and convert character-type da ta to DA TE-type data.
[T able: Processing for manipulating date dat a ] Example 1: The order quantity for each q uarter is obtained from the order table. By using the TRUNC_DA TE function, the date can be truncated for each year (January 1 on the year), month (1st day of the month), or quarter .
Example 2: The number of days that have been el apsed from the order date to t he current date is obtained from the ORDERTBL t able. The cu rrent date is 2000-08-10. By using the SP AN_DA TE function, the difference (time interval) bet ween dates can be obtained, where YEAR, MONTH, or DA Y is used as the unit fo r the time interval.
Example 3: For orders that are one m onth old or old er in t he ORDERTBL table, the corresponding order n umbers are obtained from the order table according to the specified order da y .
The last date of the m onth can be obtained using the LAS T_DA Y function. Example 5: The maximum temperatur e date data in the W EA THERM NTBL table is converted to a character string in the "YYYY .MM.DD DY" format. The date type can be converted to the character type using the CNV_CHAR function.
Example 6: The minimum temperat ure date in the WEA THERMNTBL table is converted to character-type data in the date format. The date format "DA Y MONTH DD, YYYY" is assumed to be specified in CHAR-type host variable :DA YFMT . Example 7: The date characters in MINTEMPDA YCHA RTBL are conver ted to a date.
6.6 Converting the Dat a T ype to Manipulate Dat a The data types of columns ca n be converted to e xecute date or time oper ations using character strings and to add or update data in time type or time interval type columns.
[Figure: Converting dat a from character string type to time interval type (y ear and month)] An example of a time operation e xecuted by converting the c haracter stri ng data type to the time interval type (date and time) is given belo w .
[Figure: Converting dat a from numeric ty pe to character string type] 6.7 Using CASE Expression to Manipulate Dat a The CASE expression is used to c hange the value of a re sults column depending on .
Example 3: The first setting items are obtaine d from the user table in the s equence of e-mail address, phone, and address. Example 4: The name of users and points of t he users who reach the ne xt rank by 500 points are obtained from the POINTTBL table.
Example 5: The services used are grou ped, and t he total number of services use d for each group is obtained, from history table HISTORYTBL. Example 6: The service symbols listed in histor y table HISTOR YTBL are changed.
6.8 S pecifying Row IDs to Manipulate Dat a A row ID (ROW_ID) recognizes a unique row of a table in the database. Use a single row SELECT statement or FETCH statement to fetch a row ID. The host variab le for receiving the row ID must corr espond to the ROW_ID.
123.
6.10 Omitting Schema Names In the examples of SQL st atements provided so far , tabl e names have been qualifie d with a schema name, such as in STOCKS.STOCK. This section explains how to use a tabl e declaration i n which the schema name qualifier is not specified.
6.1 1 Changing the User of the Current Session When an application pr ogram accesses a database, the user of the current session becomes the person connected to the database. This user can be ch anged in the application program. Example: In this example, the user of the current session is changed to USER2.
6.13 Manipulating Dat a Using Sequence CURRV AL and NEXTV AL can be used to fetch the creat ed sequ ence number . CURRV AL fetches the current sequence number in the application pr ogram. NEXTV AL fetches the next val ue af ter the last sequence number fetched in the RDB system.
This example sho ws the method for specifying a se que nce in the SQL st atem ent of an application program. In the example, employee numbers (E MPLOYEENO) are increment ed and as signed to ne w employees. Example 2: This example sho ws the method for automatically inserting a seque nce number in an insertion line using a sequence.
Example 3: This example sho ws the method for referencing t he sequence numb ers for stock control (STOCKCTL) and sequence 1 (SEQ-1) by specif ying CURRV AL and NEXTV AL.
129.
130.
Chapter 7 Executing Dynamic SQL Statements When Application Programs are Executed This chapter describes the functi ons and uses of dynamic SQL. This chapter consists of the following sections: 7.1 Overview of Dynamic SQL 7.2 Dynamically Modif ying an d Executing SQL Statements 7.
SQL descriptor area An SQL statement may be dynamically modified to mani pulate data. In other words, the SQL statement may be entered from outside the application program. In this case , however , the type of SQL st atement to be ent ered cannot be determined when the appli cation program is created.
The SET USER P ASSWORD statement (user control statement) and SET ROLE st atement (access control statement) cannot be executed as preparable statements. The syntax of the multiple row SEL ECT statement is the same as that of the cursor specif ication.
SET STOCKQTY = 0, WHCODE = ? WHERE ITMNO = 111 Example 6: In this example, the dynamic param eter specification is specifie d in the insert value list of an INSERT statement.
figure below . The SQLDA structure provides informatio n about the target specification and dynami c parameter specifi cations. The SQLDA structure consists of SQL V AR (item descrip tor area), SQLN (maximum n umber of SQL V AR elements), and SQLD (number of valid elements).
SQLN SQLN indicates the maximum number of the d ynamic parameter specifications that ca n be written or the maximum number of the select column list item s that can be written. SQLN theref ore determine s the maximum number of elements of array SQL V AR.
If the TYPE value is 9, indicating the datetime type, any of the following DA TE_TIME_INTERV AL_CODE values is set in SQLSCALE: If the TYPE value is 10, indicating the interval t ype, any of the follo.
Procedure for fetching execution results The order in which execution results are fetched using t he USING descriptor is shown in "Figure: Procedure for fetching execution res ults for a prepared st atement." The flow of fetching e xecution results is explained h ere.
[Figure: Procedure for fetching execution results for a pre pared statement] When modifying an SQL statement d ynamically to manipu late data, prepare exec ut ion of the SQL statement. Preparing the SQL statement Use the PREP ARE statement to prepare a dynamic SQL statement for execution.
references the cursor correspondin g to the SQL statement identifier is also deallocat ed. However , if the prepared statement is a dynamic SELECT statem ent, the cursor corresponding to the SQL sta tement identifier must hav e already been closed. An example of specif ying the PREP ARE statement is show n below .
7.2.1.1 Preparing and execu ting dynamic SELEC T statements (for SQLDA stru cture) T o fetch data continuously , prepare and exe cute a dynamic SELECT statement.
[Figure: Example of application program tha t executes a dynamic SELECT statem ent] 142.
143.
144.
7.2.1.2 Preparing and execu ting dynamic single row SE LECT statements (for SQLDA structure) T o fetch data from one row , prepare and execute a dyna mic single row SELECT statement. T o execute a dynamic single row SELECT statement, use an EXECUT E statement in which the USING clause is specified.
statements that can use t he SQL structure, s ee the examples in "Figur e: Procedure for fetchin g execution results for a prepared statement." Example: This is an example of a dynam ic single ro w SELECT statement entered from a terminal. 7.
The SQL descriptor area eithe r consists of only one identifier (COUNT) or consists of one identifier (COU NT) and one or more item descriptor areas (eleme nt s of the SQL descriptor area). COUNT i ndicates the number of select col umn lists in the SQL descriptor area; the dat a t ype is an exact numeric with binary precision.
If the TYPE value is 1 or 12, the CHARACTER_SET_NAME val ue (char acter set name) varies according to character string type and national char acter string type.
Procedure for fetching execution results The order in which execution results are fetched using t he USING descriptor is shown in "Figure: Procedure for fetching execution res ults for a prepared st atement." The flow of fetching e xecution results is explained h ere.
[Figure: Procedure for fetching execution results for a pre pared statement] When modifying an SQL statement d ynamically to manipu late data, prepare exec ut ion of the SQL statement. Preparing the SQL statement Use the PREP ARE statement to prepare a dynamic SQL statement for execution.
references the cursor correspondin g to the SQL statement identifier is also deallocat ed. However , if the prepared statement is a dynamic SELECT statem ent, the cursor corresponding to the SQL sta tement identifier must hav e already been closed. An example of specif ying the PREP ARE statement is show n below .
specified in a dynamic OPEN statement, the cursor must have already bee n closed. An example of deallocating the SQL descript or area with descriptor name DESC1 is sho wn below .
GET DESCRIPTOR statement The GET DESCRIPTOR statement fetches the information set in the SQL descript or area to a host variable. The dat a type of the host variable must match data t ype of the corresponding fetch identifier . An example of fetching the number of select column lists to host variable varcou nt is shown below .
7.2.2.1 Preparing and execu ting dy namic SELECT statem ents (for SQL descri ptor area) T o fetch data continuously , prepare and e xecute a dynamic SEL ECT statement.
[Figure: Example of application program tha t executes a dynamic SELECT statem ent] 155.
156.
157.
7.2.2.2 Preparing and executing a d yna mic single row SELECT statement (for SQL descriptor area) T o fetch data from one row , prepare and execute a dyna mic single row SELECT statement. T o execute a dynamic single row SELECT statement, use an EXECUT E statement in which the USING clause is specified.
7.3 Dynamically Modifying a nd Exe cuting SQL Statement Conditions This section describes the fo llowing methods that can be used to dynamically modify and execute SQL statement conditions.
The SQLDA structure holds dynamic pa ramet er specification information. The SQLDA structure consists of it em descriptor area SQL V AR, the maxi mum number of SQL V AR elements SQLN, and the number of effe ctive elements SQLD. Item descriptor area SQL V AR consists of arrays in which dy namic parameter specification i nformation is stored.
[Figure: Procedure f or sett ing dynamic parameter specificat ion values for pre pared statement s] SQL statements used to ma nipulate the SQLDA structure As shown in "Figure: Proced ure for sett.
When the prepared statem ent correspondin g to SQL statement identifier ST M1 is as follows, the SQLDA structure variable will have the following contents: This section describes only the SQL statements used to ma nipulate a SQLDA structure c ontainin g dynamic parameter specification information.
[Figure: Example of an a pplication p rogram that us es the SQLDA str ucture (w ith dynamic p a rameter specification)] 163.
164.
165.
166.
167.
7.3.1.2 Preparing and execut ing dynami c singl e-row SELECT statement s (for SQLDA structure) T o fetch single-row data, prepar e and execute a dynami c single-ro w SELECT statement.
single-row SELECT statement, use the EXE CUTE statement with the USING clause specified. An example of using the SQLDA structure to specif y a dynamic single- row SELECT statement with the dynamic parameter specification is sho wn below .
information must match the host variable attributes. Matching is accomplished by fetching dynamic parameter specification information from the database to the SQL descr iptor area, modifying the a ttributes, and then specifying the value. The following figure sho ws t he position of the SQL descriptor area.
[Figure: Procedure f or sett ing dynamic parameter specificat ion values for pre pared statement s] SQL statements used to manipulate the SQL descriptor area As shown in "Figure: Proced ure for s.
When the prepared statement correspo ndi ng to SQL statement identifi er STM1 is as follows, SQL descriptor area DESC1 will have the following contents: GET DESCRIPTOR statement The GET DESCRIPTOR statement fetches information set in the SQL descriptor area to the host variable.
SET DESCRIPTOR statement The SET DESCRIPTOR statement sets t he data type and value of the dynamic par ameter specification in the SQL descriptor area. Specify them using the cons tant or host vari able. The data t ype of the ho st variable must match the data type of each set identifi er .
[Figure: Example of an a pplication program t hat uses the SQL descript or area (w ith dynamic parameter specification)] 174.
175.
176.
177.
178.
179.
180.
181.
7.3.2.2 Preparing and execut ing dynami c singl e-row SELECT statement s (for SQL descriptor area) T o fetch single-row data, prepar e and execute a dynami c single-ro w SELECT statement. T o execute a dynamic single-row SELECT statement, use the E X ECUTE statement with the USING claus e specified.
is shown below . For information about SQL statements used to manipulate the SQL descriptor area, see "Figure: Procedure for setting dynamic parameter spec ification val ues for prepared statements." Example: This is an example of a dynamic single-r ow SE LECT statement entered from a terminal.
Example: This is an example of an UP DA TE statement (searched) entered from a termina l. Data for the values of the dynamic parameter specifications entered from the terminal is upd ated as a set clause.
Using the EXECUTE statement to fetch execution results Specify USING arguments in the E XECUTE statement to fetch execution results. Example 2: In this example, the values of PRODUCT and STOCKQTY for the product with ITMNO "1 10" are fetched from the STOCK table.
Using the dynamic FETCH statem ent to fetch execution results Specify USING arguments in the d ynamic FET CH statement to fetch execution res ults. Example 4: In this example, values of PRODUCT and STOCKQ TY with IT MNO greater than "200" are fetched from the STOCK table.
Figure: Example of an appl ication prog ram t hat uses the USING argument is an e xample of an applicat ion program that uses the USING argument. Example 6: This is an example of a dynamic SELECT statement that has two dynamic par ameter specifications.
[Figure: Example of an application program tha t uses the USING argument] 188.
7.4 Immediately Executing SQL Statements If an SQL statement is to be execut ed without being prepared, use the EXECUTE IMMEDIA TE statement. T he SQL statements that can be executed us ing an EXECUT E IMMEDI A T E statement are prepara b le statements other tha n the dynamic SELECT statement and the single row SELECT statement.
"Overview of Dynamic SQL". When t he preparable statement is the INSERT statement, UPDA TE statement (searched), DELETE statement (searched) , UPDA T E statement (positioned), or DELETE statement (positioned), th e dynamic parameter specificat ion cannot be specifie d.
Example: In this example, the schema name is changed to "SCH1". 191.
192.
Appendix A SQL Data types and equivalent H ost V ariable Data T y pes T able: SQL data types and equivalent C language host vari able data t ypes lists SQL data types and th e equivalent C language host variable data t ypes.
[T able: SQL data types and equiv alent C language host variable data types] 194.
[T able: Time interval types and equiv alent data types for C language host v ariables] T able: SQL data ty pes and equivalent data t ypes for COBOL host variables lists the data types and the equivalent data types for COBOL host variables.
[T able: SQL data types and equiv alent data types for COBOL host variables ] T able: T ime interval types and equivalent data types fo r COBOL host variables sho ws the time interval types and equivalent data types for COBOL host variab les.
[T able: Time interval types and equiv alent data types for COBOL host v ariables] 197.
198.
Appendix B Handling RDB Messages User handling of some messages can be r eferenced onli ne. These messages are issu ed when RDB commands are being executed or an applic ation program is being com p iled. T hese messages also include messages set in a message variable (SQLMSG) of an application program.
When executing an application program Example 4: Display a description of mess age JYP2031E set in the message variable (SQLMSG) when executing an application program.
Appendix C SQLST A TE V alues The system reports the execution resu lts of SQL statements in status variable SQLST A TE while an application program is running. The app lication pr ogram checks SQLST A TE whenever an SQL statement is to be executed, and processes SQL information while checking t he resu lt of SymfoW ARE/RDB processing.
202.
203.
204.
205.
206.
Glossary ALL set function The ALL set function is a set function for which ALL is sp ecified in the argument. Ro ws containing the same value are targeted by the ALL set function. Related term: DISTINCT set function Application program In general, programs used in the work of a computer user are called applicati on programs .
Column A constituent element of a table. A re lational database represents data using two-dimensiona l tables consisting of rows and columns. Column name (item name) A column name is the name assigned to a column, defined in the schema definition.
Database name Many databases can be cre ated as un its of administrati on and design on on e server system. T o identify each database uniquely , each is assigned a unique name (datab ase name) on the server system. DEF AUL T clause An element of the definition of a colu mn in a table.
Escape character An escape character is an alternate character for unde rscore (_) of an arbitrary ch aracter specifier or for p ercent symbol (%)of an arbitrary string s pecifier . An escape charac ter is sp ecified in a LIKE predicat e. A LIKE predicate is used to specify a retrieval condition for data manipul ation.
IN predicate In the search condition specification for a n SQL statement, the IN predicate specifies that rows are mani pulated depending on comparis ons with a set of values.
Non-cursor SQL statement An SQL statement used for data manipula tion, the non-cursor SQL statement does not use a cursor to specify rows to be manipulated. Instead, the ro ws to be processed are s pecified in t he search cond ition specifi ed in the SQL statement.
types of predicate are avail able: co mparison, BETWEEN, IN, LIKE, NULL, quantif ied, an d EXISTS. Search conditions are specified using a predic ate and Boolean oper ators. Procedure routine A procedure routine defines processin g procedures for a da tabase using SQL.
Relation operation With relational databases, a relation oper ation allows only data from specified columns or a collection of data that meets certain conditions to be fetched. T hree types of relation operations are available: selectio n, projection, and joi n.
Search condition The specification for identif ying rows that are the subject of t he operatio n in data manipulat ion SQL. F or example, a search condition is specified in the WHERE clause of an SQL SELECT statement.
Single row SELECT statement The single row SELECT statement is an SQL data manipulati on statement used to refe rence data. The singl e row SELECT statement can specify a search condit ion and fetch one row of data from a table.
an SQL statement is executed, the st atus code for the execution result is stored in the status varia ble. Storage structure A database structure along with logical structure and physical stru cture. Storage structure physic ally locates data logically expre ssed as rows and columns of tables in a databas e as storage data.
T arget specification A target specification is used t o fetch values stored in a databas e to an application program . The target specification is specified as a variable. T rigger definition A trigger definition defines t he data manipulation (inserti on) of a table linked to the data manipulation (inserti on, deletion, update) of another table.
An important point after buying a device Fujitsu J2X0-1634-01EN (or even before the purchase) is to read its user manual. We should do this for several simple reasons:
If you have not bought Fujitsu J2X0-1634-01EN yet, this is a good time to familiarize yourself with the basic data on the product. First of all view first pages of the manual, you can find above. You should find there the most important technical data Fujitsu J2X0-1634-01EN - thus you can check whether the hardware meets your expectations. When delving into next pages of the user manual, Fujitsu J2X0-1634-01EN you will learn all the available features of the product, as well as information on its operation. The information that you get Fujitsu J2X0-1634-01EN will certainly help you make a decision on the purchase.
If you already are a holder of Fujitsu J2X0-1634-01EN, but have not read the manual yet, you should do it for the reasons described above. You will learn then if you properly used the available features, and whether you have not made any mistakes, which can shorten the lifetime Fujitsu J2X0-1634-01EN.
However, one of the most important roles played by the user manual is to help in solving problems with Fujitsu J2X0-1634-01EN. Almost always you will find there Troubleshooting, which are the most frequently occurring failures and malfunctions of the device Fujitsu J2X0-1634-01EN along with tips on how to solve them. Even if you fail to solve the problem, the manual will show you a further procedure – contact to the customer service center or the nearest service center
