Table 11-5 shows details about the SQL statements that can be used for remote database access (for details about these SQL statements, see the HiRDB Version 8 SQL Reference manual).

Table 11-5 Details about SQL statements usable for remote database access

Category	SQL statement format	Usable at distributed server
		H	X	O	R	S
Variable	{:embeddedvariable [:indicatorvariable] | ? parameter}	Y9	Y9	Y9	Y9	Y9
Table name	RD-node-name.authorization-identifier.tableidentifier	Y1	Y1	Y1	Y1	Y1
	[authorization-identifier.] table-alias	Y1	Y1	Y1	Y1	Y1
	[authorization-identifier.] table-identifier	Y2	Y2	Y2	Y2	Y2
Table specification	{[authorization-identifier.] table-identifier |correlation-name}	Y3	Y3	Y	Y3	Y3
Column specification	[table-specification.] column-name	Y	Y	Y	Y	Y
	[table-specification.] repetition-column-name[subscript]	Y
Value specification	{literal | variable | USER	Y	Y	Y	Y	Y4
	| CURRENT DATE | CURRENT TIME	Y	Y
	| [statement-label.] SQL-variable-name |[[authorization-identifier.] routine-identifier.] SQL-parameter-name}
Term specification	{column-specification	Y	Y	Y	Y	Y
	| [statement-label.] SQL-variable-name | [[authorization-identifier.] routine-identifier.] SQL-parameter-name}
Set function	AVG, SUM, MAX, MIN, COUNT	Y	Y	Y	Y	Y
Scalar function	VALUE	Y	Y
	DATE, TIME, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DAYS,	Y	Y
	DECIMAL, DIGITS, FLOAT, INTEGER, CHARACTER, HEX,	Y	Y
	LENGTH, SUBSTR	Y	Y5	Y5
Labeled duration	(value-expression) {YEAR [S] | MONTH [S] | DAY [S] }	Y	Y
Primary	{(value-expression) | column-specification | value-specification | set-function | scalar-function	Y	Y	Y	Y	Y4
	| labeled-duration}	Y	Y
Value expression	{[{+ | -}] primary | value-expression {+ | - | * | / | ||} primary}	Y	Y	Y	Y4	Y4
Comparison operators	{= | < > | < | < = | > | > =}	Y	Y	Y	Y	Y4
Predicate	{value- expression IS [NOT] NULL	Y	Y	Y	Y	Y4
	| value- expression[NOT] LIKE value-specification	Y	Y	Y	Y	Y4
	| value-expression [NOT] BETWEEN value-expression AND value-expression	Y	Y	Y	Y	Y4
	| value-expression [NOT] IN {(value-specification [, value-specification] ... ) | subquery}	Y	Y	Y	Y	Y4
	| value-expression comparison-operator value-expression	Y	Y	Y	Y	Y
	| value-expression comparison-operator subquery	Y	Y	Y
	| value-expression comparison-operator{ ANY | ALL | SOME] subquery	Y	Y	Y
	| EXISTS subquery	Y	Y	Y
	| term-specification[NOT] XLIKE-pattern-character	Y
	| ARRAY (repetition-column-name[,repetition-column-name]...) [ANY](retrieval-condition)	Y
Search condition	{ [NOT] {(search-condition) | predicate}	Y	Y	Y	Y	Y4
	| search-condition OR {(search-condition) | predicate}	Y	Y	Y	Y	Y
	| search-condition AND {(search-condition) | predicate}}	Y	Y	Y	Y	Y
Selection expression	value-expression (other than a variable) | table-specification.*	Y	Y	Y	Y	Y
	| [table-specification.] ROW}
Query specification	{SELECT [{ ALL | DISTINCT}]	Y	Y	Y	Y
	{* | selection-expression [. selection-expression] }	Y	Y	Y	Y	Y
	FROM table-name [. table-name] ...	Y	Y	Y	Y	Y
	[WHERE search-condition]	Y	Y	Y	Y	Y
	[GROUP BY column -specification]	Y	Y	Y	Y	Y
	[HAVING search-condition]	Y	Y	Y	Y	Y
Query expression	{query-specification | (query-expression) | query-expression UNION [ALL] {query-specification | (query-expression)}}	Y	Y	Y
Static cursor declaration	DECLARE cursor-name CURSOR FOR query-expression	Y	Y	Y	Y6	Y6
	[ORDER BY {columnspecification | sort-item-specification-number} [{ASC | DESC}] [, {column-specification | sort-item-specification-number} [{ASC | DESC}]] ...]	Y	Y9	Y	Y7	Y7
	[{WITH {SHARE | EXCLUSIVE} LOCK	Y	Y
	| WITHOUT LOCK [{WAIT | NOWAIT}]}]	Y	Y		Y
	[WITH ROLLBACK]	Y	Y		Y
	| NO WAIT}]	Y
	[FOR {UPDATE [OF column-name [, column-name] ...]	Y	Y	Y	Y	Y
	| READ ONLY}]	Y	Y
Dynamic cursor declaration	DECLARE cursor-name CURSOR FOR SQL-statement-identifier-indicating-dynamic-SELECT-statement	Y	Y	Y	Y	Y
Dynamic SELECT statement Format 1	query-expression	Y	Y	Y	Y6	Y6
	[ORDER BY {column-specification|sort-item- specification-number} [{ASC | DESC}] [, {column-specification | sort-item-specification-number} [{ASC | DESC}]] ...]	Y	Y7	Y	Y7	Y7
	[{WITH {SHARE | EXCLUSIVE} LOCK	Y	Y
	| WITHOUT LOCK [{WAIT | NOWAIT}]}]	Y	Y		Y
	[WITH ROLLBACK]	Y	Y		Y
	| NO WAIT}]	Y
	[FOR UPDATE]	Y	Y	Y	Y	Y
Dynamic SELECT statement Format 2	SELECT {{column-name |repetition-column[[subscript]] |column-name..attribute-name[..attribute-name]..} [,{{column-name |repetition-column[[subscript]] |column-name..attribute-name[..attribute-name]..}].. |*} FROM LIST list-name [lock-option]
ASSIGN LIST statement Format 1	ASSIGN LIST list-name FROM ([authorization-identifier.]table-identifier) [WHERE search-condition] [WITHOUT LOCK [{WAIT|NOWAIT}]] [WITH ROLLBACK|NO WAIT]
ASSIGN LIST statement Format 2	ASSIGN LIST list-name FROM list-name-1 [{{AND|OR|AND NOT|ANDNOT}list-name-2 |FOR ALTERLIST]
DROP LIST statement	DROP {LIST list-name|ALL LIST}
OPEN statement Format 1	OPEN cursor-name [USING:embeddedvariable [, embeddedvariable] ...]	Y	Y	Y	Y	Y
OPEN statement Format 2	OPEN cursor-name USING DESCRIPTOR [:] SQL-data-area-name	Y	Y	Y	Y	Y
CLOSE statement	CLOSE cursor-name	Y	Y	Y	Y	Y
FETCH statement Format 1	FETCH cursor-name INTO variable [, variable] ...	Y	Y	Y	Y	Y
FETCH statement Format 2	FETCH cursor-name USING DESCRIPTOR [:]SQL-data-area-name	Y	Y	Y	Y	Y
	BY variable [ROWS]
FETCH statement Format 3	FETCH cursor-name INTO array-variable [, array-variable] ...
Single-row SELECT statement	SELECT [{ALL | DISTINCT}] (* | selectionexpression [, selection-expression] ...} INTO variable [, variable] ... FROM table-name [,table-name] ... [WHERE search-condition] [GROUP BY column-specification] [HAVING search-condition]	Y	Y	Y	Y
	[{WITH {SHARE | EXCLUSIVE} LOCK	Y	Y
	| WITHOUT LOCK [{WAIT | NOWAIT}]}]	Y	Y		Y
	[WITH ROLLBACK]	Y	Y		Y
	| NO WAIT}]	Y
Insertion value	{value-specification | NULL}	Y	Y	Y	Y	Y4
INSERT statement Format 1	INSERT INTO table-name [(columnname [, columnname] ...)] {VALUES (insertion-value [, insertionvalue] ...) | query-specification}	Y	Y	Y	Y	Y
	[WITH ROLLBACK]	Y	Y		Y
INSERT statement Format 2	INSERT INTO tablename (ROW) {VALUES (row-insertion-value) | query-specification} [WITH ROLLBACK]
INSERT statement Format 3	FOR variable INSERT INTO table-name[(column-name[,column-name]...)] {VALUES (insertion-value[,insertion-value]...) |query-specification} [WITH ROLLBACK]
INSERT statement Format 4	FOR variable INSERT INTO table-name(ROW) {VALUES (row-insertion-value)|query-specification} [WITH ROLLBACK]
Update value	{value-expression | NULL}	Y	Y	Y	Y	Y4
UPDATE statement Format 1	UPDATE table-name SET column-name=updatevalue [, columnname=update-value] ... [WHERE {search-condition| CURRENT OF cursor-name}]	Y	Y	Y	Y	Y
	UPDATE table-name SET repetition-column-name [{subscript|*}] =element-value[,repetition-column-name [{subscript|*}] =element-value] ... [WHERE{retrieval-condition| CURRENT OF cursor-name}]	Y
	UPDATE tablename ADD repetition-column-name[{subscript|*}] =ARRAY[element-value[,element-value] ...] [,repetition-column-name [{subscript|*}] =ARRAY[element-value[,element-value] ...) ... [WHERE{retrieval-condition |CURRENT OF cursor-name}]	Y
	UPDATE table-name DELETE repetition-columnname [{subscript|*}] [,repetition-column-name[ {subscript|*}]... [WHERE{retrieval-condition| CURRENT OF cursor-name}]	Y
	[WITH ROLLBACK]	Y	Y		Y
UPDATE statement Format 2	UPDATE table-name SET ROW=variable [WHERE {search-condition| CURRENT OF cursor-name}] [WITH ROLLBACK]
UPDATE statement Format 3	FOR variable UPDATE table-name SET column-name=update-value[,column-name=update-value]... [WHERE search-condition] [WITH ROLLBACK]
UPDATE statement Format 4	FOR variable UPDATE table-name SET ROW=variable [WHERE search-condition] [WITH ROLLBACK]
DELETE statement Format 1	DELETE FROM table-name [WHERE {search-condition |CURRENT OF cursor-name}]	Y	Y	Y	Y	Y
	[WITH ROLLBACK]	Y	Y		Y
DELETE statement Format 2	FOR variable DELETE FROM table-name WHERE search-condition
	[WITH ROLLBACK]
PREPARE statement	PREPARE SQL-statement-identifier FROM{'character-string' | variable}	Y	Y	Y	Y	Y
	[WITH SQLNAME OPTION]	Y	Y	Y
SQL statements preprocessable by PREPARE statement	INSERT, UPDATE (without cursor), DELETE (without cursor), dynamic SELECT statement	Y	Y	Y	Y	Y
	PURGE TABLE, LOCK TABLE, CALL	Y
	Definition SQL ASSIGN LIST, DROP LIST, dynamic SELECT statement Format 2
DESCRIBE OUTPUT statement	DESCRIBE [OUTPUT] SQL-statement-identifier INTO [:] SQL-Data-Area-name [[:] column-name-data-areaname]	Y	Y	Y	Y	Y
DESCRIBE INPUT statement	DESCRIBE INPUT SQL-statement-identifier INTO [:] SQL-data-area-name [[:] column-name-data-area-name]	Y
EXECUTE statement Format 1	EXECUTE SQL-statement-identifier	Y	Y	Y	Y	Y
	{INTO variable[,variable]... |INTO DESCRIPTOR [:]SQL -description-area-name}	Y
	[{USING variable[,variable]... |USING DESCRIPTOR [:]SQL -description-area-name}]	Y	Y	Y	Y	Y
EXECUTE statement Format 2	EXECUTE SQL-statement-identifier {USING array-variable[,array-variable]... |USING DESCRIPTOR [:]SQL-descriptor-area-name} BY variable [ROWS]
SQL statements executable by EXECUTE statement	SQL statements other than dynamic SELECT statement preprocessed by PREPARE statement	Y	Y	Y	Y	Y
EXECUTE IMMEDIATE statement	EXECUTE IMMEDIATE {'characterstring' | variable}	Y	Y	Y
SQL statements executable by EXECUTE IMMEDIATE statement	INSERT, UPDATE (without cursor), DELETE (without cursor)	Y	Y	Y
	PURGE TABLE, LOCK TABLE, CALL	Y
	Definition SQL ASSIGN LIST, DROP LIST
PURGE TABLE statement	PURGE TABLE table-name [WITH ROLLBACK | NO WAIT]	Y
LOCK TABLE statement	LOCK TABLE tablename [, tablename] ... [IN {SHARE | EXCLUSIVE} MODE] [WITH ROLLBACK | NO WAIT]	Y
	UNTIL DISCONNECT
CALL statement10	CALL RD-node-name.authorization-identifier. routine-identifier(argument-specification)	Y
GET DIAGNOSTICS statement	GET DIAGNOSTICS...	Y8	Y8	Y8	Y8	Y8
SET SESSION AUTHORIZATION statement	SET SESSION AUTHORIZATION :embedded-variable-1 [{USING|IDENTIFIED BY} :embedded-variable-2]
FREE LOCATOR statement	FREE LOCATOR :locator-reference [,:locator-reference]...
Assignment statement	SET assignment-destination=assignment-value

The letter in each column under the Usable at distributed server column denotes the following:

H: HiRDB

X: XDM/RD

O: ORACLE

R: RDB1 E2

S: SQL/K

Legend:

Y: Can be used.

: Cannot be used.

¹ The table name formats RD-node-name.authorization-identifier.table-identifier and [authorization-identifier.]table-alias can be used only when the default SQL connection is used as the current SQL connection.

² The table name format [authorization-identifier.]table-identifier can be used only when an SQL connection to a distributed RD-node is used as the current SQL connection.

³ If a correlation name contains both 1- and 2-byte characters, it may not be processed by a distributed server.

⁴ The following functions cannot be used:

• USER literal (SQL/K only)
• NULL literal (SQL/K only)
• Comparison operator || (RDB1 E2 only)
• Comparison operator <> (SQL/K only)
• Predicate and search condition NOT (SQL/K only)
• Scalar function (RDB1 E2 and SQL/K only)
• Query specifications ALL and DISTINCT (SQL/K only)

⁵ When the LENGTH or SUBSTR function is used on an MCHAR type column, HiRDB processes the data length and location based on the number of characters, while XDM/RD processes based on the number of bytes; this means that the execution results are different. Also, in ORACLE, a character type column may contain both one-byte and two-byte characters, so processing is performed based on the number of bytes when the LENGTH or SUBSTR function is used.

⁶ For RDB1 E2 or SQL/K, define using an inquiry specification rather than an inquiry equation.

⁷ When UNION[ALL] is not specified, XDM/RD, RDB1 E2, and SQL/K use a different number for the sort specification item number in the ORDER BY clause from the number used by other systems. In XDM/RD, RDB1 E2, and SQL/K, the number that represents the position of the selection expression of the column to be used as the sort key (the position specified in the SELECT clause) is specified. In other systems, the number that represents the position of the column to be used as the sort key (the position specified in a derived table) is specified.

⁸ Only errors that occur in the distributed server can be collected. For details about the errors that can occur in a distributed server, see 11.5 Handling distributed server errors.

⁹ Embedded variables and ? parameters that have repetition structures cannot be used.

¹⁰ Procedures that use the PURGE TABLE, COMMIT, or ROLLBACK statement cannot be executed.