DB2 SQL Errors Codes and Error Messages and Warnings from Error -214 to -219

SQLServerF1

Error: DB2 SQL Error: SQLCODE=-214, SQLSTATE=42822, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
AN EXPRESSION IN THE
FOLLOWING POSITION, OR
STARTING WITH position-or-expressionstart
IN THE clause-type CLAUSE IS
NOT VALID. REASON CODE =
reason-code
Explanation: The expression identified by the first
part of the expression expression-start in the clause-type
clause is not valid for the reason specified by the
reason-code as follows:
1 The fullselect of the select-statement is not a
subselect. Expressions are not allowed in the
ORDER BY clause for this type of
select-statement. This reason code occurs only
when clause-type is ORDER BY.
2 DISTINCT is specified in the SELECT clause,
and either a column name in the ORDER BY
clause cannot be matched exactly with a
column name in the select list, or a
sort-key-expression is specified in the ORDER
BY clause. This reason code occurs only when
clause-type is ORDER BY.
3 The select list includes an aggregate function
or the subselect includes a GROUP BY clause
and the expression is not an aggregate
function or does not match exactly with an
expression in the select list.
4 Grouping is caused by the presence of an
aggregate function in the ORDER BY clause.
This reason code occurs only when clause-type
is ORDER BY.
5 Expression in a GROUP BY clause cannot
contain a scalar-fullselect. This reason code
occurs only when clause-type is GROUP BY.
6 Invalid use of scalar-fullselect. This reason
code can be issued when the RETURN
statement of an SQL function contains a
scalar-fullselect, or a scalar-fullselect is passed
as an argument on a CALL statement for a
parameter that is defined as an input
parameter (IN).
System action: The statement cannot be executed.
Programmer response: Modify the select-statement
based on the reason specified by the reason-code. Use
the following suggestions to modify the
select-statement:
1 Remove the expression from the ORDER BY
clause. If attempting to reference a column of
the result, change the sort key to the
simple-integer or simple-column-name form. See
the ORDER BY syntax diagram in the DB2
SQL Reference for more information.
2 Remove DISTINCT from the SELECT clause.
3 Change the expression in the ORDER BY or
GROUP BY clause to an aggregate function or
change the clause to use a numeric column
identifier or a column name.
4 Add a GROUP BY clause or remove the
aggregate function from the ORDER BY clause.
5 Remove the scalar fullselect from the GROUP
BY clause.
6 Remove the scalar fullselect from the
statement.
SQLSTATE: 42822

Error: DB2 SQL Error: SQLCODE=-216, SQLSTATE=428C4, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
THE NUMBER OF ELEMENTS ON
EACH SIDE OF A PREDICATE
OPERATOR DOES NOT MATCH.
PREDICATE OPERATOR IS operator.
Explanation: The number of expressions specified on
the left-hand side of OPERATOR operator is unequal to
either the number of values returned by the fullselect
or to the number of expressions specified on the
right-hand side of the operator. The number of
expressions and the number of values/expressions on
either side of the operator must be equal.
System action: The statement was not executed.
Programmer response: Change the number of
expressions to match the number of values returned by
the fullselect or vice versa.
SQLSTATE: 428C4

Error: DB2 SQL Error: SQLCODE=-219, SQLSTATE=42704, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
THE REQUIRED EXPLANATION
TABLE table-name DOES NOT EXIST
Explanation: The EXPLAIN statement assumes the
existence of the explanation table and it is not defined
in the DB2 subsystem as a base table. Refer to chapter 2
of SQL Reference for more information.
System action: The statement cannot be executed.
Programmer response: Determine whether the
required explanation table does exist. If not, create the
required table.
SQLSTATE: 42704

Above are list of DB2 SQL Errors and Warnings from Error -214 to -219 received while performing certain operation against DB2 Database or related products.

SQLCODE – Regardless of whether the application program provides an SQLCA or a stand-alone variable, SQLCODE is set by DB2 after each SQL statement is
executed. DB2 conforms to the ISO/ANSI SQL standard as follows:
If SQLCODE = 0, execution was successful.
If SQLCODE > 0, execution was successful with a warning.
If SQLCODE < 0, execution was not successful.
SQLCODE = 100, “no data” was found. For example, a FETCH statement returned no data because the cursor was positioned after the last row of the result table.

SQLSTATE – SQLSTATE is also set by DB2 after the execution of each SQL statement. Thus, application programs can check the execution of SQL statements by testing SQLSTATE instead of SQLCODE.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about DB2 SQL Error Codes and Error Messages on Windows, Linux and Z/OS Operating Systems.

 

Leave a Reply

Your email address will not be published. Required fields are marked *