|
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 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.
midrange.com
