Having trouble declaring/fetching in Pro*Cobol
in [Cobol]
|
From: Binyamin Dissen on 11 Mar 2010 08:45 On Thu, 11 Mar 2010 04:18:48 -0800 (PST) jmoore <jmoore207(a)gmail.com> wrote: :>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. :>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. That don't matter. You can use the FOR :host ROWS clause if your database supports it. -- 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 08:54 On Mar 11, 8:45 am, Binyamin Dissen <postin...(a)dissensoftware.com> wrote: > On Thu, 11 Mar 2010 04:18:48 -0800 (PST) jmoore <jmoore...(a)gmail.com> wrote: > > :>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. > > :>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. > > That don't matter. > > You can use the > > FOR :host ROWS > > clause if your database supports it. > > -- > 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. Oracle database
From: Pete Dashwood on 11 Mar 2010 17:54 jmoore wrote: > 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. Here is ONE way to do it (It isn't the ONLY way and may not even be the BEST way, but it is a good way for Embedded SQL) declare host variables (You only need each variable defined once without OCCURS on it.) declare cursor move zero to SQLSTATE set stored array index/subscript to 1 Perform until SQLSTATE is not = zero exec SQL fetch...into the defined host variables end-exec move host variables fetched into to the sorage array or whatever increment the storage array index/subscript end-perform The storage array index/subscript tells you how many there were. ALTERNATIVELY: declare host variables with OCCURS 4000 DON'T declare a cursor. exec SQL SELECT blah blah INTO (occurring host variables without subscript on their names) end-exec Check SQLSTATE/SQLCODE (I prefer SQLSTATE; it tells me more...) SQLERRD(3) contains the number of rows loaded to HV array. If you find this helpful, please return the favour and go and visit http://primacomputing.co.nz/cobol21 (Seeing the visit count going up makes my day :-)) Cheers, Pete. -- "I used to write COBOL...now I can do anything."
From: jmoore on 12 Mar 2010 11:09 On Mar 11, 5:54 pm, "Pete Dashwood" <dashw...(a)removethis.enternet.co.nz> wrote: > jmoore wrote: > > 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. > > Here is ONE way to do it (It isn't the ONLY way and may not even be the BEST > way, but it is a good way for Embedded SQL) > > declare host variables (You only need each variable defined once without > OCCURS on it.) > declare cursor > > move zero to SQLSTATE > set stored array index/subscript to 1 > > Perform until SQLSTATE is not = zero > exec SQL > fetch...into the defined host variables > end-exec > move host variables fetched into to the sorage array or whatever > increment the storage array index/subscript > end-perform > > The storage array index/subscript tells you how many there were. > > ALTERNATIVELY: > > declare host variables with OCCURS 4000 > > DON'T declare a cursor. > > exec SQL > SELECT blah blah INTO (occurring host variables without subscript on > their names) > end-exec > > Check SQLSTATE/SQLCODE (I prefer SQLSTATE; it tells me more...) > > SQLERRD(3) contains the number of rows loaded to HV array. > > If you find this helpful, please return the favour and go and visithttp://primacomputing.co.nz/cobol21 > > (Seeing the visit count going up makes my day :-)) > > Cheers, > > Pete. > -- > "I used to write COBOL...now I can do anything."- Hide quoted text - > > - Show quoted text - Thanks everyone for your responses, I decided to handle it a different way. update mbrsepmstr set code4='1000' where substr(mbrsep,1,8) in (select substr(mbrsep, 1,8) from mbrsepmstr group by substr(mbrsep, 1,8) having count(*) > 01)
First
|
Prev
|
Pages: 1 2 Prev: ACE - the ghost of COBOL past... Next: moving NULL value to SQL table |