RE: AS400/DB2 Problem with Group By clause
From: Darrell Wesley (DarrellWesley_at_discussions.microsoft.com)
Date: 06/30/04
- Next message: LeeHolsen: "RE: basic ado question"
- Previous message: Dan: "Strange behavior with SQL text field and ADO Parameter"
- In reply to: Darrell Wesley: "AS400/DB2 Problem with Group By clause"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 30 Jun 2004 15:00:01 -0700
Found problem. BUT for all of those AS400 experts what is the best way to handle a query with a Group By clause that you are attempting to also bring back data that does not need to be in an aggegate. Like in this query the first 3 fields represent the PO number, PO line and PO date and what Id like to do is to sum the shipqtys together for each PO and Line keeping the original PO date in the output.
In order to solve this I've arbitrarily added the max function to the other fields just to satisfy the query syntax. Is there something better to use?
"Darrell Wesley" wrote:
> With the following query every thing is fine but when I add a Group By clause I get a strange error. Need help help in resolving what the problem is.
>
> SQL = "Select OHBPO#, DIPOLN, DIPODT, ODPN, O8BTPN,"
> SQL = SQL & " EO6DES, ODORGQ, O6CRQM, O6CRQY, OHORDD, ODSHPD,"
> SQL = SQL & " ODSHPQ AS SHIPQTY, CMCITY, ODCARR from ERMTST010.O4ER7"
>
> With adoConn
> .ConnectionString = AS400CONN
> .Open
> End With
> With adoCD
> .CommandText = SQL
> .CommandTimeout = 120
> .CommandType = adCmdText
> .ActiveConnection = adoConn
>
> End With
> Set adoRS = adoCD.Execute
>
> Adding the GROUP BY clause:
>
> SQL = "Select OHBPO#, DIPOLN, DIPODT, ODPN, O8BTPN,"
> SQL = SQL & " EO6DES, ODORGQ, O6CRQM, O6CRQY, OHORDD, ODSHPD,"
> SQL = SQL & " SUM(ODSHPQ) AS SHIPQTY, CMCITY, ODCARR from ERMTST010.O4ER7"
> SQL = SQL & " GROUP by OHBPO#, DIPOLN "
>
> The following error is produced:
>
> [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0122 - Column DIPODT or expression in Select list is not valid.
>
> Select OHBPO#, DIPOLN, DIPODT, ODPN, O8BTPN, EO6DES, ODORGQ, O6CRQM, O6CRQY, OHORDD, ODSHPD, SUM(ODSHPQ) AS SHIPQTY, CMCITY, ODCARR from ERMTST010.O4ER7 GROUP by OHBPO#, DIPOLN
- Next message: LeeHolsen: "RE: basic ado question"
- Previous message: Dan: "Strange behavior with SQL text field and ADO Parameter"
- In reply to: Darrell Wesley: "AS400/DB2 Problem with Group By clause"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
