Grouping the records in RPG
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??
Software/Hardware used:
Software/Hardware used:
ASKED:
October 21, 2008 4:39 AM
UPDATED:
October 22, 2008 8:37 PM
Answer Wiki:
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
To see all answers submitted to the Answer Wiki: View Answer History.
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
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.
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