SQL/MX 2.x Messages Manual (G06.24+, H06.03+)

Binder and Compilation Messages (4000 through
4999)
HP NonStop SQL/MX Messages Manual—523730-004
6-30
Recovery. An updatable cursor query should specify the target update column in the
select list of the query. Use the FOR UPDATE OF clause only if the cursor query is
updatable.
This example of a nonupdatable cursor join query does not select the target of the
update and receives an error:
>>SELECT A.PROD_CODE FROM EXPRODPARAMS A, MSRATES B
+>WHERE CASE WHEN B.UPDATE_FLAG IS NULL THEN 'N' ELSE
B.UPDATE_FLAG END = 'N'
+>AND A.ACC_TYPE = B.ACC_TYPE AND A.PROD_CODE = B.PROD_CODE
+>FOR UPDATE OF UPDATE_FLAG ;
*** ERROR[4001] Column UPDATE_FLAG is not found. Tables in
scope: A. Default schema: CAT.SCH.
*** ERROR[4117] The cursor query expression may be nonupdatable.
*** ERROR[8822] Unable to prepare the statement.
SQL 4118
Cause. You specified a join or a nonupdatable query in an updatable cursor query
(that is, one with an optional FOR UPDATE OF clause). A join query is not updatable.
Effect. The operation fails.
Recovery. An updatable cursor query should not specify a join or a nonupdatable
query. An SQL/MX statement cursor is updatable if all the following are true:
•
It is a SELECT statement.
•
There is only one table reference in the FROM clause, and there are no correlated
subquery references to that table. For example, this query is updatable:
SELECT A FROM T;
This one is not: SELECT A FROM T WHERE B = (SELECT C FROM U WHERE
T.I. = U.I)
•
There are no aggregates.
•
There are no GROUP BY, DISTINCT, or ORDER BY clauses.
•
All select_list columns are column references.
•
No column reference occurs more than once in the select list.
4118 The cursor query expression is not updatable.