Re: RPGLESQL interim processing file

[Craig Richards <craig@xxxxxxxxxxxxxxxx>]

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