Look at the With clause. It will create a temporary file that can be used in the Select below it. You can rename the fields in the With clause and use those names in the Select statement.
Here is one example
Exec SQL
With GetContent as
(Select crcomp, crclm#, crseq
From CREVIEWP a
Inner Join UCMVRECONP b
On b.recdocc = a.crcmcid and
b.recdocid = a.crcmdid
Inner join clmsitmp c
On a.crcomp = c.icomp and
a.crclm# = c.iclm# and
a.crseq = c.iseq
WHERE a.crcrtdt >= :FdteIso
and
a.crcrtdt <= :TDteIso
and
c.ienter >= :FDatcymd and
c.ienter <= :TDatcymd and
c.imstat <> 'D')
Select count(*) into :PdaCount from getcontent;
The With clause creates a temporary file named GetContent from the CREVIEWP file and then the select statement counts the records in that temp file.
Thanks,
Marvin
No trees were killed in the sending of this message, but a large number of electrons were terribly inconvenienced, traumatized, triggered, and crying for their safe spaces.
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Booth Martin
Sent: Monday, March 25, 2019 12:50 PM
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RPGLESQL interim processing file
I am asking the wrong question because I don't know enough to frame the question correctly.
I have an sql situation which is too complicated for me to grasp all of one piece. I would like to do half the process into a smaller file, and then complete the process on that smaller file.
What I am thinking is process A which gathers 12 or so fields from 3 very large files and then processes B through G which expand, group and/or orders those fields in various ways, as needed for various web services.
I realize I can create a file in QTEMP and then draw from that, but it occurs to me that I must not be the first to have this problem; maybe I should stop, look, and listen. SQL has surprised me before and no doubt will again.
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
Notice: This e-mail transmission may contain information that is proprietary, privileged and/or confidential and is intended exclusively for the person(s) to whom it is addressed. This message may also contain Protected Health Information (PHI) and must be treated confidentially and handled in accordance with HIPAA and other federal and state privacy laws. Any use, copying, retention or disclosure by any person other than the intended recipient or the intended recipient's designees is strictly prohibited. If you are not the intended recipient or their designee, please notify the sender immediately and delete this e-mail (and any accompanying attachments).
midrange.com
