Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unanswered: DB2: GROUP BY problem

    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!!

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    $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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Dec 2003
    Posts
    4

    db2 ? SQL0122N

    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.

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    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?

  5. #5
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    ...and 'grouping' purely to make distinct with 'A.QTY_SHIPPED*A.UNIT_PRICE' in the select ain't gonna get you what you want!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •