by iseriesflorida » Wed, 20 May 2009 21:10:50 GMT

I have a file(test) with lets say 7 records, I want the count(fieldA)
and the sum(fieldb). I then need to take the count from fieldA and
the sum from field(b) and put these values into a file, is this
possible?? If so could someone provide a small example.

I can define the file test as input specs but I am having problems
with count and sum part. I believe I will need to rename these fields
as well and my take is that I could do this with a simple insert once
all records were read.


by iseriesflorida » Wed, 20 May 2009 22:12:35 GMT

This sql works:
select count(odobnm) as name, sum(odobsz) as size
from qtemp/jrnrcv

here is the ilerpg code that is not working:

D SIZE S 31 0
D NAME S 9 0
c/exec sql
c+ declare mainCursor Cursor
c+ for
c+ select sum(odobsz) as size, count(odobnm) as name
c+ from qtemp/jrnrcv
c/exec sql
c+ insert into bob/rmtboxinfo


by CRPence » Wed, 20 May 2009 22:31:57 GMT

If using SQL, there would be no F-spec [file /input specs/] nor
generally should there bei.e. SQL statements define the utilization
of the file, not the RPG statements. An exception is if\when the
file is declared for USROPN, with the intent only for reference as
DS; no open by RPG ever need take place. There is no reason to
/read/ the records, because the SQL has statements that insert data
without the program ever needing to /see/ any individual rows.

The simplest statement is to just create a TABLE as a result of
the SELECT. For example:

create table as
( select count(fieldA) as CountA
/* or use count(*), verify for sure which is desired */
, sum(fieldB) as SumB
) with data

If the table already exists then the same SELECT on an INSERT
INTO instead of the CREATE TABLE AS. For example:

insert into TheExistingTable
( select count(fieldA) as CountA
, sum(fieldB) as SumB

Regards, Chuck


by CRPence » Thu, 21 May 2009 01:25:58 GMT

As noted in a prior reply, the F-spec is undesirable because its
inclusion without USROPN keyword will cause the file to be
implicitly opened by the RPG activation, even though there is no
READ activity for the program; i.e. the extra open is a pointless
and expensive.

Please note that ODOBSZ is deprecated due to its being too small
to represent larger object sizes. References to that field should
be replaced by the expression, (number of units * unit size); I do
not recall the field names.

The first statement produces a cursor result set for an OPEN, but
a FETCH would be required to read the rows; no OPEN nor FETCH is
code. There seems to be no reason to do so, however.

The second statement [INSERT INTO] wants to use variables but the
first does not SELECT INTO the variables, nor does a READ assign
them. Also the variable names are suspect, given the values in the
summary query are for size & count, but the variables in the insert
statement are size & name. A revised variation of the SELECT might
look like the following, if the desire is to use an insert with the
variables while avoiding cursor, open, and fetch:

select sum(odxxxunits*odxxxsizeu)
, count(odobnm)
into :size, :nbr
from qtemp/jrnrcv

Then the revised insert presumably could then be:

insert into bob/rmtboxinfo
values(:size, :nbr)

As in the prior reply, the SQL statements allow INSERT INTO
SELECT FROM or CREATE TABLE AS SELECT to avoid ever having to /read/
or /FETCH/ the data from the file into the program; i.e. just ask
the SQL to do everything.

Regards, Chuc

Similar Threads

1. Embedded SQL calling another Embedded SQL program

Anyone deal with a full SQLRPG Video program calling another SQLRPG Video 
program without losing the cursor pointers in the first program?

Program A is a standalone SQLRPG program which simply displays a subfile. 
Program A requires a call to another video program (Program B) and Program B 
in turn is normally a standalone program as well.

Program A and Program B both have standard CONNECT RESET and DISCONNECT 

When program A calls program B, and then I return back to Program A, it will 
not scroll down and load additional records.  The job logs indicate 
obviously that the database is not connected and/or the cursor is not open. 
I know why it's doing it but not necessarily know how to fix it.  By calling 
program B, the database connection and cursor is closed in A and I doing a 
subfile reload is not really an option because users can change values in 
the subfile which a reload would wipe out.

If someone can point me in the right direction and/or post a link to some 
type of article, it would really help!

Any advice would be greatly appreciated.


2. help with SQL embedded in C: SQLSTATE problems

3. Problem with SQL in STRSQL vs EMBED SQL in Cobol

Hi, we have performance problem with a query in a Cobol.  The same
query is really quick with strsql, but in the SQL cobol, it's very
slow.  Both are run interactive.

Somebody has an idea ?


4. Embedded SQL in RPG - AS/400

5. Recursive Subprocedure that contain Embedded SQL....

I have a question about this..

For some reason when the Subprocedure calls itself it everything works great
except for the fact that the SQL portion is reusing the existing cursor from
the original caller.

Has anyone seen this happen?

The SQL statement gets updated just fine..  (I justified this using debug)

The fetch bring back the next row from the original caller.

Jeff P

6. Read and Update same file in embedded SQL - AS/400

7. Embedded SQL in RPG with *ISO dates

I am new to SQL and have been writting some programs to get a taste of
SQL.  I seem to be running into a wall everytime I mess with a file
that has *ISO dates (2007-12-31) that don't have anything in them
besides 0001-01-01.  It causes an SQL error every time.  Our ERP
system has all kinds of date *ISO date fields that are initially set
to 0001-01-01 until a real date is needed to be stored.

Is there some kind of compile option or something to use to stop this
error?  There must be something stupid I am doing or SQL is never
going to work well with our package.  Anybody have any ideas?

8. Synon with Embedded SQL - AS/400