× 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.



GEEZ!! That reads like a horror novel LOL 


Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Friday, September 09, 2005 8:41 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Fw: Embeded SQL Query - Possible to make it in RPGLE.

Sriram,

Again, it will take a lot more understanding of the business process
involved.  We really don't know what the end result desired is.

There are several "shoot from the hip" suggestions available, but,
again, it ties into what you are trying to get.

There are several good books available from:
http://store.midrange.com/
I like SQL/400 Developer's Guide by Paul Conte and Mike Cravitz The last
few releases of OS/400 have had many enhancements so it's tough for a
book to keep up though.


Here's a couple shots:

Select field1, count(field1), sum(field2)
>From xyz
Group by field1

or another shot

Select F1.field1, F2.text, count(F1.field1), sum(F1.field2)
>From xyz F1 Join abc F2 on F1.Field1=F2.Field1
Group by F1.field1

Then there's the whole temporary table concept available with the 'with'

clause
With T1 as (
    Select F1.field1 as T1Field1,
           F2.text as T1Text,
           count(F1.field1) as T1Count,
           sum(F1.field2) as T1Total
    From xyz F1 Join abc F2 on F1.Field1=F2.Field1
    Group by F1.field1)
Select T1Field1,T1Text, T1Count, T1Total
>From T1
Order by T1Field1

You can even join T1 to other tables.

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



Rob Berendt

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

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.