IBM Mainframe Forum

[Raja190]

Select all

Select A.ID, count(B.count) as counter
from A,B
where A.ID=B.ID
group by a.ID;

The above sql just works fine, but if I add any column from table A it fails with below error.

I believe the group by can't be made if I include one more column so its failing. Could some please clarify?


DSNT408I SQLCODE = -122, ERROR: COLUMN OR EXPRESSION IN THE SELECT LIST IS
NOT VALID
DSNT418I SQLSTATE = 42803 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOGB SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -200 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF38' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

[Robert Sample]

You didn't want to post the code in error instead of what is working? How can anybody possibly help you without you posting what you tried that didn't work?

[Raja190]

Select all

Select A.ID,A.Name, count(B.count) as counter
from A,B
where A.ID=B.ID
group by a.ID;

 

When Name from table A is included in the select list, it through below error.

DSNT408I SQLCODE = -122, ERROR: COLUMN OR EXPRESSION IN THE SELECT LIST IS
NOT VALID
DSNT418I SQLSTATE = 42803 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOGB SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -200 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF38' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

[NicC]

it through below error.

This makes no senese - unless you meant 'threw' (past tesnse of 'to throw') in which case you are wrong. Errors are not thrwon on a z/OS system: they occur, are given, result in and several other ways - but NOT thrown.

[Robert Sample]

The Troubleshooting for DB2 manual says

-122
COLUMN OR EXPRESSION IN THE SELECT LIST IS NOT VALID
Explanation

The SELECT statement contains one of these errors:

The statement contains a column name and an aggregate function in the SELECT clause, but no GROUP BY clause.
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.
An expression is specified in the SELECT clause but not in the GROUP BY clause.
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 processed.
Programmer response

You can correct the statement by:

Including the columns or expressions in the GROUP BY clause that are in the SELECT clause, or
Removing the columns or expressions from the SELECT clause.

SQLSTATE

42803