Hello,

I'm trying to execute the following query with a SQL0122N error:

SELECT A.INVOICE_NUMBER, A.QTY_SHIPPED, C.SERIAL_NUMBER, A.QTY_SHIPPED*A.UNIT_PRICE AS TOTAL_REVENUE FROM BIDS.DAC_SHIP_LINE A, BIDS.DAC_SHIP_COMPONENT B, BIDS.DAC_SHIP_SERIAL C WHERE A.INVOICE_NUMBER = B.INVOICE_NUMBER AND A.LINE_NUMBER = B.LINE_NUMBER AND A.INVOICE_NUMBER = C.INVOICE_NUMBER AND A.LINE_NUMBER = C.LINE_NUMBER AND B.COMPONENT_NUMBER = C.COMPONENT_NUMBER AND A.QTY_SHIPPED ¬= B.QUANTITY_SHIPPED AND CONFIGURATION_FLAG = 'C' AND COMPONENT_IND = 'Y' GROUP BY A.INVOICE_NUMBER

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. SQLSTATE=42803


THANKS!!

$db2 "? SQL0122N "

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

Yes, that help page (as most DB2 help pages) is useless... if you look at my query, I have indeed included a column in the GROUP BY clause that is also in the SELECT clause.

I don't see a group function that would require you to have a group by clause. Have you tried the query without the GROUP BY clause?

...and 'grouping' purely to make distinct with 'A.QTY_SHIPPED*A.UNIT_PRICE' in the select ain't gonna get you what you want!