Group By results into a temporary table

[bibarnes@yahoo.com]

Create the table in mylib usinf iseries naviogator or the CREATE statemnt in STRSQL. Then you can compile your program. After it is compiled (and tested) you can delete or DROP the table and let the program do it's own create. What I have done is to use QTEMP to create temporary tables. The downside is that in a batch job you can't see the table to check it. Hope this helps Bill Barnes

[Guest.Visitor]

Group By results into a temporary table

On V5R2 and up you could do: CREATE TABLE qtemp/test as ( SELECT code, sum(money)as total$, sum(numberof) as totalNumber FROM orders group by code order by code) WITH DATA That said, I'd rather just use embedded SQL as you initially intended to do (fetch). I recommend you download IBM Redbooks "Modernizing iSeries Application Data Access - a Roadmap Cornerstone.pdf" and "Who Knew You Could Do That with RPG IV? A Sorcerer's Guide to System Access and More.pdf" and check out examples on RPG and embedded SQL.

[Diane Mueller]

Group By results into a temporary table

My sql issue: In an sql editor I run a statement that calculates fields "money" and "number ordered", grouped by "item code" (only 10 items for this situation), print the report and all is good. Next, I want this same statement within an embedded sql program, and this is where confusion sets in... I've tried to "fetch into" a data structure, (see code below), but the results are row 1's data repeated throughout the data structure-no good. So then I thought to create a temporary table that will hold the results of my select statement, so basically I'm selecting/summing from table ORDERS and trying to input the 10 rows of data into my temporary table, but how?? I like the idea of the temporary table holding my results, but the program won't compile since the temporary table does not exist, the fields are not defined. I'm looking for any examples of "group by" results entered into another table, or into a data structure. Diane

---

Code

[T.Grove]

Group By results into a temporary table

I would also pick up Paul Conte's book "Database Design and Programming for DB2/400", and the Conte/Cravitz book "SQL/400 Developer's Guide". Both of these have been invaluable for me in learning to embed SQL into RPG. BTW, your cursor should return each of your ten rows one row at a time. You would just have to loop until done (SQLCOD = 100 = EOF). That's the point of a cursor. It is like the file pointer when reading a regular file. Then you would not have to create an additional table. See my code example

---

Code