English dropdown-ico

Manual Fujitsu J2X0-1634-01EN

227 pages 27.02 mb
Download

Go to site of 227

Summary
  • Fujitsu J2X0-1634-01EN - page 1

    J2X0-1634-01E N SymfoWARE(R)Server SQL Beginner's Guide ...

  • Fujitsu J2X0-1634-01EN - page 2

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

  • Fujitsu J2X0-1634-01EN - page 3

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

  • Fujitsu J2X0-1634-01EN - page 4

    SymfoW ARE Programmer's Kit Position of this manual The manual system and the position of this manual are as follows: iii ...

  • Fujitsu J2X0-1634-01EN - page 5

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

  • Fujitsu J2X0-1634-01EN - page 6

    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 ? ...

  • Fujitsu J2X0-1634-01EN - page 7

    · 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 ...

  • Fujitsu J2X0-1634-01EN - page 8

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

  • Fujitsu J2X0-1634-01EN - page 9

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

  • Fujitsu J2X0-1634-01EN - page 10

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

  • Fujitsu J2X0-1634-01EN - page 11

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

  • Fujitsu J2X0-1634-01EN - page 12

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

  • Fujitsu J2X0-1634-01EN - page 13

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

  • Fujitsu J2X0-1634-01EN - page 14

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

  • Fujitsu J2X0-1634-01EN - page 15

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

  • Fujitsu J2X0-1634-01EN - page 16

    [Figure: Relationship bet ween STOCK t a ble, ORDER t able, and COMP ANY t able] 7 ...

  • Fujitsu J2X0-1634-01EN - page 17

    8 ...

  • Fujitsu J2X0-1634-01EN - page 18

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

  • Fujitsu J2X0-1634-01EN - page 19

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

  • Fujitsu J2X0-1634-01EN - page 20

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

  • Fujitsu J2X0-1634-01EN - page 21

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

  • Fujitsu J2X0-1634-01EN - page 22

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

  • Fujitsu J2X0-1634-01EN - page 23

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

  • Fujitsu J2X0-1634-01EN - page 24

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

  • Fujitsu J2X0-1634-01EN - page 25

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

  • Fujitsu J2X0-1634-01EN - page 26

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

  • Fujitsu J2X0-1634-01EN - page 27

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

  • Fujitsu J2X0-1634-01EN - page 28

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

  • Fujitsu J2X0-1634-01EN - page 29

    [T able: Dat a type of columns t argeted by set functions and dat a type of the resu lts] 20 ...

  • Fujitsu J2X0-1634-01EN - page 30

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

  • Fujitsu J2X0-1634-01EN - page 31

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

  • Fujitsu J2X0-1634-01EN - page 32

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

  • Fujitsu J2X0-1634-01EN - page 33

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

  • Fujitsu J2X0-1634-01EN - page 34

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

  • Fujitsu J2X0-1634-01EN - page 35

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

  • Fujitsu J2X0-1634-01EN - page 36

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

  • Fujitsu J2X0-1634-01EN - page 37

    [Figure: Adding dat a using a complex query specification ] 28 ...

  • Fujitsu J2X0-1634-01EN - page 38

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

  • Fujitsu J2X0-1634-01EN - page 39

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

  • Fujitsu J2X0-1634-01EN - page 40

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

  • Fujitsu J2X0-1634-01EN - page 41

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

  • Fujitsu J2X0-1634-01EN - page 42

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

  • Fujitsu J2X0-1634-01EN - page 43

    [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", ...

  • Fujitsu J2X0-1634-01EN - page 44

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

  • Fujitsu J2X0-1634-01EN - page 45

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

  • Fujitsu J2X0-1634-01EN - page 46

    [Figure: Example of deleting all rows from a t able] 37 ...

  • Fujitsu J2X0-1634-01EN - page 47

    38 ...

  • Fujitsu J2X0-1634-01EN - page 48

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

  • Fujitsu J2X0-1634-01EN - page 49

    [Figure: Cursor overview] 40 ...

  • Fujitsu J2X0-1634-01EN - page 50

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

  • Fujitsu J2X0-1634-01EN - page 51

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

  • Fujitsu J2X0-1634-01EN - page 52

    · 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 ...

  • Fujitsu J2X0-1634-01EN - page 53

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

  • Fujitsu J2X0-1634-01EN - page 54

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

  • Fujitsu J2X0-1634-01EN - page 55

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

  • Fujitsu J2X0-1634-01EN - page 56

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

  • Fujitsu J2X0-1634-01EN - page 57

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

  • Fujitsu J2X0-1634-01EN - page 58

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

  • Fujitsu J2X0-1634-01EN - page 59

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

  • Fujitsu J2X0-1634-01EN - page 60

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

  • Fujitsu J2X0-1634-01EN - page 61

    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 , ...

  • Fujitsu J2X0-1634-01EN - page 62

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

  • Fujitsu J2X0-1634-01EN - page 63

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

  • Fujitsu J2X0-1634-01EN - page 64

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

  • Fujitsu J2X0-1634-01EN - page 65

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

  • Fujitsu J2X0-1634-01EN - page 66

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

  • Fujitsu J2X0-1634-01EN - page 67

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

  • Fujitsu J2X0-1634-01EN - page 68

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

  • Fujitsu J2X0-1634-01EN - page 69

    [Figure: Example in which multiple t ables are joined and dat a manipulated] 60 ...

  • Fujitsu J2X0-1634-01EN - page 70

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

  • Fujitsu J2X0-1634-01EN - page 71

    fetched. [Figure: Example in which multiple tables are j oined using a join table] 62 ...

  • Fujitsu J2X0-1634-01EN - page 72

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

  • Fujitsu J2X0-1634-01EN - page 73

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

  • Fujitsu J2X0-1634-01EN - page 74

    [Figure: Example of joining a t able to it self and manipulating dat a] 65 ...

  • Fujitsu J2X0-1634-01EN - page 75

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

  • Fujitsu J2X0-1634-01EN - page 76

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

  • Fujitsu J2X0-1634-01EN - page 77

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

  • Fujitsu J2X0-1634-01EN - page 78

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

  • Fujitsu J2X0-1634-01EN - page 79

    70 ...

  • Fujitsu J2X0-1634-01EN - page 80

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

  • Fujitsu J2X0-1634-01EN - page 81

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

  • Fujitsu J2X0-1634-01EN - page 82

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

  • Fujitsu J2X0-1634-01EN - page 83

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

  • Fujitsu J2X0-1634-01EN - page 84

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

  • Fujitsu J2X0-1634-01EN - page 85

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

  • Fujitsu J2X0-1634-01EN - page 86

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

  • Fujitsu J2X0-1634-01EN - page 87

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

  • Fujitsu J2X0-1634-01EN - page 88

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

  • Fujitsu J2X0-1634-01EN - page 89

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

  • Fujitsu J2X0-1634-01EN - page 90

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

  • Fujitsu J2X0-1634-01EN - page 91

    82 ...

  • Fujitsu J2X0-1634-01EN - page 92

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

  • Fujitsu J2X0-1634-01EN - page 93

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

  • Fujitsu J2X0-1634-01EN - page 94

    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& ...

  • Fujitsu J2X0-1634-01EN - page 95

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

  • Fujitsu J2X0-1634-01EN - page 96

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

  • Fujitsu J2X0-1634-01EN - page 97

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

  • Fujitsu J2X0-1634-01EN - page 98

    [T able: Comp arable dat a ty pes] The following is an example o f specifying a comparison predicate: Example 1: 89 ...

  • Fujitsu J2X0-1634-01EN - page 99

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

  • Fujitsu J2X0-1634-01EN - page 100

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

  • Fujitsu J2X0-1634-01EN - page 101

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

  • Fujitsu J2X0-1634-01EN - page 102

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

  • Fujitsu J2X0-1634-01EN - page 103

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

  • Fujitsu J2X0-1634-01EN - page 104

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

  • Fujitsu J2X0-1634-01EN - page 105

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

  • Fujitsu J2X0-1634-01EN - page 106

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

  • Fujitsu J2X0-1634-01EN - page 107

    [Figure: Example of specifyi ng a quantified predicate] 98 ...

  • Fujitsu J2X0-1634-01EN - page 108

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

  • Fujitsu J2X0-1634-01EN - page 109

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

  • Fujitsu J2X0-1634-01EN - page 110

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

  • Fujitsu J2X0-1634-01EN - page 111

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

  • Fujitsu J2X0-1634-01EN - page 112

    [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)" ...

  • Fujitsu J2X0-1634-01EN - page 113

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

  • Fujitsu J2X0-1634-01EN - page 114

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

  • Fujitsu J2X0-1634-01EN - page 115

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

  • Fujitsu J2X0-1634-01EN - page 116

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

  • Fujitsu J2X0-1634-01EN - page 117

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

  • Fujitsu J2X0-1634-01EN - page 118

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

  • Fujitsu J2X0-1634-01EN - page 119

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

  • Fujitsu J2X0-1634-01EN - page 120

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

  • Fujitsu J2X0-1634-01EN - page 121

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

  • Fujitsu J2X0-1634-01EN - page 122

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

  • Fujitsu J2X0-1634-01EN - page 123

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

  • Fujitsu J2X0-1634-01EN - page 124

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

  • Fujitsu J2X0-1634-01EN - page 125

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

  • Fujitsu J2X0-1634-01EN - page 126

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

  • Fujitsu J2X0-1634-01EN - page 127

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

  • Fujitsu J2X0-1634-01EN - page 128

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

  • Fujitsu J2X0-1634-01EN - page 129

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

  • Fujitsu J2X0-1634-01EN - page 130

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

  • Fujitsu J2X0-1634-01EN - page 131

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

  • Fujitsu J2X0-1634-01EN - page 132

    123 ...

  • Fujitsu J2X0-1634-01EN - page 133

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

  • Fujitsu J2X0-1634-01EN - page 134

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

  • Fujitsu J2X0-1634-01EN - page 135

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

  • Fujitsu J2X0-1634-01EN - page 136

    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, ...

  • Fujitsu J2X0-1634-01EN - page 137

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

  • Fujitsu J2X0-1634-01EN - page 138

    129 ...

  • Fujitsu J2X0-1634-01EN - page 139

    130 ...

  • Fujitsu J2X0-1634-01EN - page 140

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

  • Fujitsu J2X0-1634-01EN - page 141

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

  • Fujitsu J2X0-1634-01EN - page 142

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

  • Fujitsu J2X0-1634-01EN - page 143

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

  • Fujitsu J2X0-1634-01EN - page 144

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

  • Fujitsu J2X0-1634-01EN - page 145

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

  • Fujitsu J2X0-1634-01EN - page 146

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

  • Fujitsu J2X0-1634-01EN - page 147

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

  • Fujitsu J2X0-1634-01EN - page 148

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

  • Fujitsu J2X0-1634-01EN - page 149

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

  • Fujitsu J2X0-1634-01EN - page 150

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

  • Fujitsu J2X0-1634-01EN - page 151

    [Figure: Example of application program tha t executes a dynamic SELECT statem ent] 142 ...

  • Fujitsu J2X0-1634-01EN - page 152

    143 ...

  • Fujitsu J2X0-1634-01EN - page 153

    144 ...

  • Fujitsu J2X0-1634-01EN - page 154

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

  • Fujitsu J2X0-1634-01EN - page 155

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

  • Fujitsu J2X0-1634-01EN - page 156

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

  • Fujitsu J2X0-1634-01EN - page 157

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

  • Fujitsu J2X0-1634-01EN - page 158

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

  • Fujitsu J2X0-1634-01EN - page 159

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

  • Fujitsu J2X0-1634-01EN - page 160

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

  • Fujitsu J2X0-1634-01EN - page 161

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

  • Fujitsu J2X0-1634-01EN - page 162

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

  • Fujitsu J2X0-1634-01EN - page 163

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

  • Fujitsu J2X0-1634-01EN - page 164

    [Figure: Example of application program tha t executes a dynamic SELECT statem ent] 155 ...

  • Fujitsu J2X0-1634-01EN - page 165

    156 ...

  • Fujitsu J2X0-1634-01EN - page 166

    157 ...

  • Fujitsu J2X0-1634-01EN - page 167

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

  • Fujitsu J2X0-1634-01EN - page 168

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

  • Fujitsu J2X0-1634-01EN - page 169

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

  • Fujitsu J2X0-1634-01EN - page 170

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

  • Fujitsu J2X0-1634-01EN - page 171

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

  • Fujitsu J2X0-1634-01EN - page 172

    [Figure: Example of an a pplication p rogram that us es the SQLDA str ucture (w ith dynamic p a rameter specification)] 163 ...

  • Fujitsu J2X0-1634-01EN - page 173

    164 ...

  • Fujitsu J2X0-1634-01EN - page 174

    165 ...

  • Fujitsu J2X0-1634-01EN - page 175

    166 ...

  • Fujitsu J2X0-1634-01EN - page 176

    167 ...

  • Fujitsu J2X0-1634-01EN - page 177

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

  • Fujitsu J2X0-1634-01EN - page 178

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

  • Fujitsu J2X0-1634-01EN - page 179

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

  • Fujitsu J2X0-1634-01EN - page 180

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

  • Fujitsu J2X0-1634-01EN - page 181

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

  • Fujitsu J2X0-1634-01EN - page 182

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

  • Fujitsu J2X0-1634-01EN - page 183

    [Figure: Example of an a pplication program t hat uses the SQL descript or area (w ith dynamic parameter specification)] 174 ...

  • Fujitsu J2X0-1634-01EN - page 184

    175 ...

  • Fujitsu J2X0-1634-01EN - page 185

    176 ...

  • Fujitsu J2X0-1634-01EN - page 186

    177 ...

  • Fujitsu J2X0-1634-01EN - page 187

    178 ...

  • Fujitsu J2X0-1634-01EN - page 188

    179 ...

  • Fujitsu J2X0-1634-01EN - page 189

    180 ...

  • Fujitsu J2X0-1634-01EN - page 190

    181 ...

  • Fujitsu J2X0-1634-01EN - page 191

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

  • Fujitsu J2X0-1634-01EN - page 192

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

  • Fujitsu J2X0-1634-01EN - page 193

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

  • Fujitsu J2X0-1634-01EN - page 194

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

  • Fujitsu J2X0-1634-01EN - page 195

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

  • Fujitsu J2X0-1634-01EN - page 196

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

  • Fujitsu J2X0-1634-01EN - page 197

    [Figure: Example of an application program tha t uses the USING argument] 188 ...

  • Fujitsu J2X0-1634-01EN - page 198

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

  • Fujitsu J2X0-1634-01EN - page 199

    "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 ...

  • Fujitsu J2X0-1634-01EN - page 200

    Example: In this example, the schema name is changed to "SCH1". 191 ...

  • Fujitsu J2X0-1634-01EN - page 201

    192 ...

  • Fujitsu J2X0-1634-01EN - page 202

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

  • Fujitsu J2X0-1634-01EN - page 203

    [T able: SQL data types and equiv alent C language host variable data types] 194 ...

  • Fujitsu J2X0-1634-01EN - page 204

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

  • Fujitsu J2X0-1634-01EN - page 205

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

  • Fujitsu J2X0-1634-01EN - page 206

    [T able: Time interval types and equiv alent data types for COBOL host v ariables] 197 ...

  • Fujitsu J2X0-1634-01EN - page 207

    198 ...

  • Fujitsu J2X0-1634-01EN - page 208

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

  • Fujitsu J2X0-1634-01EN - page 209

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

  • Fujitsu J2X0-1634-01EN - page 210

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

  • Fujitsu J2X0-1634-01EN - page 211

    202 ...

  • Fujitsu J2X0-1634-01EN - page 212

    203 ...

  • Fujitsu J2X0-1634-01EN - page 213

    204 ...

  • Fujitsu J2X0-1634-01EN - page 214

    205 ...

  • Fujitsu J2X0-1634-01EN - page 215

    206 ...

  • Fujitsu J2X0-1634-01EN - page 216

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

  • Fujitsu J2X0-1634-01EN - page 217

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

  • Fujitsu J2X0-1634-01EN - page 218

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

  • Fujitsu J2X0-1634-01EN - page 219

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

  • Fujitsu J2X0-1634-01EN - page 220

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

  • Fujitsu J2X0-1634-01EN - page 221

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

  • Fujitsu J2X0-1634-01EN - page 222

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

  • Fujitsu J2X0-1634-01EN - page 223

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

  • Fujitsu J2X0-1634-01EN - page 224

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

  • Fujitsu J2X0-1634-01EN - page 225

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

  • Fujitsu J2X0-1634-01EN - page 226

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

  • Fujitsu J2X0-1634-01EN - page 227

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

Manufacturer Fujitsu Category Computer Accessories

Documents that we receive from a manufacturer of a Fujitsu J2X0-1634-01EN can be divided into several groups. They are, among others:
- Fujitsu technical drawings
- J2X0-1634-01EN manuals
- Fujitsu product data sheets
- information booklets
- or energy labels Fujitsu J2X0-1634-01EN
All of them are important, but the most important information from the point of view of use of the device are in the user manual Fujitsu J2X0-1634-01EN.

A group of documents referred to as user manuals is also divided into more specific types, such as: Installation manuals Fujitsu J2X0-1634-01EN, service manual, brief instructions and user manuals Fujitsu J2X0-1634-01EN. Depending on your needs, you should look for the document you need. In our website you can view the most popular manual of the product Fujitsu J2X0-1634-01EN.

Similar manuals

A complete manual for the device Fujitsu J2X0-1634-01EN, how should it look like?
A manual, also referred to as a user manual, or simply "instructions" is a technical document designed to assist in the use Fujitsu J2X0-1634-01EN by users. Manuals are usually written by a technical writer, but in a language understandable to all users of Fujitsu J2X0-1634-01EN.

A complete Fujitsu manual, should contain several basic components. Some of them are less important, such as: cover / title page or copyright page. However, the remaining part should provide us with information that is important from the point of view of the user.

1. Preface and tips on how to use the manual Fujitsu J2X0-1634-01EN - At the beginning of each manual we should find clues about how to use the guidelines. It should include information about the location of the Contents of the Fujitsu J2X0-1634-01EN, FAQ or common problems, i.e. places that are most often searched by users in each manual
2. Contents - index of all tips concerning the Fujitsu J2X0-1634-01EN, that we can find in the current document
3. Tips how to use the basic functions of the device Fujitsu J2X0-1634-01EN - which should help us in our first steps of using Fujitsu J2X0-1634-01EN
4. Troubleshooting - systematic sequence of activities that will help us diagnose and subsequently solve the most important problems with Fujitsu J2X0-1634-01EN
5. FAQ - Frequently Asked Questions
6. Contact detailsInformation about where to look for contact to the manufacturer/service of Fujitsu J2X0-1634-01EN in a specific country, if it was not possible to solve the problem on our own.

Do you have a question concerning Fujitsu J2X0-1634-01EN?

Use the form below

If you did not solve your problem by using a manual Fujitsu J2X0-1634-01EN, ask a question using the form below. If a user had a similar problem with Fujitsu J2X0-1634-01EN it is likely that he will want to share the way to solve it.

Copy the text from the picture

Captcha
New picture

Comments (0)