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

Uhm...  Wow...  if that's appropriate.
I need a break now just after reading that.  I can't imagine coding it. 
Nice work tho....

Ron Power
Programmer
Information Services
City Of St. John's, NL
P.O. Box 908
St. John's, NL
A1C 5M2
709-576-8132
rpower@xxxxxxxxxx
http://www.stjohns.ca/
___________________________________________________________________________
Success is going from failure to failure without a loss of enthusiasm. - 
Sir Winston Churchill




"Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
2005/09/09 11:51 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