Solved

AS400 does not understand 'Group by'

Posted on 1999-01-22
16
780 Views
Last Modified: 2013-12-25
I'm sending a SQL SELECT command to AS400 that has a *group by* clause in it from VB (ADO) and I get back an error that has nothing to do with the *group by* - because if I remove the group by clause it runs fine.
0
Comment
Question by:emezei
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 1499358
Do you use DB2/400? Did you tried your query directly on the AS/400 (maybe you can get a more meaningful error description there)?

Send us your SQL query string. Maybe it's in there!!!
0
 

Author Comment

by:emezei
ID: 1499359
I believe it is DB2.  I have not had the training so far to work directly in the AS400 and the SQL *must* run from a VB application.  After the error messages I was getting, I broke down the query to it's simplest form and I still had the error.  I don't have to code here now but it was something like ...

Dim SQL$
Dim cmdGetMemberIds as ADODB.command
Dim rsMemberIds as ADODB.recordset

SQL$ = "Select memberID, SubsciberId, LastName, FirstName " _
  & "From MEMMAS " _
  & "Where SubscriberId = memberId " _
  & "Group by memberId "

Set cmdGetMemberIds.activeConnection = conAS400  'CONNECTION
WITH cmdGetMemberIds
  .CommandType = adCmdText
  .CommandText = SQL$
ENDWITH
Set rsMemberIds = cmdGetMemberIds.Execute

- When I have the *group by* and I run it I get an error back about something it doesn't like about the Last Name.


0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 100 total points
ID: 1499360
It has nothing to do with AS400.

Your query that is not correct.

When you use "Group By" clause, you have to aggregate or sum some fields to give you only a subset of the data.

Create a copy of your structure in Access and use its designer to create you query. Once your query done, it should work without much problem on AS-400.

You said VB6? You have the query designer in there that you can plug directly to your as-400 datasource and test your query!!!

Do not hesitate to come back here if you have any problems with this (SQL syntax or others).
0
 

Author Comment

by:emezei
ID: 1499361
Thanks for your quick response.  In FoxPro we did not have this restriction, I guess I have to get back to the manuals.  In my situation though, if I want to make sure that I get back only *one* MemberId in the resulset, - What would I use? - *count* ?
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 1499362
Why not try DISTINCT?

Select DISTINCT memberID, SubsciberId, LastName, FirstName
From MEMMAS
Where SubscriberId = memberId
0
 

Author Comment

by:emezei
ID: 1499363
Distinct is not good for me (I'm still thinking FoxPro - any one field being different in DISTINCT will end up in the result while with GROUP BY I can select the fields for the GROUP) because I have many more fields in this Select and I will not get a Distinct MemberId
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 1499364
Try if you have this function an the AS-400:

SELECT Field1, First(Field2) AS F2, First(Field3) AS F3
FROM TableX
GROUP BY Field1
0
 

Expert Comment

by:cpouliot
ID: 1499365
it supose to work
check you syntaxe maybe you have an error
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Expert Comment

by:cognition
ID: 1499366
I always liked this "feature" of foxpro. However if you think about it it is wrong to allow a group by on only one of a list of non aggregated fields.

If you have the following data

MemberID, FirstName, LastName, Sales
1         Oliver     Kelly     10
2         John       Smith     20
3         John       Doe       30

In foxpro you could run a query grouping by the firstname, and get
1         Oliver     Kelly     10
2         John       Smith     50

Foxpro has assumed which fields you want.

The following query should work :

SQL$ = "Select memberID, SubsciberId, LastName, FirstName " _
  & "From MEMMAS " _
  & "Where SubscriberId = memberId " _
  & "Group by memberId, SubsciberId, LastName, FirstName"

0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 1499367
Hey emezei, is it working well for you?
0
 

Author Comment

by:emezei
ID: 1499368
Sorry I didn't get back to you, yesterday was my first day back at work. - so far negative - I tried:
Select ...., Count(memberId) as DummyCount
also tried Sum(numberField) as DummyCount

.. group by MemberId
- But I got The same errors

I need to get a prototype ready by this coming Monday so for now I'm doing it in the client in a do-while loop where I'm populating a *listview* - but I would like to get this to work the proper way.  Thanks for your patience.
0
 
LVL 3

Expert Comment

by:cognition
ID: 1499369
Just add all the fields other than the aggregates, into the group by.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 1499370
Did you tried the comment left on january 22 - 01:01PM which says:

Try if you have this function an the AS-400:

SELECT Field1, First(Field2) AS F2, First(Field3) AS F3
FROM TableX
GROUP BY Field1
0
 

Author Comment

by:emezei
ID: 1499371
Hi emoreau,
No I just did not get a chance - I've got to find some time tomorrow to try it out. What exactly does the *First* function do?   Return the first record it finds?

Thanks.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 1499372
Exactly. First returns the first field it finds for the group ('cause you have a GROUP BY clause).

You can also use Last (I don't think that I have to say why???)

Come back to us with an answer (good or bad)!
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 1499373
Hey emezei!

Is the life good for you? Is it finally working?
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now