Grouping the records in RPG

20 pts.
Tags:
AS/400 database
AS/400 development
AS/400 error messages
Embedded SQL
Report Program Generator
RPG
RPG/400
SQL precompile failed
SQL/400
How to I group the records in the database based on one particular field?? When i try using a group by clause in the embedded SQL, it give me a 'SQL precompile failed' error. How do I handle this?? And i have to display the employee records grouped by their locations... how can this be done??

Answer Wiki

Thanks. We'll let you know when a new response is added.

In SQL this would be an order on clause

Select * from EmpFile
Order on EmpLoc

Group by is used when the select clause has summary data
So you might use it like this

Select EmpLoc, count(EmpLoc) from EmpFile
Group by EmpLoc

Test your SQL’s using the interactive SQL — strSQL
Or from navigator

Alternatives – 1. Logical file keyed on the field or fields desired
2. OpnQryF

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Sloopy
    To use GROUP BY, you must also have an ORDER BY clause with the same field names in the same order. So, for the example given in the answer: Select EmpLoc from EmpFile Order by EmpLoc Group by EmpLoc In the SELECT field list you can add other fields which are modified by group functions, such as MIN(field) or SUM(field), but all column names in the SELECT statement which are NOT inside group functions MUST be in both the ORDER BY and GROUP BY clauses. Regards, Sloopy
    2,195 pointsBadges:
    report
  • philpl1jb
    Order by is not needed in a summary (Group By) SQL and it would have to be after the Group by clause. However, it didn't seem that group by was the correct phrase to answer the question.
    49,720 pointsBadges:
    report
  • BigKat
    And you can order by any fields in the result set - even the group functioned fields i.e. order by count(emp) desc Kevin C. Ketzler - Affiliated
    8,010 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following