Failed SQL statedment w/DB2

[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

[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 =---

[Serge Rielau]

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

Cheers
Serge