By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,611 Members | 2,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,611 IT Pros & Developers. It's quick & easy.

Failed SQL statedment w/DB2

P: n/a
Osc
Hello all,

I am doing a search my test database and I am getting an error with
the sql search statement. I get the error message of : The statement
failed. Column ZCLOTNBR or expression in SELECT list not valid.
(SQLSTATE 42803, SQLCODE -122). Here is the SQL statement. I have
validated that all the fields exists.

SELECT L.ZCLOTNBR, L.ZCYRDNBR, L.ZCYRDCTY, DIGITS(L.ZCLOTCEN) AS
dLotCen, DIGITS(L.ZCLOTYER) AS dLotYear,
L.ZCAUCCCC, L.ZCAUCYYY, L.ZCAUCMMM, L.ZCAUCDDD, L.ZCLOTMDL,
L.ZCGRPMDL, L.ZCODRRCV, L.ZCODBCLC, L.ZCSTLTYP,
L.ZCDMGTYP, L.ZCVINNBR, Z.ZZYRDNBR, Z.ZZREQZIP, Z.ZZSTDMIL,
Z.ZZSTDZON, L.ZCCOD2UD, L.ZCASGCCC, L.ZCASGYYY,
L.ZCASGMMM, L.ZCASGDDD, L.ZCLOTNBR, L.ZCVINDES, L.ZCLOTMAK,
L.ZCRCDUSR, COUNT(L.ZCVINDES)
FROM LOTFNDZC01 L
INNER JOIN ZIPZONZZ01 Z
ON Z.ZZYRDNBR = L.ZCYRDNBR
WHERE L.ZCLOTMAK = '$(pMake)'
AND L.ZCLOTMDL = '$(pModel)'
AND L.ZCLOTYER > 98
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Ian
Osc wrote:
Hello all,

I am doing a search my test database and I am getting an error with
the sql search statement. I get the error message of : The statement
failed. Column ZCLOTNBR or expression in SELECT list not valid.
(SQLSTATE 42803, SQLCODE -122). Here is the SQL statement. I have
validated that all the fields exists.

SELECT L.ZCLOTNBR, L.ZCYRDNBR, L.ZCYRDCTY, DIGITS(L.ZCLOTCEN) AS
dLotCen, DIGITS(L.ZCLOTYER) AS dLotYear,
L.ZCAUCCCC, L.ZCAUCYYY, L.ZCAUCMMM, L.ZCAUCDDD, L.ZCLOTMDL,
L.ZCGRPMDL, L.ZCODRRCV, L.ZCODBCLC, L.ZCSTLTYP,
L.ZCDMGTYP, L.ZCVINNBR, Z.ZZYRDNBR, Z.ZZREQZIP, Z.ZZSTDMIL,
Z.ZZSTDZON, L.ZCCOD2UD, L.ZCASGCCC, L.ZCASGYYY,
L.ZCASGMMM, L.ZCASGDDD, L.ZCLOTNBR, L.ZCVINDES, L.ZCLOTMAK,
L.ZCRCDUSR, COUNT(L.ZCVINDES)
FROM LOTFNDZC01 L
INNER JOIN ZIPZONZZ01 Z
ON Z.ZZYRDNBR = L.ZCYRDNBR
WHERE L.ZCLOTMAK = '$(pMake)'
AND L.ZCLOTMDL = '$(pModel)'
AND L.ZCLOTYER > 98

Your error message description is not accurate:

SQL0122N A SELECT statement with no GROUP BY clause contains a
column name and a column function in the SELECT clause
or a column name is contained in the SELECT clause but
not in the GROUP BY clause.

Explanation:

The SELECT statement has one of the following errors:

o A column name and a column function are contained in the
SELECT clause but there is no GROUP BY clause.

o A column name is contained in the SELECT clause but not in
the GROUP BY clause.

The column may be contained in a scalar function.

If the NODENUMBER or PARTITION functions are specified in the
SELECT clause, then all partitioning key columns of the
underlying table are considered to be in the SELECT clause.

The statement cannot be processed.

User Response:

Correct the statement by including the columns in the GROUP BY
clause that are in the SELECT clause or by removing the columns
from the SELECT clause.

sqlcode : -122

sqlstate : 42803
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---
Nov 12 '05 #2

P: n/a
COUNT(L.ZCVINDES) ?
How many rows do you expect back.
Read SQL122 carefully. It says exactly what happens.

Cheers
Serge
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.