× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. September 2005

RE: Embeded SQL Query - Possible to make it in RPGLE.

If performance is not an issue RPGLE Rules!  If performance is an issue
SQLRPGLE Rules!  

Debugging SQL if possible must be a nightmare because same SQL runs just
fine in STRSQL but it can fail in stored procedures or in SQLRPGLE.  


Thanks


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Friday, September 09, 2005 11:27 AM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: Embeded SQL Query - Possible to make it in RPGLE.

I guess you're not being facetious, so I'll answer the question.

A 150 line RPG program is different primarily because it's (in most
cases) 150 different statements, rather than a single 150 line statement
as in your SQL example.

With the RPG program I can breakpoint the program at individual lines
and follow the execution flow step by step.  With SQL, testing is
basically "run it and determine any problems from the results".  This is
little better than the old batch report days.  Unfortunately, SQL is
literally decades behind most other languages in terms of debugging.

You can get some information from the joblog if you do STRDBG and there
are other external analysis tools which require various hoops to jump
through, but by its nature SQL is a black box over which you have very
little direct control.  Thus, I consider simple SQL statements to be a
help to development and overly complex ones to be a liability.

YMMV.

Joe


> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
> On Behalf Of rob@xxxxxxxxx
> Sent: Friday, September 09, 2005 10:25 AM
> To: RPG programming on the AS400 / iSeries
> Subject: RE: Embeded SQL Query - Possible to make it in RPGLE.
> 
> Would you feel the same way about 150 line RPGLE programs?
> 
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
> 
> 
> 
> 
> 
> "Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>
> Sent by: rpg400-l-bounces@xxxxxxxxxxxx
> 09/09/2005 09:21 AM
> Please respond to
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
> 
> 
> To
> "'RPG programming on the AS400 / iSeries'" <rpg400-l@xxxxxxxxxxxx>
> cc
> 
> Subject
> RE: Embeded SQL Query - Possible to make it in RPGLE.
> 
> 
> 
> 
> 
> 
> Rob, you did this just to make me spew coffee all over my monitor,
> right?  150-line SQL statements really need to be taken out back and
> shot... <g>
> 
> Joe
> 
> > From: rob@xxxxxxxxx
> >
> >
> > Just to blow your mind, this is some production code (and this is
not
> the
> > only cursor defined in this program!).
> >
> >      C/EXEC SQL
> >      C+ DECLARE CURS1 CURSOR FOR
> >      C*define an intermediate table to use Opening balance or Onhand
> >      C+ WITH T1 AS (
> >      C+   SELECT LPROD, LLOC, ILIL01.LWHS,
> > DLS06C+     IFNULL(
> > DLS06C+     CASE
> > DLS06C+       WHEN IFNULL(MIN(CFPLVL),0)=0
> > DLS06C+        AND (MIN(IITYP)='C' OR MIN(IITYP)='D')  THEN '7'
> > DLS06C+       WHEN IFNULL(MIN(CFPLVL),0)>0
> > DLS06C+        AND (MIN(IITYP)='C' OR MIN(IITYP)='D')  THEN '1'
> > DLS06C+       ELSE MIN(IITYP)
> > DLS06C+     END,'E') AS IITYP,
> >      C+     IFNULL(MIN(IDESC),' ') AS IDESC,
> >      C+     IFNULL(MIN(WMFAC),' ') AS WMFAC,
> >      C+     IFNULL(SUM(PHQTY),0) AS TotPhyQty,
> >      C+
> >      C+     CASE
> >      C+       WHEN :VARCALC='1' THEN
> >      C+       MAX(LADJU)+MAX(LRCT)-MAX(LISSU)+MAX(LOPB)
> >      C+       ELSE MAX(LOPB)
> >      C+     END AS OnHand,
> >      C+
> >      C+     IFNULL(MIN(CFTLVL),0) +
> >      C+     IFNULL(MIN(CFPLVL),0) AS TotStdCst,
> >      C+     1 AS ILIFlag
> >      C+
> >      C*
> >      C*     When CFPLVL=0 then the item is considered a raw for
> IITYP='C'
> > or 'D'.
> >      C*     The report breaks on this value so it is turned into an
> > indicator for sorting.
> >      C*
> >      C+   FROM ILIL01
> >      C+   LEFT OUTER JOIN IIM
> >      C+     ON LPROD=IPROD
> >      C+   LEFT OUTER JOIN IWML01
> >      C+     ON ILIL01.LWHS=IWML01.LWHS
> >      C+   LEFT OUTER JOIN CMFL01
> >      C+     ON LPROD=CFPROD
> >      C+     AND WMFAC=CFFAC
> >      C+     AND CFCSET=2
> >      C+     AND CFCBKT=0
> >      C+   LEFT OUTER JOIN IPHL01
> >      C+     ON LPROD=PHPROD
> >      C+     AND ILIL01.LWHS=PHWHSE
> >      C+     AND LLOC=PHLOC
> >      C+   WHERE ILIL01.LWHS>=:LWHSE
> >      C+     AND ILIL01.LWHS<=:UWHSE
> >      C+     AND LLOC>=:DLLOC
> >      C+     AND LLOC<=:DULOC
> >      C+     AND WMFAC=:LPLANT
> >      C+   GROUP BY LPROD,LLOC,ILIL01.LWHS
> >      C+
> >      C*
> >      C* After selecting all from ILI, select from IPH where there
> isn't
> >      C* a matching record in the ILI. UNION puts these selections
into
> >      C* the same result table so that all can be sorted together.
> This
> >      C* section is similar to the one above, but not a duplicate.
> UNION
> >      C* requires that this table have the exact same field structure
> as
> > the one
> >      C* above.
> >      C*
> >      C+ UNION ALL
> >      C+
> >      C+   SELECT PHPROD AS LPROD, PHLOC AS LLOC, PHWHSE AS LWHS,
> > DLS06C+     IFNULL(
> > DLS06C+     CASE
> > DLS06C+       WHEN IFNULL(MIN(CFPLVL),0)=0
> > DLS06C+        AND (MIN(IITYP)='C' OR MIN(IITYP)='D')  THEN '7'
> > DLS06C+       WHEN IFNULL(MIN(CFPLVL),0)>0
> > DLS06C+        AND (MIN(IITYP)='C' OR MIN(IITYP)='D')  THEN '1'
> > DLS06C+       ELSE MIN(IITYP)
> > DLS06C+     END,'E') AS IITYP,
> >      C+     IFNULL(MIN(IDESC),' ') AS IDESC,
> >      C+     IFNULL(MIN(WMFAC),' ') AS WMFAC,
> >      C+     IFNULL(SUM(PHQTY),0) AS TotPhyQty,
> >      C+     0 AS OnHand,
> >      C+
> >      C+     IFNULL(MIN(CFTLVL),0) +
> >      C+     IFNULL(MIN(CFPLVL),0) AS TotStdCst,
> >      C+     0 As ILIFlag
> >      C+
> >      C+   FROM IPHL01
> >      C+   EXCEPTION JOIN ILIL01
> >      C+     ON LPROD=PHPROD
> >      C+     AND ILIL01.LWHS=PHWHSE
> >      C+     AND LLOC=PHLOC
> >      C+   LEFT OUTER JOIN IIM
> >      C+     ON PHPROD=IPROD
> >      C+   LEFT OUTER JOIN IWML01
> >      C+     ON PHWHSE=IWML01.LWHS
> >      C+   LEFT OUTER JOIN CMFL01
> >      C+     ON PHPROD=CFPROD
> >      C+     AND WMFAC=CFFAC
> >      C+     AND CFCSET=2
> >      C+     AND CFCBKT=0
> >      C+   WHERE PHWHSE>=:LWHSE
> >      C+     AND PHWHSE<=:UWHSE
> >      C+     AND PHLOC>=:DLLOC
> >      C+     AND PHLOC<=:DULOC
> >      C+     AND WMFAC=:LPLANT
> >      C+
> >      C+   GROUP BY PHPROD, PHLOC, PHWHSE),
> >      C+
> >      C*
> >      C* End of intermediate table.   Now select the table and add
> >      C* more calculated fields.  These must be calculated in the
> >      C* SQL because sorting is done against variance values in
> >      C* some of the report types.
> >      C*
> >      C+   T2 AS (
> >      C+    SELECT
> >      C+      LPROD, LLOC, LWHS,
> >      C+      IITYP, IDESC, WMFAC,
> >      C+      ROUND(TotPhyQty,3) AS TotPhyQty,
> >      C+      ROUND(OnHand,3) As OnHand,
> >      C+      TotStdCst, ILIFlag,
> >      C+      ROUND((TotPhyQty - OnHand),3) AS VarQty,
> >      C+      ROUND((OnHand * TotStdCst),2) AS OnHandValue,
> >      C+      ROUND((TotPhyQty * TotStdCst),2) AS PhyAmt,
> >      C+      ROUND(((TotPhyQty - OnHand) * TotStdCst),2) AS VarVal
> >      C+    FROM T1)
> >      C* End of Table 2
> >      C*
> >      C* Beginning of final select where fetch will retrieve from.
> This
> >      C* step sorts the values.
> >      C*
> >      C+
> >      C+    SELECT
> >      C+      LPROD, LLOC, LWHS, IITYP, IDESC, WMFAC,
> >      C+      TotPhyQty, OnHand, TotStdCst, ILIFlag,
> >      C+      VarQty, OnHandValue, PhyAmt, VarVal
> >      C+    FROM T2
> > DLS05C+    WHERE OnHand<>0
> > DLS07C+      OR TotPhyQty<>0
> >      C* The following case statement sets up 3 different
> >      C* order by possibilities depending on the value of RptTyp.
> >      C* Different data types may not be listed within the same case
> > statement
> >      C* so the logic gets a little complicated, using NULL's to pass
> on to
> >      C* the next possible data type.
> >      C*   When RptTyp1  Order BY IITYP, LWHS, LLOC, LPROD
> >      C*   When RptTyp2  Order by IITYP, VarVal, LPROD
> >      C*   When RptTyp3  ORDER BY IITYP, VarQty, LPROD
> >      C+    order by IITYP,
> >      C+      case when :RptTyp=1 then LWHS
> >      C+      else null end,
> >      C+
> >      C+      case when :RptTyp=1 then LLOC
> >      C+      else null end,
> >      C+
> >      C+      case :RptTyp when 2 then VarVal
> >      C+                   when 3 then VarQty
> >      C+      else null end,
> >      C+
> >      C+      LPROD
> >      C+
> >      C/END-EXEC
> >      C
> >      C/EXEC SQL
> >      C+ OPEN CURS1
> >      C/END-EXEC
> 
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
> 
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.