Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2008
    Posts
    3

    Question Unanswered: Group By on inner join fails

    I'm porting this query from SQLServer where it works.
    Actually I have shortened it somewhat to make it easier for you kind people to spot the problem. Please ignore the odd space in the query, it isn't really there, something to do with the message board. If I take the group by clause out then it is fine.

    select 'Invoice' as DocumentType,t.transactionDate,t.actualDate,t.amou nt,t.outstanding,t.customerref,t.customerId as debtorno,t.documentNumber,t.clientId as clientno,t.datePosted,t.duedate,0 as x, 'null' as y from AR_Invoice t,AR_debtor d,client c
    inner join usertable u on c.parentid = u.id where t.clientid = c.clientnumber
    group by u.id,t.transactionDate,t.actualDate,t.amount,t.cus tomerref,t.customerId,t.documentNumber,t.clientId, t.datePosted
    order by ClientId,debtorno,transactionDate

    The error I get is:
    Error: com.ibm.db2.jcc.b.nm: DB2 SQL Error: SQLCODE=-119, SQLSTATE=42803, SQLERRMC=DUEDATE, DRIVER=3.50.152, SQL State: 42803, Error Code: -119

    This is referring to a problem with one of the columns in the group by clause, but they look fine to me (and to SQLServer). Is there a way to tell which column? Thanks for any help.

    Version info:
    DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09050" with level identifier "03010107". Informational tokens are "DB2 v9.5.0.808", "s071001", "NT3295", and Fix Pack "0". Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".

    Product name: "DB2 Express-C"
    License type: "Unwarranted"
    Expiry date: "Permanent"
    Product identifier: "db2expc"
    Version information: "9.5"
    Max number of CPUs: "2"

    O/S is Windows Server 2003

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I reformatted the query a bit so that one can actually see what it is doing (you know, just good for maintenance):
    Code:
    SELECT 'Invoice' AS DocumentType,
           t.transactionDate,
           t.actualDate,
           t.amou nt,
           t.outstanding,
           t.customerref,
           t.customerId AS debtorno,
           t.documentNumber,
           t.clientId AS clientno,
           t.datePosted,
           t.duedate,
           0 AS x,
           'null' AS y
    FROM   AR_Invoice t, AR_debtor d, client c
              INNER JOIN
           usertable u ON c.parentid = u.id
    WHERE  t.clientid = c.clientnumber
    GROUP BY u.id,
           t.transactionDate,
           t.actualDate,
           t.amount,
           t.cus tomerref,
           t.customerId,
           t.documentNumber,
           t.clientId,
           t.datePosted
    ORDER BY ClientId,
           debtorno,
           transactionDate
    Now let's have a look at the error. If you look up the message SQL0119, you get this:
    SQL0119N An expression starting with "<expression-start>" specified in
    a SELECT clause, HAVING clause, or ORDER BY clause is not
    specified in the GROUP BY clause or it is in a SELECT clause,
    HAVING clause, or ORDER BY clause with a column function and no
    GROUP BY clause is specified
    You have to fit in the SQLERRMC information into the message, i.e. <expression-start> is actually DUEDATE. The message tells you that you have DUEDATE in the SELECT list but not the GROUP BY clause.

    But let's have a look at the query in general because there are things that don't make sense at all:
    • What's the purpose of the GROUP BY? You don't do any aggregations (no column functions), so what should happen on each group?
    • Have you considered how the system should handle such a case:
      Code:
      col1 col2 col3
      ---- ---- ----
      1    1    1
      1    1    2
      3    2    7
      2    5    8
      
      SELECT col1, col2, col3
      FROM   ...
      GROUP BY col1, col2
      Now you have 3 groups where one group with (col1, col2) = (1, 1) is made up of 2 rows. Which value should the system pick for col3 in the result? It could choose between value 2 and value 3 - or it could simply require that you tell the system what you want to have. The former is what SQL Server is doing, the latter is what the SQL standard mandates.
    • Maybe you just want to use the GROUP BY to do some sort of DISTINCT operation, i.e. eliminate duplicates?


    p.s: This has nothing to do with inner joins - just with undetermined semantics of the query.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2008
    Posts
    3

    Thumbs up

    Thanks for the rapid reply. Very helpful and nicely explained.
    Okay, so I do need to include EVERY select field in my group by, that's new syntax to me. Although now I think about it I have come across it before.

    I agree the group-by doesn't make sense in that query but I did shorten it. The original has about 5 union clauses in it and I thought it might be too scary (esp the way I format these things).

    You've also given me a better idea of interpreting the error messages, they've been looking kinda cryptic but you've clarified that for me.

    Thanks a lot.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I don't understand this: What is the GROUP BY shortening? What exactly should the GROUP BY do in the query? It should have a purpose...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Apr 2008
    Posts
    3
    I shortened the whole query for clarity, removing several union clauses. One of the union clauses had a sum() in it, which is why I needed the group by. So in the shortened query the group-by does not have a purpose, except to demonstrate the problem. In the full query it does.

    However, once you pointed it out, I realised I could put the sum() in a subquery and so now I have been able to eliminate the group-by after all.

Posting Permissions

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