1.6.4 Specifiable locations

The following table indicates the locations where embedded variables, indicator variables, ? parameters, SQL variables, and SQL parameters can be specified.

Table 1-16 Locations where variables and parameterscan be specified

SQL statementSpecifiable locationsEmbedded variableIndicator variable? ParameterSQL variable or parameter
WRITE specification1st argumentNNNN
2nd argument, 3rd argumentYYYN
GET_JAVA_STORED_ROUTINE_SOURCE specification1st argument, 2nd argumentYYYY
3rd argumentNNNN
DECLARE CURSORAnywhere in a search condition where a literal is allowed#1YYNY
ALLOCATE CURSOR Format 1Extended cursor nameYNNN
Extended statement nameYNNN
ALLOCATE CURSOR Format 2Extended cursor nameYNNN
SELECTAnywhere in a search condition where a literal is allowedYYYY
INTO clauseYYNY
INSERTAnywhere in a VALUES clause where a literal is allowedYYYY
Anywhere in a search condition where a literal is allowedYYYY
UPDATEAnywhere in a SET clause where a literal is allowedYYYY
Anywhere in a search condition where a literal is allowedYYYY
Preparable dynamic UPDATE statement: locatingPositions where a literal can be specified using a SET clauseNNYN
DELETEAnywhere in a search condition where a literal is allowedYYYY
OPENUSING clauseYNNN
FETCHINTO clauseYYNY
PREPAREAnywhere an SQL character string is allowedYNNN
DEALLOCATE PREPAREExtended statement nameYNNN
DESCRIBEExtended statement nameYNNN
DESCRIBE CURSORExtended cursor nameYNNN
DESCRIBE TYPEExtended statement nameYNNN
EXECUTEINTO clauseYYNN
USING clauseYYNN
Extended statement nameYNNN
EXECUTE IMMEDIATEAnywhere an SQL character string is allowedYNNN
INTO clauseYYNN
USING clauseYYNN
CALLArgumentYYYY
Assignment statementAssignment destination and assigned valueY#2YY#2Y
FREE LOCATORSee LocatorYNNN
CONNECTAuthorization identifier and passwordYNNN
SET SESSION AUTHORIZATION statementAuthorization identifier and passwordYNNN
ALLOCATE CONNECTION HANDLEPDCNCTHDL-type variable, return code-receiving variable; connection PDHOST variable, and connection PDNAMEPORT variableYNNN
FREE CONNECTION HANDLEPDCNCTHDL-type variable, and return code-receiving variableYNNN
DECLARE CONNECTION HANDLE SETPDCNCTHDL-type variableYNNN
GET DIAGNOSTICSStatement information item name, condition information item nameYNNN
WRITE LINE statementvalue expressionNNNY

Y: Specifiable

N: Not specifiable

Note 1
Embedded variables and indicator variables are specified in a UAP. The ? parameter should be specified in an SQL character string that is preprocessed by the PREPARE statement. SQL parameters in an external routine are specified in a parameter variable specification in the external routine with which they are associated. When a parameter for an external routine is passed to an SQL or a routine, it is specified as an embedded variable or a ? parameter rather than as an SQL parameter. An SQL variable cannot be specified in an external routine.
Note 2
Embedded variables, indicator variables, and ? parameters cannot be specified in selection expressions.
Embedded variables, indicator variables, and ? parameters can be specified in the following cases:
  • Specifying in a function call argument
  • Specifying in an argument of the SUBSTR scalar function
For specification methods, see 2.3 Query specification.
Note 3
Arithmetic or comparison operations cannot be specified between embedded variables, indicator variables, and ? parameters.
Note 4
Embedded variables, indicator variables, and ? parameters cannot be specified in an argument of a set function.
Note 5
Embedded variables, indicator variables, and ? parameters cannot be specified in an argument of the HEX scalar function.
Note 6
Embedded variables, indicator variables, or ? parameters cannot be specified singly (including specification in a unary operation expression) in an argument of a scalar function, with the exception of the second and third arguments of VALUE, BIT_AND_TEST, or SUBSTR, or in the third argument of POSITION. However, embedded variables, indicator variables, or ? parameters of the BLOB or BINARY type, and these types only, can be specified if the AS data-type is specified in the first argument of SUBSTR, in an argument of LENGTH, or in the first or second argument of POSITION.
Note 7
Embedded variables, indicator variables, and ? parameters cannot be specified in a date, time, or concatenation operation.
Note 8
Embedded variables, indicator variables, and ? parameters cannot be specified singly in the first value expression of the VALUE scalar function (including specification in monomial operational expressions).
Note 9
Embedded variables, indicator variables, and ? parameters cannot be specified singly in the CASE, THEN, or ELSE value expression of a simple CASE expression or searched CASE expression (including specification in monomial operational expressions).
Note 10
Embedded variables, indicator variables, and ? parameters cannot be specified singly in the first WHEN value expression during simple CASE expression specification, the first value expression of COALESCE, or both value expressions of NULLIF (including specification in monomial operational expressions).
Note 11
Embedded variables, indicator variables, or ? parameters cannot be specified singly (including specification in a unary operation expression) in the two value expressions of the BIT_AND_TEST scalar function.

#1: Excludes the search condition of a CASE expression in a selection expression.

When a cursor declaration is specified in a function call argument, the function call can be specified in a search condition in the CASE expression of the selection expression.

#2: When specifying an embedded variable, an indicator variable, or a ? parameter singly as an assignment value in an assignment statement (SET), you must always specify the AS data-type.