Re: SELECT stmt to count records
> I want U to give me a SELECT statement to serve this purpose :
>
> I have got a table with fields fld1, fld2, fld3, fld4, fld5
> where fld1, fld2 and fld3 forms the index. I want to find the
> absolute position of the record (not rowid) with values
> fld1=$fld1 AND fld2=$fld2 AND fld3=$fld3 in the table.
> I want this absolute position to be used with FETCH ABSOLUTE
> statement.
>
> Expecting You may be able to help me,
>
> -------------------------------------------------------------------------
> Javeed Anwar T e-mail : javi@psi.ernet.in
> PSI Data Systems Phone : (91 80) 5585726
> 19,Primrose Road
> Bangalore 560025
> INDIA
> -------------------------------------------------------------------------
The "FETCH ABSOLUTE" is used to retrieve a specific row from the
active set, where the active set is defined by a select statement
with a scroll cursor (not a sequential cursor). If a select statement
returned 50 rows, and you wanted the 40th one, you use a FETCH ABSOLUTE
to specify it.
For example:
DECLARE s_curs SCROLL CURSOR FOR
SELECT * FROM your_table
WHERE fld1=$fld1
AND fld2=$fld2
AND fld3=$fld3
Yields 50 rows. You may address the 40th one "absolutely" using:
FETCH ABSOLUTE counter_variable s_curs
INTO your_variable
(where counter_variable contains the value "40")
Theh "absolute" refers to the position of a given row with respect to
the other rows of the active set defined by the select. Rowid refers
to the address of the row in the dbspace. It, too, is an "absolute"
reference, but only with respect to all other rows *in the table*, and
without regard to the active set.
This is discussed in great detail in the "Informix Guide to SQL Tutorial"
version 7.1, December 1994, part number 000-7634, in chapter 5
(specifically on page 5-24), and listed in the index under "FETCH". RTFM.
Good luck,
__________________________________________________________________
| Clem Akins Standard Disclaimers Apply |
|Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" |
| Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com |
|________________________________________________________________|