Re: Capturing counts on an UPDATE stmt...

[Lydia Verhoef <lverhoef@PCC.EDU>]

Ray --

The #sql-count variable works great.  Thank-you for pointing me in the
right direction.

Lydia


On Sat, 3 May 1997, Ray Ontko wrote:

> > I am in need of capturing update counts within an SQR report, but am not
> > sure quite how to do it.  For example if I issue the following statement:
> >
> >      BEGIN-SQL
> >      UPDATE ssbsect
> >      SET    ssbsect_seats_avail =  (ssbsect_max_enrl - ssbsect_enrl)
> >      WHERE  ssbsect_term_code   =  $PARM_term
> >      AND    ssbsect_seats_avail <> (ssbsect_max_enrl - ssbsect_enrl)
> >      END-SQL
> >
> > how can I capture the number of records it updated?  The only two ways
> > I can think of are:
> >
> >    a) Add a pre-count using the same where clause.  The drawback
> >       with this is performance, especially where there are a lot
> >       of separate updates.
> >
> >    b) Use the -S option at runtime to get row counts.  This option
> >       has the drawback of not being very readable from a user
> >       standpoint and is stored in a separate output file.
> >
> > Are there any other alternatives I have overlooked?
>
> Lydia,
>
> Have you looked at #sql-count?  There is a SQR pre-defined
> variable that contains the number of records updated, inserted,
> or deleted by the last begin-sql block.  I think this is on
> page 11 in the V3 manual.
>
> Note: If you're using Sybase, you may need to use -xp on the
> begin-sql statement or on the command line.
>
> Good luck.
>
> Ray
>
> ----------------------------------------------------------------------
> Ray Ontko       |  Ray Ontko & Co  |  "Ask me about our SQR toolkit."
> rayo@ontko.com  |  Richmond, In    |  See us at http://www.ontko.com/
>

--------------------------------------------------------------------------
Lydia Verhoef
Programmer/Analyst                                        lverhoef@pcc.edu
Portland Community College                                  (503) 977-4750
Information Technology Services                     http://www.pcc.edu/its
--------------------------------------------------------------------------