RE: Free format vs SQL -- RPG400-L

Yes you can do a free format exsr.

And while you can jump in/out of free it can look messy.  For example, how
many would have bothered with /free in this sample?



     H/DEFINE HSpec
     H/COPY ROUTINES/QRPGLESRC,HSPEC1
     H/COPY ROUTINES/QRPGLESRC,HSPEC2
     H/UNDEFINE HSpec

     F/TITLE EDG306A HMI copy VD files to PL files
     F*****************************************************************
     F* Copyright   Group Dekko Services, LLC     2001
     F*****************************************************************
     F*
     F*  Program ID          -    EDG306A
     F*  Application ID      -    EDG306A
     F*  Application Name    -    HMI copy VD files to PL files
     F*  Date Generated      -    Aug 29, 1901
     F*
     F*****************************************************************
     F*  Program Narrative
     F*
     F*  (name removed) Group Dekko International, Inc.
     F*
     F*  This program was created with ASSET in Application set name
     F*  EDIMFGCD.
     F*
     F*  Purpose:  Copy VD* records to PL* records then delete VD* records.
     F*
     F*  Modifications:
     F*
     F*  MG001 - 10-24-00  Correct SQL insert statement for PLHEAD.  When
the
     F*                    program was ran an error was log as 'Duplicate
     F*                    record key in member PLHEAD.'  This was do to
the
     F*                    fact that PO# 00708 now went to 2 separate plant
and
     F*                    the SQL statement didn't allow for this.
     F*
      * 09/05/01 by R.Berendt, CCP    Group Dekko Services, LLC
      *          Rewrote in complete ILE

     F*****************************************************************
     F*  Function Of Subroutines
     F*
     F* SFILE - process files

     D EDG306ARB       PR
     D  RtParm                        1A
     D  Plant                        10A

     D EDG306ARB       PI
     D  RtParm                        1A
     D  Plant                        10A

     D vdlineds      E DS                  extname(vdline)

     D Plant7          S                   like(hlff1)
     D Count           S              5P 0

     C/FREE

       Plant7=Plant;
       VdlPo=*blank;

      /END-FREE

     C/EXEC SQL
     C+ declare V1 cursor for
     C+  select vdlpo
     C+         from vdline where hlff1 = :Plant7
     C+         group by vdlpo
     C/END-EXEC

     C/EXEC SQL
     C+ open V1
     C/END-EXEC

     C/EXEC SQL
     C+ fetch V1 into :VDLPO
     C/END-EXEC

     C/FREE
       dow sqlcod=*zeros;

       count=*zeros;
      /END-FREE

     C/EXEC SQL
     C+ select count(*) into :COUNT from plline where pdlpo=:VDLPO
     C/END-EXEC

     C/FREE
       if count=0;
       exsr sfile;
       EndIf;
      /END-FREE

     C/EXEC SQL
     C+ fetch V1 into :VDLPO
     C/END-EXEC

     C/FREE
       EndDo;
      /END-FREE

     C/EXEC SQL
     C+ close V1
     C/END-EXEC

     C/FREE
       *inlr=*on;
       return;

      /END-FREE

      /EJECT
     C*****************************************************************
     C* SFILE - process files
     C*****************************************************************
     C     SFILE         BEGSR

     C/EXEC SQL
     C+ insert into plline
     C+   (select * from vdline where hlff1 = :PLANT7
     C+                         and   vdlpo = :VDLPO)
     C/END-EXEC

     C*EXEC SQL
     C* insert into plline2
     C*   (select * from vdline2
     C*    where (vdlpo concat h2item)
     C*          in (select (pdlpo concat plitem) from plline))
     C*END-EXEC

     C*EXEC SQL
     C* insert into plline2
     C*  (select * from vdline2 a
     C*    where exists (select * from plline b
     C*          where a.vdlpo = b.pdlpo and a.h2item = b.plitem))
     C*END-EXEC

     C/EXEC SQL
     C+ insert into plline2
     C+   (select * from vdline2
     C+    where (vdlpo concat h2item)
     C+    in (SELECT  a.vdlpo  concat  a.h2item
     C+    FROM  vdline2  a  INNER JOIN  plline  b
     C+    ON  a.vdlpo  concat  a.h2item  =  b.pdlpo  concat  b.plitem))
     C/END-EXEC

     C/EXEC SQL
     C+ insert into plhead
     C+   (select * from vdhead
     C+       where vdhpo in (select pdlpo from plline)
     C+         and vdhpo=:VDLPO)
     C/END-EXEC

     C/EXEC SQL
     C+ delete from vdline
     C+   where hlff1 = :PLANT7 and vdlpo=:VDLPO
     C/END-EXEC

     C/EXEC SQL
     C+ delete from vdline2
     C+   where (vdlpo concat h2item) not
     C+         in (select (vdlpo concat hlitem) from vdline)
     C/END-EXEC

     C/EXEC SQL
     C+ delete from vdhead
     C+   where vdhpo not in (select vdlpo from vdline)
     C/END-EXEC

     C     $SFILE        ENDSR

Rob Berendt

==================
A smart person learns from their mistakes,
but a wise person learns from OTHER peoples mistakes.



                    "Phil"
                    <sublime78ska@yah       To:     <rpg400-l@midrange.com>
                    oo.com>                 cc:
                    Sent by:                Fax to:
                    rpg400-l-admin@mi       Subject:     RE: Free format vs SQL
                    drange.com


                    09/06/2001 11:25
                    AM
                    Please respond to
                    rpg400-l






Rob,

I was responding to Aaron's post about how he likes how java does it.  I
don't see much of a difference.

I put all my embedded sql into subroutines (almost always - there are
exceptions).  Therefore, using /FREE should have no impact at all (unless
you can't EXSR in a /FREE block - I'm still at V4R5).  I showed that with
the example I posted.

IMHO:
I don't think the /EXEC SQL directive needs to be allowed within a /FREE
directive.  What's the difference between:

 /FREE
 /ENDFREE
 /EXEC SQL
 /END-EXEC

and

 /FREE
 /EXEC SQL
 /END-EXEC
 /ENDFREE

If you're already using embedded sql you're more than used to needing an
/EXEC SQL block for each sql statement anyway.  (Also, I believe not all
opcodes can be used within a /FREE block anyway?  Not sure.)

If some programmers won't use /FREE because they can't nest /EXEC SQL in it
then I think they're just looking for an excuse.  If /FREE has merit this
shouldn't get in the way.

Phil





> -----Original Message-----
> From: rpg400-l-admin@midrange.com [mailto:rpg400-l-admin@midrange.com]On
> Behalf Of rob@dekko.com
> Sent: Thursday, September 06, 2001 11:51 AM
> To: rpg400-l@midrange.com
> Subject: RE: Free format vs SQL
>
>
>
> Uh, Phil,
>
> What problem are you trying to solve?  Maybe I lost the thread here, but
I
> though we were working on why we needed SQL to be free formatted, or, how
> to work with it in the short term.  Meaning, as potential workarounds,
how
> to keep your code free format and stick the sql into a subroutine or
> subprocedure.
>
> Rob Berendt
>


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.