Re: RPGLESQL interim processing file

Hi Booth,

I'd go the way suggested by Kevin and Marvin.
I use CTEs ( Common Table Expressions ) a lot because I find it simplifies
things for me.

As a trivial example suppose you want to compare two libraries to see if
they contain the same objects.

And you are doing it over a DSPOBJD outfile which contains information for
BOTH libraries

The fields you care about in the DSPOBJD Outfile are:

- ODLBNM (library name)
- ODOBNM (object name)
- ODOBTP (object type)

In my example, t1 represents the objects for lib1 and
t2 represents the objects for lib2

I'd do something like:

with t1 as (select * from qtemp/objs where odlbnm = 'lib1' ),
t2 as (select * from qtemp/objs where odlbnm = 'lib2' )
select * from t1 exception join t2
on t1.odobnm = t2.odobnm
and t1.odobtp = t2.odobtp

(Of course for this particular example I'd then swap t1 and t2 in the join
to check for anthing in t2 not in t1)

But the point is, for me at least, it makes it super clear what I am doing.

regards,
Craig





On Mon, 25 Mar 2019 at 20:40, Marvin Radding <
marvin.radding@xxxxxxxxxxxxxxxxxxxxx> wrote:

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).
--
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