DB2 SQL Errors Codes and Error Messages and Warnings from Error -120 to -122

SQLServerF1

Error: DB2 SQL Error: SQLCODE=-120, SQLSTATE=42903, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
AN AGGREGATE FUNCTION OR
OLAP SPECIFICATION IS NOT VALID
IN THE CONTEXT IN WHICH IT WAS
INVOKED
Explanation: An aggregate function, a user-defined
function that is sourced on an aggregate function, or an
OLAP specification is not permitted in the following
contexts:
v In a SET clause.
v In a VALUES clause.
v In a SET transition-variable statement.
v In an assignment statement.
v In the key-expression of an index definition.
v In the expression of a RETURN statement.
Aggregate functions or OLAP Specifications can only
be specified in a WHERE clause if the WHERE clause
appears within a subquery of a HAVING clause.
System action: The statement cannot be processed.
Programmer response: Change the statement so that
the aggregate function or OLAP specification is not
specified in a context where it is not allowed. Refer to
chapter 4 of SQL Reference for information about
restrictions on operands that can be specified within a
WHERE clause, SET clause, VALUES clause, SET
host-variable statement, SET transition-variable
statement, assignment statement, or as the expression
of a RETURN statement.
SQLSTATE: 42903

Error: DB2 SQL Error: SQLCODE=-121, SQLSTATE=42701, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
THE COLUMN name IS IDENTIFIED
MORE THAN ONCE IN THE INSERT
OR UPDATE OPERATION OR SET
TRANSITION VARIABLE STATEMENT
Explanation: The same column ‘name’ is specified
more than once, either in the list of object columns of
an insert operation, in the SET clause of an update
operation, or in a SET transition variable statement.
System action: The statement cannot be executed. No
data was inserted or updated in the object table.
Programmer response: Correct the syntax of the
statement so that each column name is specified only
once.
SQLSTATE: 42701

Error: DB2 SQL Error: SQLCODE=-122, SQLSTATE=42803, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
COLUMN OR EXPRESSION IN THE
SELECT LIST IS NOT VALID
Explanation: The SELECT statement contains one of
these errors:
v The statement contains a column name and an
aggregate function in the SELECT clause, but no
GROUP BY clause.
v A column name is contained in the SELECT clause
(possibly within a scalar function) but not in the
GROUP BY clause. Grouping expressions can be
used in a SELECT list. A grouping expression
specifies only one value for each group. A grouping
expression that is specified in this context must
exactly match a grouping expression that is specified
in the GROUP BY clause, except that blanks are not
significant.
Attention: A HAVING clause specified without a
GROUP BY clause implies a GROUP BY with no
columns. Thus, no column names are allowed in the
SELECT clause.
v An expression is specified in the SELECT clause but
not in the GROUP BY clause.
v A sort-key-expression was specified in the ORDER BY
clause, the result table contains grouped data, but the
select-clause and ORDER BY clause contain a mixture
of grouped data and non-grouped data. Grouping
expressions can be used in a sort-key-expression of an
ORDER BY clause. A grouping expression specifies
only one value for each group. A grouping
expression that is specified in this context must
exactly match a grouping expression that is specified
in the GROUP BY clause, except that blanks are not
significant.
System action: The statement cannot be executed.
Programmer response: You can correct the statement
by:
v Including the columns or expressions in the GROUP
BY clause that are in the SELECT clause, or
v Removing the columns or expressions from the
SELECT clause.
Refer to chapter 4 of SQL Reference for information
about the use of GROUP BY clauses in SQL statements.
SQLSTATE: 42803

Above are list of DB2 SQL Errors and Warnings from Error -120 to -122 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 *