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



I'm using an SQL scroll cursor with the code shown below.  I call the
$ResetCurs1 subroutine and read through the entire cursor 4 times because I
generate the report 4 times with 4 different headings.  I had hoped that
after the first time through the cursor that any subsequent reads would be
relatively fast.  However, in debug I find that it takes 6 seconds to fetch
the first record in the cursor every time after the 'fetch before'
statement is executed.  Do you have any idea why it might take so long to
read the first record even though the data path has already been built?  I
don't see any messages after the 'fetch before' command or before the
'fetch into' that indicates anything is being rebuilt.   I could record the
output during the first run-through into a work-file, but I'm using SQL to
avoid doing such a thing.


C*****************************************************************************
     C* Declare cursor C1 to read detail records
*

C*****************************************************************************
     C     $OpenCurs1    BegSr
     C
     C/EXEC SQL
     C+ declare C1 scroll cursor for
     C+ select PLPRD, sum(PLQTY) as PLQTY,
     C+        PLCTN, sum(PLWGT) as PLWGT,
     C+        'BULTOS' as PTyp1, 'BUNDLES' as PTyp2,
     C+        sum(PHCWT)+sum(PLWGT) as GWgt,
     C+        ifnull(min(FKENGD),' ') as EDSC,
     C+        ifnull(min(FKSPND),' ') as SDSC,
     C+        ifnull(min(F5COO),' ') as F5COO,
     C+        ifnull(min(F2TOUM),' ') as F2TOUM,
     C+        ifnull(min(HARPMT),' ') as HARPMT,
     C+        ifnull(min(F5SCST),0), ifnull(min(F2FACT),1),
     C*        multiply by duty rate percentage
     C+          ifnull(case
     C+            when :FEDATE>=min(F4COOD)
     C+              then 0
     C+            when :FEDATE<min(F4COOD)
     C+              and min(FLPROF)='Y'
     C+              then min(FLPROR)
     C+            when :FEDATE<min(F4COOD)
     C+              and min(FLPROF)<>'Y'
     C+              then min(FLNORR)
     C+            end,0)
     C+
     C+   from PCHL01 join PCLL01
     C+     on PHCTN=PLCTN
     C+   left outer join DGF4
     C+     on F4PROD=PLPRD
     C+     and F4CMP=PHCMP
     C+     and F4FAC=PHFAC
     C+   left outer join HARF
     C+     on HARCOD=F4HARM
     C+   left outer join DGF5
     C+     on F5CMP=PHCMP
     C+     and F5Fac=PHFAC
     C+     and F5PROD=PLPRD
     C+   left outer join
     C+      (select F2FRUM, min(F2TOUM) as F2TOUM, min(F2FACT) as F2FACT
     C+        from DGF2 group by F2FRUM) DGF2
     C+     on F2FRUM=F5IUM
     C+   left outer join DGFK
     C+     on FKHARM=F4SUBH
     C+     and FKFAMN=F4FAMN
     C+   left outer join DGFL
     C+     on FLHARM=F4SUBH
     C+
     C+   where PHCONS=:Cons
     C+
     C+   group by PLCTN, PLPRD
     C+ order by PLCTN, PLPRD
     C/END-EXEC
     C
     C/EXEC SQL
     C+ open C1
     C/END-EXEC
     C                   EndSr
     C

C**************************************************************************
     C* Fetch cursor C1

C**************************************************************************
     C     $FetchCurs1   BegSr
     C/EXEC SQL
     C+ fetch C1 into
     C+      :PLPRD, :PLQTY, :PLCTN,
     C+      :PLWGT, :PTyp1,
     C+      :PTyp2, :GWgt, :EDSC,
     C+      :SDSC, :F5COO, :F2TOUM,
     C+      :HARPMT, :F5SCST, :F2FACT, :DutyRate
     C/END-EXEC
     C
DLS02C*                  Eval      F5SCST*=(DutyRate/100+1)
     C
     C                   EndSr

C**************************************************************************
     C* Reset cursor C1 to beginning of data

C**************************************************************************
     C     $ResetCurs1   BegSr
     C/EXEC SQL
     C+ fetch before from C1
     C/END-EXEC
     C                   EndSr

___________________________________
Darren Strong
Programmer Analyst
Group Dekko Service LLC.
Phone 260-347-3100 ext. 70270
Fax 260-599-3215


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.