Re: Constructed Select and Row

On Thu, 29 Jul 1993, Stuart Hemming wrote: > > In-Reply-To: <duhonCAu35B.MFt@netcom.com> duhon@netcom.com (Joey Duhon) > > TITLE: Constructed Select and Row Count > > In <duhonCAu35B.MFt@netcom.com> Joey Duhon asks: > > > I'm doing a select with a "construct" (using a cursor) in 4GL. > I was hoping that after opening (or at least after the 1st fetch) > > sqlerrd[3] would have the wor count. I can't seem to get this value > > anyway i try. > > Is there a trick to getting this value, or should i give up? > > The only real solution is to: > > a. Create a SELECT COUNT(*) statement from your CONSTRUCT query and > use that result, then SELECT <whatever> as you normally would. > > b. Create your query, DECLARE a cursor, run a FOREACH loop with > that cursor incrementing a counter for each row, then at the end > use the value of the counter. You can then do what ever it is > you were going to do in the first place. > > As you can see there is no real way of achieving what you want > without effectively doing 2 SELECTs. > > ***************************************************************************** > * Stuart Hemming, Tudor Labels Ltd, Roman Bank, Bourne, Lincs, PE10 9LQ. UK * > * shemminga@cix.compulink.co.uk uunet!cix.compulink.co.uk!shemminga * > * Tel : (+44) 778 426444 Fax : (+44) 778 421862 * > ******************** PGP 2.2 sig available on request *********************** The SELECT COUNT(*) will work if you know what to count before hand. If you are using a construct and you want to use the "where clause" you'll have to user the PREPARE statement. The problem is that you have to use the SELECT INTO to retreive the count into your variable. You cannot PREPARE a select with the INTO option. What is left is to declare cursor and loop to the end with a counter. The other option is the prepare a declare cursor with a SELECT COUNT(*). Here's an example: LET get_count = "SELECT COUNT(*) FROM table_name WHERE ", where_var CLIPPED PREPARE ex_count FROM get_count DECLARE the_count CURSOR FOR ex_count OPEN the_count FETCH the_count INTO vtotal_count CLOSE the_count The sad part of this is although it's less code (I think), based on my tests this method is no fasted then fetching through a loop with a counter. Hope it helps Regards, Peter +---------------------------+--------------------------------------------+ | Peter Estabrook | Internet: estabroo@sea07s.navsea.navy.mil | | User Technology Assoc. | Voice: (703) 486-7190 | | 2121 Crystal Drive #103 | Fax : (703) 486-7179 | | Arlington, VA 22202 USA | Host : Sequent S2000/200 ptx V1.4 | +---------------------------+--------------------------------------------+