Having trouble declaring/fetching in Pro*Cobol
in [Cobol]
|
From: jmoore on 10 Mar 2010 19:17 If I run the query in sql developer it works fine, but when I declare it as a cursor it is not retrieving but 1 record. SELECT SubStr(mbrsep,1,8), Count(*) FROM mbrsepmstr GROUP BY SubStr(mbrsep,1,8) HAVING Count(*) > 01 Returns 1915 rows. Below is the code. If anyone can help I would really appreciate it! 4000-SECOND-PASS. PERFORM 4000-DECLARE-CURSOR THRU 4000-DECLARE-CURSOR-EXIT. PERFORM 4000-FETCH-CURSOR THRU 4000-FETCH-CURSOR-EXIT. MOVE SQLERRD(3) to SQL-ARG-CNT. IF SQL-ARG-CNT = 0 GO TO 4000-SECOND-PASS-EXIT. DISPLAY "SQL " SQL-ARG-CNT PERFORM VARYING DTL-SUB FROM 1 BY 1 UNTIL DTL-SUB > sql-arg-cnt MOVE D-SQL-ARG-NO(dtl-sub) to SQL-ARG-NO PERFORM 4000-UPDATE-MBRSEPMSTR THRU 4000-UPDATE-MBRSEPMSTR-EXIT IF UPD-ERR-RESULT = 1 GO TO 4000-SECOND-PASS-EXIT END-IF END-PERFORM. 4000-SECOND-PASS-EXIT. EXIT. 4000-DECLARE-CURSOR. MOVE ZEROES TO LCOUNT. EXEC SQL DECLARE MBR_C CURSOR FOR SELECT SubStr(mbrsep,1,8), Count(*) as COUNT FROM mbrsepmstr GROUP BY SubStr(mbrsep,1,8) HAVING Count(*) > 01 END-EXEC. EXEC SQL OPEN MBR_C END-EXEC. IF (SQLCODE NOT = 0) DISPLAY "ERR IN 4000-DECLARE-CURSOR" PERFORM SQL-ERROR-PARA THRU SQL-ERROR-PARA-EXIT END-IF. 4000-DECLARE-CURSOR-EXIT. 4000-FETCH-CURSOR. EXEC SQL FETCH MBR_C INTO :D-SQL-ARG-NO ,:D-SQL-ARG-CouNT end-exec. IF SQLCODE <> 0 AND 1403 DISPLAY "ERR IN 4000-FETCH-CURSOR" PERFORM SQL-ERROR-PARA THRU SQL-ERROR-PARA-EXIT END-IF. 4000-FETCH-CURSOR-EXIT. EXIT. 4000-UPDATE-MBRSEPMSTR. exec sql update mbrsepmstr set CODE4 = '1000' where SubStr(mbrsep,1,8) = :SQL-ARG-NO end-exec. IF SQLCODE <> 0 AND 1403 DISPLAY "ERR IN 4000-UPDATE-MBRSEPMSTR" PERFORM SQL-ERROR-PARA THRU SQL-ERROR-PARA-EXIT END-IF.
From: dashwood on 10 Mar 2010 22:26 On Mar 11, 1:17 pm, jmoore <jmoore...(a)gmail.com> wrote: > If I run the query in sql developer it works fine, but when I declare > it as a cursor it is not retrieving but 1 record. > > SELECT SubStr(mbrsep,1,8), Count(*) > FROM mbrsepmstr > GROUP BY SubStr(mbrsep,1,8) > HAVING Count(*) > 01 > > Returns 1915 rows. Below is the code. If anyone can help I would > really appreciate it! It can only retrieve 1 row because the host variables have not been defined with OCCURS on them. SQLERRD(3) will have a count of the number of rows actually loaded by the FETCH. (Or the same for a SELECT...) If there is no OCCURS ON the Host Variables, it can only ever load 1 row. If you want more rows, keep fetching (with a loop) until you get a non- zero SQLCODE (taking care to save the host variables into something else on each FETCH, because they will obviously be overwritten by the next FETCH), OR, don't use a CURSOR and simply SELECT ...INTO an array of Host Variables. (SQLERRD(3) will tell you how many it loaded). HTH, Pete. <snipped>
From: Binyamin Dissen on 11 Mar 2010 04:33 On Wed, 10 Mar 2010 16:17:04 -0800 (PST) jmoore <jmoore207(a)gmail.com> wrote: :>If I run the query in sql developer it works fine, but when I declare :>it as a cursor it is not retrieving but 1 record. :> SELECT SubStr(mbrsep,1,8), Count(*) :> FROM mbrsepmstr :> GROUP BY SubStr(mbrsep,1,8) :> HAVING Count(*) > 01 :>Returns 1915 rows. Below is the code. If anyone can help I would :>really appreciate it! You only fetched one row. If you want more rows, FETCH more rows. -- Binyamin Dissen <bdissen(a)dissensoftware.com> http://www.dissensoftware.com Director, Dissen Software, Bar & Grill - Israel Should you use the mailblocks package and expect a response from me, you should preauthorize the dissensoftware.com domain. I very rarely bother responding to challenge/response systems, especially those from irresponsible companies.
From: jmoore on 11 Mar 2010 07:18 On Mar 11, 4:33 am, Binyamin Dissen <postin...(a)dissensoftware.com> wrote: > On Wed, 10 Mar 2010 16:17:04 -0800 (PST) jmoore <jmoore...(a)gmail.com> wrote: > > :>If I run the query in sql developer it works fine, but when I declare > :>it as a cursor it is not retrieving but 1 record. > > :> SELECT SubStr(mbrsep,1,8), Count(*) > :> FROM mbrsepmstr > :> GROUP BY SubStr(mbrsep,1,8) > :> HAVING Count(*) > 01 > > :>Returns 1915 rows. Below is the code. If anyone can help I would > :>really appreciate it! > > You only fetched one row. If you want more rows, FETCH more rows. > > -- > Binyamin Dissen <bdis...(a)dissensoftware.com>http://www.dissensoftware.com > > Director, Dissen Software, Bar & Grill - Israel > > Should you use the mailblocks package and expect a response from me, > you should preauthorize the dissensoftware.com domain. > > I very rarely bother responding to challenge/response systems, > especially those from irresponsible companies. My fetch is going into variables that occur 4000 times. 000000 01 D-SQL-ARG-COUNT PIC S9(04) OCCURS 4000 times. 000000 01 D-SQL-ARG-NO pic 9(08) OCCURS 4000 times.
From: jmoore on 11 Mar 2010 07:20
On Mar 10, 10:26 pm, dashw...(a)enternet.co.nz wrote: > On Mar 11, 1:17 pm, jmoore <jmoore...(a)gmail.com> wrote: > > > If I run the query in sql developer it works fine, but when I declare > > it as a cursor it is not retrieving but 1 record. > > > SELECT SubStr(mbrsep,1,8), Count(*) > > FROM mbrsepmstr > > GROUP BY SubStr(mbrsep,1,8) > > HAVING Count(*) > 01 > > > Returns 1915 rows. Below is the code. If anyone can help I would > > really appreciate it! > > It can only retrieve 1 row because the host variables have not been > defined with OCCURS on them. > > SQLERRD(3) will have a count of the number of rows actually loaded by > the FETCH. (Or the same for a SELECT...) > > If there is no OCCURS ON the Host Variables, it can only ever load 1 > row. > > If you want more rows, keep fetching (with a loop) until you get a non- > zero SQLCODE (taking care to save the host variables into something > else on each FETCH, because they will obviously be overwritten by the > next FETCH), OR, don't use a CURSOR and simply SELECT ...INTO an array > of Host Variables. (SQLERRD(3) will tell you how many it loaded). > > HTH, > > Pete. > <snipped> Can you give me some pseudocode for the example I gave. I am fetching into 2 variables for count and mbrsep that occurs 4000 times. |