Re: SELECTing first x results (a FAQ?)
Adam Szczerbicki wrote:
>
> D. Sanderson wrote:
>
> > I want an SELECT statement that returns the first x results (x being a
> > constant, say, 50) matching certain criteria. (I want an exact number, so
> > I can't use the 'fraction of' formula in the FAQ.)
> >
> > I don't want a broad user-specified search to tie up the port getting
> > hundreds of
> > results.
>
> On server side You can use temporary table with serial field.
>
> CREATE TEMP TABLE the_temp_table(id SERIAL, the_data CHAR(x))
> INSERT INTO the_temp_table SELECT "0", needed_value FROM some_table WHERE> some_condition
> DELETE FROM the_temp_table WHERE id < (50 + 1)
> INSERT INTO CLIENT (-:)) SELECT the_data FROM the_temp_table>
> Actually You get all records that match Your condition, but You store them in
> temporary table and get only first 50.
> Instead of storing values in temp table You can store some ids of ther
> records.
>
> In ORACLE ther is some counter for being getted record.
> For example:
> CREATE COUNTER rec_counter_STARTED WITH 1 #not real example, not real
> syntax
> SELECT rec_counter, some_data FROM some_table WHERE some_condition AND
> rec_counter < (50+1)>
> --
> #ifdef LANGUAGE=POLISH | Unix Adm & Dev, Informix Adm & Dev
> #include <wyrazy_szacunku.h> | Pure HTML Coder, Win32 API Dev
> #else
> #include <greetings.h>
> #endif
You can create your own counter for this in SPL. I have just been
mucking about with this today to solve a slightly different problem but
an adaptation of my solution may work for you.
This is a work in progress, please test it first!
--- cut here ---
-- %W% iterator to produce sequences of numbers
drop procedure init_iterator;
create procedure init_iterator(
start integer
)
define global g_iterator_start, g_iterator_i integer default 0; define global g_iterator1, g_iterator2, g_iterator3, g_iterator4
char(32) default " ";
let g_iterator_start = start;
let g_iterator_i = start;
let g_iterator1 = " ";
let g_iterator2 = " ";
let g_iterator3 = " ";
let g_iterator4 = " ";
end procedure
document
'init_iterator() sets global variables for procedure iterator() to
spaces.',
'Arg1: iterator starting value, integer'
with listing in 'init_iterator.err';
grant execute on init_iterator to public;
drop procedure iterator;
create procedure iterator(
step integer,
a1 char(32),
a2 char(32),
a3 char(32),
a4 char(32)
)
returning integer; define global g_iterator_start, g_iterator_i integer default 0;
define global g_iterator1, g_iterator2, g_iterator3, g_iterator4
char(32) default " ";
if ((a1 = g_iterator1)
and (a2 = g_iterator2)
and (a3 = g_iterator3)
and (a4 = g_iterator4)) then
let g_iterator_i = g_iterator_i + step;
else
let g_iterator_i = g_iterator_start;
let g_iterator1 = a1;
let g_iterator2 = a2;
let g_iterator3 = a3;
let g_iterator4 = a4;
end if
return g_iterator_i;
end procedure
document
'iterator() returns an integer increased by "step" if any of its
arguments',
'have changed since the previous call.',
'Arg1: step value, integer',
'Arg2-5: arguments tested for change, char(32)',
'Procedure init_iterator() should be called first.',
'WARNING: ORDER BY is carried out AFTER iterator() is called.',
'If ORDERing of results is required, use SELECT ... ORDER BY ... INTO
TEMP',
'and then SELECT ..., iterator() FROM TEMP ...;'
with listing in 'iterator.err';
grant execute on iterator to public;--- cut here ---
--
Peter Lancashire
Information Systems Specialist, Bayer plc
Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK
Tel: +44-1635-562258, Fax: +44-1635-562281
---
If all else fails, read the instructions AND the release notes.
All opinions are my own and not those of Bayer plc.
My Internet plumbing does not allow me to mail and post news together.
Sorry.
---
Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/