• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 829
  • Last Modified:

AS400 does not understand 'Group by'

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
emezei
Asked:
emezei
  • 8
  • 5
  • 2
  • +1
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
emezeiAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
emezeiAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
Why not try DISTINCT?

Select DISTINCT memberID, SubsciberId, LastName, FirstName
From MEMMAS
Where SubscriberId = memberId
0
 
emezeiAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
cpouliotCommented:
it supose to work
check you syntaxe maybe you have an error
0
 
cognitionCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
Hey emezei, is it working well for you?
0
 
emezeiAuthor Commented:
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
 
cognitionCommented:
Just add all the fields other than the aggregates, into the group by.
0
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
emezeiAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
Hey emezei!

Is the life good for you? Is it finally working?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now