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

I reformatted the query a bit so that one can actually see what it is doing (you know, just good for maintenance):
Now let's have a look at the error. If you look up the message SQL0119, you get this:
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.

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.

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

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.