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

It's very simple and nicely done!

 

The only problem I see with such a complex SQL that sometimes it becomes
a nightmare to maintain it for user requirements.

 

I have a question, it seems like cursors are temporary tables in QTEMP.
(May be I am wrong but if not) 

Is it possible to find out the name of the table (cursor) at design time
and use it in another cursor for join purpose?  

 

Thanks

 

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

 

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