Re: SQL explained

Frank, there are very good reasons application reasons to use a work
file.  The top reason is that you can gather the data once and then
run multiple reports from it without worrying about users making
changes to the data in the middle of your report run.  The second
reason is that you can tune your LF's (over the work file) with
MAINT(*DLY) or MAINT(*REBLD).  The third reason is that you don't have
to add a bunch of LF's used only once a month to your PF's.

The SQL script builds a work file and loads it with data summarized by
SQL.  It's a good approach and probably executes faster than RPG.

-reeve (LTL/400 BDFL)


On Tue, 29 Mar 2005 16:48:26 -0600, fkany@xxxxxxxxxxxxxxxxxx
<fkany@xxxxxxxxxxxxxxxxxx> wrote:
> 
> I'm currently developing an RPGIV report program that uses an "old school"
>  work file
> method to process a file with millions of records and it's very slow.  If I
>  choose
> to process 30 days of information, it takes an hour to spit out a report.
> 
> Rob Berendt provided the example below.  I believe it shows how to process
>  a large
> number of records using SQL.  Please correct me if that's not how it was
>  meant to
> be used.
> 
> If anyone can help explain the SQL code below, I'd appreciate it.  I've
>  posted
> questions before each line that I have questions about.  Please use any
> references to RPGIV if you think it might help me understand.  My questions
>  are
> numbered as (1),(2),(3) etc...
> 
> Thanks,
> 
> Frank
> 
> ---------------------------------------------------------------
> SLSPSN - Sales person file
>  SLSMGR    SLSPSN
>  100       100
>  100       110
>  100       120
>  100       200
>  100       300
>  700       700
> 
> CUSTMAST
> CUSTNO    SLSPSN     YTDHG
> 12345     100     10000.95
> 12346     110       575.23
> 12347     700       999.47
> 
> LINEITEM
> CUSTNO    ORDERNO   LINE#  CYLRNT
> 12345           1       1    1.95
> 12345           1       2    4.25
> ---------------------------------------------------------------
> 
> (1) What does this SQL section do? Does it create a PF named DOUGGIE in
>  library ROB?  Does it already have
> to exist?  When the program ends does it automatically get deleted when the
>  job ends?  Is the view only accessible to
> the current job?
> CREATE VIEW ROB/DOUGGIE
>  (SLSMGR, SLSPSN, CUSTNO, YTDHG, ORDERNO, LINE#, CYLRNT)
> 
> (2) What is this section doing?
> AS SELECT
>  SLSPSNF.SLSMGR, SLSPSNF.SLSPSN,
>  CUSTMAST.CUSTNO, CUSTMAST.YTDHG,
>  LINEITEM.ORDERNO, LINEITEM.LINE#, LINEITEM.CYLRNT
> 
> (3) What is "LEFT OUTER JOIN" doing?
> FROM
>  (SLSPSNF
>   LEFT OUTER JOIN CUSTMAST USING (SLSPSN))
>   LEFT OUTER JOIN LINEITEM USING (CUSTNO)
> 
> (4) Please explain this section.
> SELECT SLSMGR, SLSPSN, MAX(CUSTNO), YTDHG, SUM(CYLRNT)
> FROM ROB/DOUGGIE
> 
> (5) It's probably very obvious, but can someone explain in
> more detail the functions of GROUP and ORDER?
> GROUP BY SLSMGR, SLSPSN, YTDHG
> ORDER BY SLSMGR, SLSPSN, YTDHG DESC
> 
> SLSMGR          SLSPSN   MAX ( CUSTNO )       YTDHG   SUM ( CYLRNT )
>    100             100           12,345   10,000.95             6.20
>    100             110           12,346      575.23                 -
>    100             120                 -           -                -
>    100             200                 -           -                -
>    100             300                 -           -                -
>    700             700           12,347      999.47                 -
> 
> (6) What's the difference between a TABLE and a VIEW?
> What's "INT NOT NULL"?  Why is it in parentheses?  What's the "CONTSTRAINT"
> section of the code doing?
> 
> CREATE TABLE ROB/SLSPSNF (SLSMGR INT NOT NULL, SLSPSN INT NOT NULL,
> CONSTRAINT SLSATH_SLSPSN PRIMARY KEY (SLSPSN))
> 
> (7) What's the "REFERENCES" section of the code doing?  Is YTDHG being
> redefined as 7,2?
> 
> CREATE TABLE ROB/CUSTMAST (CUSTNO INT NOT NULL, SLSPSN INT NOT
> NULL, YTDHG DEC ( 7, 2), CONSTRAINT CUSTMAST_CUSTNO PRIMARY KEY
> (CUSTNO), CONSTRAINT CUSTMAST_SLSPSN FOREIGN KEY (SLSPSN)
> REFERENCES ROB/SLSPSNF (SLSPSN) ON DELETE NO ACTION ON UPDATE NO
> ACTION)
> 
> CREATE TABLE ROB/LINEITEM (CUSTNO INT NOT NULL, ORDERNO INT NOT
> NULL, LINE# INT NOT NULL, CYLRNT DEC (7 , 2), CONSTRAINT
> LINEITEM_PRIKEY PRIMARY KEY (ORDERNO, LINE#), CONSTRAINT
> LINEITEM_CUSTNO FOREIGN KEY (CUSTNO) REFERENCES ROB/CUSTMAST
> (CUSTNO) ON DELETE NO ACTION ON UPDATE NO ACTION)
> 
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>