Fujitsu J2X0-1634-01EN manual

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. SymfoW ARE is a database system supporting a client -serve r application co nfigurati on. In t[...]

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. These manual s provide r[...]

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. The copyright of the online manual is the pr operty of UNIX S[...]

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 . SymfoW ARE Programmer's Kit Related manuals The related manuals are as fol lows: · Reference Manuals Collection of AnswerBook 2 ?[...]

· 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 · Microsoft(R) Windows(R) 98 Second Edition · Microsoft(R) Windows(R) Millennium Edition · Microsoft(R) Windows XP Professional · Microsoft(R) Window[...]

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. vii[...]

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. SymfoW ARE is a registered tr ademark of Fujitsu Limited. Other company and [...]

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.1 T ypes of SQL 1.2 Common SQL S tatement Rules 1.1 T ypes of SQL SymfoW ARE/RDB uses the international standard SQL for data manipulation, whic h is linked to languages such as C [...]

[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. 2[...]

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. Routine names e. Parameter names f. T rigger names g. Host identifiers names h. SQL variabl e [...]

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. ORDER t able Figure: Inventory management data base b) sho ws the cont [...]

[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. 5[...]

[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. The STOCK t able and the ORDER table ar e joined through IT MNO and PRODNO. Th[...]

[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.1 Fetching Column V alues without Modifications Use the single row SELECT stat[...]

[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. In the follo wing example an asterisk is used to specify columns: Example 2: In this example, an asterisk [...]

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. Use an embedded SQL declaration clause to d eclare the i[...]

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. 2.1.3 Fetching dat a from multiple t ables A single row SELECT st atement can be used to join t wo or more table[...]

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 . The following is an example o[...]

[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.2.1 P[...]

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: Method of specify ing and meaning of operational expressions] S pecif[...]

[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. The single row SELECT statement can be used to fetch th e results of tota ling da[...]

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" to obtain the average value, "M AX" to obtain the ma ximum val ue, "MIN" to obtain the mini mum value, and "COUNT " to ob[...]

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: Obtains average column value MAX function: Obtains maximum column value MIN function: Obtains minimum column value SUM function: Obtains total column valu[...]

[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. Dat a type of result s of set functions T able: Dat a type of columns targeted by set fu[...]

[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.3 Deleting Data from a Data Base 3.1 Adding dat a to a Dat a Base Use the [...]

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. If an indi cator variable is used, only one I NSERT statement needs to be coded. However , for processing in which the data must be specified as a null va lue, specify the keyword "N[...]

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. The default values can be used by the IN SE RT statement to add data. If no value is specified for a column for which no default value is defined, a null value is adde d to the colu[...]

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. The following is an example in which DEF AUL T V ALUES is specified: Example 3: In this example, a row of data is[...]

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.1.4 Adding dat a fro[...]

[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 . For example, the data from some c[...]

[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. An example of this follo ws. For detailed informatio[...]

[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. This section explains the following o perations tha[...]

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. 3.2.1 Up dating values in multiple columns In the first example [...]

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. A value need not be s[...]

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. T o specify the current date, specify CU[...]

[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. When products are [...]

[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. However , PREVSHIPMT is updated to "100", [...]

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. However , V ARIA [...]

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". The method of deleting rows explaine d in this section is called the "DE[...]

[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.5 Updating Data Using a Cursor 4.6 Deleting Data Using a Cursor 4.1 Curs[...]

[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. The cursor 41[...]

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. T he cursor declaration must be coded before any SQL statements that use the cursor are coded, regardless of the execution sequenc e of the application prog[...]

· 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 manipulate dat a] 43[...]

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. The following is an example of a[...]

[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. This is true even for a simple curs[...]

[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. The followi ng is an example of specifying the ORDER BY clause: Example 3: In t[...]

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. The sit uation when the valu e of ITMNO is the same for more than one ro[...]

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 . [Figure: Example of specifyi ng col[...]

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. Conditions r) to t) are specified in cursor declarations. Conditions r) an[...]

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 . The cursor table specified in the cursor declaration is created a s [...]

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. [Figure: Example of using a FETCH st atement to position a cursor] 51[...]

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. If the FETCH statem ent is executed when the cursor is positi oned in the last row ,[...]

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. Example: FETCH LAST FROM CSR1 INTO :H1, :H2 e) This example fetches the value of t he nth row from the first or last row regardless of t[...]

[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". Example 2: In this example, the cursor in Figure: Example of using a FETCH st[...]

[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". This method of upd ating data using an UP DA TE statement is called UPDA TE s[...]

[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.3 "Deleting Data from a Data Base". This method of deleting data using a DE LETE statement is called DELETE statement (positioned) beca use the row to be deleted is sp[...]

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. This data is to b[...]

[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. In t he same way , if three or more tables names are specified i[...]

[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. When multiple tables are spec ified in a FROM clause, co lumns with the same nam e m[...]

[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. A join t able can be used to also fetch rows which do n[...]

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. Instead, data can be manipulated by specifyi [...]

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. If a correlation name is spec ified, the correlatio n[...]

[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. The data to be fetched is ITMNO for both products, PRODUCT for the one with the high[...]

[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". S pecify the tables to be pr ocessed and the conditions for fetching data in the quer[...]

[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. The column sequ ence is from the left. Each query spec ification must be specified as follo ws: · The number of columns in the results table must be the [...]

[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. A query expr ession enclosed in pa rentheses is process ed before other quer y expressions. T[...]

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.3 Manipulating Numeric Data 6.4 Manipulating Character String Data 6.5 Manipulating Date Data 6.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. Also, b[...]

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. In a query specificati on for which a GROUP BY clause is specified, a grouped column, a grouped f unction[...]

[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. In Example 2, onl y PRODUCT was specified i[...]

[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 . Therefore, this query specification r esults in an error . Grouping t ables by using p art s of char acter strings Methods of grouping table data in c olumns have been e xplained so f ar[...]

[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. An example of specification is shown bel ow . For deta ils on the CASE expr[...]

[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.5 Manipulating Date Data." Example 6 The sales dates in the RET AILSAL[...]

[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. T o do this, specifying the groups to be proc essed in a table grouped by the GROUP BY clause, using a HA VIN[...]

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. If a column other than a grouped column is specified by other than a set f[...]

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. 6.1.3 Grouping multiple t ables that are joined a[...]

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. T he following is an example of using this method incorrectly: Example 2: In this example, the data manipulation used i n Example 1 is incorrec[...]

[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. Because the value of STOCKQTY is the same in each gr oup, both the maximum and minimum v alues for each[...]

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. This section explains ho w to specify search cond itions that use a single pred icat e and predicates combine d by logical operators ("AND", " OR", or "NOT&[...]

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 t able for AND lists the resu lt of specifying AND bet ween predicates . T able[...]

[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. A condition enclosed in pa[...]

[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". 88[...]

[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. SELECT ... FROM STOCKS. STOCK WHERE STOCKQTY < 50 Example 3: In this example, rows for which "PRICE x ORDERQTY" is 1,000,000 or less are fetched from the [...]

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. SELECT ... FROM STOCKS. STOCK WHERE STOCKQTY >= 10 AND PRODUCT = 'V[...]

[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.ORDER WHERE PRODNO = (SELECT MAX (ITMNO) FROM STOCKS. STOCK) 92[...]

[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. The follo wing are examples of specifying the NULL predicate: Example 12: In this example, rows for which WHCODE is a null value are fetched from the ST[...]

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. The resul[...]

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 Example 1: Example 4: 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 STOC[...]

[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. The result of the IN predicate is true if the value for the value [...]

[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. The format of the subquer y specification in parentheses[...]

[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. If the resu[...]

[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. The result of the LIKE predicate is true if the s pecified p attern is [...]

[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 . When the pattern is a character st[...]

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. SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE 'A BC%' Example 3: Rows on[...]

[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 . The figure with the example of "Figure: LIKE predicate specification (usi ng an arbitrary stri ng specifier)" [...]

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 ... FROM SCM.TBL5 WHERE COL1 LIKE 'A _BC' [Figure: Example of LIKE predicate specifica tion (using an arbitrary string specifier) ] Escape characters In a pattern specification in th[...]

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.3 Manipulating Numeric Dat a Users can manipulate numeric data to obtain absolute valu es, mini[...]

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. 106[...]

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. 107[...]

[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. Users can fetch a part[...]

[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. 109[...]

[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. [Figure: Example of combining a character substring function with a numeric function to up d ate dat a] Example 4: An asterisk "*" is em[...]

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. The table below list s the processing fo[...]

[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 . 1 12[...]

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. Users can also [...]

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 cu rrent date is 2000-08-01. By using the ADD_DA T E function, dates can be added, where YEAR, MONTH, or DA Y is used as the unit of addition for the time inte[...]

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. The conversion is performed in the date f[...]

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. The character-t ype data defined [...]

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. An example of a dat e operation execute d by converting the c haracter string data type to the time interval t ype (year and[...]

[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 . Example 2: In this example, the process time is updated to a valu e reduced by 1 hour and 30 min utes for da[...]

[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 t he column value, arrange fetched columns in a sequenc e based on priority level, and sp lit data to cases to specify an ap propriate value to eac h [...]

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. The number of points of each rank ar e as follows: SIL VER is 3,000 points; GOLD is 10,000 poi[...]

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. 121[...]

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. For the variable definition correspondi ng to each data type, see T able "SQL dat a[...]

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. S pecify the table name used b y the applic ati on program and the name of the sche[...]

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.12 Adding Name to Results Column in Quer[...]

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. CURRV AL and NEXT V AL can be specified in the follo wing pa rts of SQ[...]

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. In the e xample,[...]

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. 128[...]

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.3 Dynamically Modif ying and Exec uting SQL Statem ent Cond itions 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. For ex am ple, a variable used to fetch data fr[...]

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. T o execute these statements, the st atements must be prepared for exec ution. These stat ements [...]

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. INSERT INTO STOCKS. STOCK (ITMNO, PRODUCT, STOCKQTY) VALUES(?, ?, ?) USING clause Use the USING clause to set values in the dynamic paramet er specifica tion of a prepared sta[...]

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). The format of the SQLDA structure is as follows. The configuration element[...]

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. SQLD SQLD determines the number of valid elements of arra y SQL V AR. Executing a output DESCRI[...]

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 following DA T ETIME_INTERV AL_CODE values is stored in SQLSCALE: 137[...]

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. Each SQL statement is explained after "F igure: Procedur e for fet[...]

[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. T o create and execute an SQL statement when the program is [...]

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 . The SQL statement to be prepared f[...]

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. An example of an appl ication program that executes a dynamic SELECT statement using th e SQLDA structure is shown in "Figure: Exam ple of application progra m that executes a d ynami[...]

[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. An exampl e of a dynamic single row SELECT stateme[...]

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.2.2 Preparing SQL statements and manipulating the SQL descriptor area When data is fetched from a [...]

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. In the follo wing fig[...]

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. · Character string type: BASIC · National character string type: NCHAR If the TYPE value is 9, indicating the dat etime type, the DA TETIME_I NTERV AL_CODE value is a ny of the codes list[...]

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. Each SQL statement is explained after "F igure: Procedur e for fet[...]

[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. T o create and execute an SQL statement when the program is [...]

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 . The SQL statement to be prepared f[...]

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 . DESCRIBE statement The DESCRIBE statement fetches select column list inf ormation for the pr epared statement prepared by the PREP ARE statement to the SQL descripto r are[...]

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 . The following is an e[...]

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. An example of an application pr ogram that executes a dynamic SE LECT statement using the SQL descriptor area is sho wn in "Figure: Example of application program that executes [...]

[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. An exampl e of a dynamic single row SELECT st[...]

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. · Manipulating the SQLDA structure - Preparing and executing d ynamic SELEC T statements (for SQLDA structure) - Preparing and executing d ynamic single-row SE[...]

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. SQLD in[...]

[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 setting dynamic parame ter specific ation values for prepare d statements," use SQL statements to fetch dynamic parameter specification informa tion [...]

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. For informa tion about other SQL statements, see &q[...]

[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. T o execute a dynamic 168[...]

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 . For informati on about the SQL statements used to manipul ate the SQL structure, see "Figure: Procedure fo[...]

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. The SQL descriptor area eithe r consists [...]

[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 setting dynamic parame ter specific ation values for prepare d statements," use SQL statements to fetch dynamic parameter speci fication informa t[...]

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. The data type of the host variable must match the dat a type of the co[...]

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 . When the descriptor name specified i n the SET DESCRIPT OR[...]

[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. An exa mple of using the SQL descriptor area to s[...]

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. Data i s searched using the value of the dy [...]

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. 7.3.4 Executing prepared statements for which variable attributes are know n When an application pro gram is created, the attributes and the numb[...]

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. The data types for PRODUCT and STOCKQTY and those for the two arguments in w[...]

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. The data types of ITMNO and STOCKQTY and those of the two arguments[...]

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. V alues for IT MNO and STOCKQTY entered from a terminal are us ed as search conditions to fetch [...]

[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. For deta ils on pre[...]

"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. If an SQL statement variable c ontains an U PDA T E statement (positio[...]

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. 193[...]

[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. 195[...]

[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. 196[...]

[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. Use the online manu al SymfoWARE/RDB Message R[...]

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. 200[...]

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. SQLST A T E [...]

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 . In this manual, a prog[...]

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. T he column name is used in SQL statements for data manipulation to s pecify a column to b e processed[...]

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. If the following conditi on applies, the value defi ned in the [...]

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. However , to retrieve the act[...]

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. The IN predicate is the predicate in IN (C , D, ...) or IN subquery . Related term: predicate Indicator variable In high-level languages such as C langu age and COBOL, the in[...]

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. NOT NULL constraint The NOT NULL constraint is a constraint that can be applied to c[...]

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. Procedure routine definition A procedure routine definition defin es a procedure[...]

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. Selection refers to fetching rows that match specified co nditions from a table. Projec[...]

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. Select column list SQL statement query specificat ions and si ngle row SELECT statements specify columns to be targeted for dat a re[...]

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. Sort specification When an SQL statement for manipulating da ta gets the re sults of a query expressi on u sing a cursor [...]

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. A storage structure is e[...]

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. [...]