Board index » delphi » shrink the select

Eitan Mizrah

Delphi Developer
Sat, 13 Mar 2004 06:00:59 GMT

shrink the select

Hello,

Is there any method for shrinking any select  statment,
  so the result will be only the first 5 rows , or only the first 3 rows
etc...

I need a code sample.

Thanks :o)

 
Phil Shrimpto

Delphi Developer
Fri, 12 Mar 2004 18:15:21 GMT

Re:shrink the select


Quote
In article <3baf0445_1@dnews>, eit...@e-ntouch.com wrote...

Hi,

Quote
> Is there any method for shrinking any select  statment,
>   so the result will be only the first 5 rows , or only the first 3 rows
> etc...

With Firebird use LIMIT and SKIP.

With Interbase, you will need to use a stored procedure that exits after
X records. Something like

CREATE PROCEDURE PRC$MYPROC(NumRecs INTEGER)
RETURNS (FIELD1 VARCHAR)
AS
DECLARE VARIABLE MyCounter INTEGER;
BEGIN
  COUNTER = 0;
  FOR
  SELECT FIELD1
  FROM MYTABLE
  INTO :FIELD1
  DO
    BEGIN
      MyCounter = :MyCounter + 1;
      SUSPEND;
      IF (MyCounter = :NumRecs) THEN EXIT;
    END
END

Phil

Sergey Dovganu

Delphi Developer
Fri, 12 Mar 2004 18:18:21 GMT

Re:shrink the select


Hi,
only using SP:

set term ^;
create procedure (cnt integer)
returns (
..... // the list of declarations of the fields to be returned,
separated by ','
)
as
begin
   for select ....
      from ....
      into .....
   do begin
      cnt = cnt - 1;
      if (cnt < 0) then exit;
      suspend;
   end
end^
set term ;^
--
I am looking for a job in EU.
I have no a work permit.

Sergey Dovganu

Delphi Developer
Fri, 12 Mar 2004 18:20:34 GMT

Re:shrink the select


Oops, sorry,
I didn't see your answer.
With best regards,
Sergey.

--
I am looking for a job in EU.
I have no a work permit.

Jan Henrik Sylveste

Delphi Developer
Fri, 12 Mar 2004 19:49:56 GMT

Re:shrink the select


Please, correct your system time.

You're ahead of us -- at least not a few years or so, thus your posting is
only the first one for a day... ;-)

Cheers,
Jan Henrik Sylvester <m...@janh.de>

Eitan Mizrah

Delphi Developer
Fri, 12 Mar 2004 20:17:50 GMT

Re:shrink the select


I used Oracle before,
In Oracle there is a simple way to do so :

Select ... from ...
  where rownum <= 30

Is there any simplier way to do select of 5 records like oracle does ?
(I need it for speeding up the query, and I don't think that fetching the
query will speed up my query).
Thanks :o)

Quote
"Sergey Dovganuk" <dovganuk_ser...@mail.ru> wrote in message

news:3BAF086D.D71B5D3C@mail.ru...
Quote
> Hi,
> only using SP:

> set term ^;
> create procedure (cnt integer)
> returns (
> ..... // the list of declarations of the fields to be returned,
> separated by ','
> )
> as
> begin
>    for select ....
>       from ....
>       into .....
>    do begin
>       cnt = cnt - 1;
>       if (cnt < 0) then exit;
>       suspend;
>    end
> end^
> set term ;^
> --
> I am looking for a job in EU.
> I have no a work permit.

Martijn Tonie

Delphi Developer
Fri, 12 Mar 2004 20:40:00 GMT

Re:shrink the select


"Eitan Mizrahi" <eit...@e-ntouch.com> schreef in bericht
news:3baf246d_2@dnews...

Quote
> I used Oracle before,
> In Oracle there is a simple way to do so :

> Select ... from ...
>   where rownum <= 30

> Is there any simplier way to do select of 5 records like oracle does ?

No.

Quote
> (I need it for speeding up the query, and I don't think that fetching the
> query will speed up my query).

That doesn't really matter - if you open the query (from your programming
language) it won't fetch any records yet, but will execute the query: the
resultset is readied for you at the server - if you fetch 5 records and
close it, you still fast enough...

--
Martijn Tonies
Upscene Productions

InterBase Workbench - The Developer Tool for InterBase
http://www.interbaseworkbench.com

Phil Shrimpto

Delphi Developer
Fri, 12 Mar 2004 21:17:34 GMT

Re:shrink the select


Quote
In article <3baf31ea_2@dnews>, eit...@e-ntouch.com wrote...

Hi,

Quote
> Sorry of the newbie question,
>   but I tried both yours code and Phil code, and it doesn't compiled !
> -- line 2, char 22 is "varchar" (the line was : returns (field1 varchar),

Have you stated the varchars length ? e.g field1 varchar(20)

Phil

Sean Leyn

Delphi Developer
Fri, 12 Mar 2004 21:29:03 GMT

Re:shrink the select


Martijn,

Quote
"Martijn Tonies" <m.ton...@upscene.com> wrote in message

news:3baf2917$1_1@dnews...

Quote

> "Eitan Mizrahi" <eit...@e-ntouch.com> schreef in bericht
> news:3baf246d_2@dnews...
> > I used Oracle before,
> > In Oracle there is a simple way to do so :

> > Select ... from ...
> >   where rownum <= 30

> > Is there any simplier way to do select of 5 records like oracle does ?

> No.

Actually, not completely true -- the original criteria is equivalent to the
new SELECT FIRST X syntax of the Firebird.  So there is a simple way to do
it.

Sean

Eitan Mizrah

Delphi Developer
Fri, 12 Mar 2004 21:15:24 GMT

Re:shrink the select


Thanks,

Sorry of the newbie question,
  but I tried both yours code and Phil code, and it doesn't compiled !
(
Dynamic SQL Error
SQL error code = -104
Token unknown - line 2, char 22

-- line 2, char 22 is "varchar" (the line was : returns (field1 varchar),
I've done some changes on the code,
but I need more accurate sample.
)

Quote
"Sergey Dovganuk" <dovganuk_ser...@mail.ru> wrote in message

news:3BAF086D.D71B5D3C@mail.ru...
Quote
> Hi,
> only using SP:

> set term ^;
> create procedure (cnt integer)
> returns (
> ..... // the list of declarations of the fields to be returned,
> separated by ','
> )
> as
> begin
>    for select ....
>       from ....
>       into .....
>    do begin
>       cnt = cnt - 1;
>       if (cnt < 0) then exit;
>       suspend;
>    end
> end^
> set term ;^
> --
> I am looking for a job in EU.
> I have no a work permit.

Martijn Tonie

Delphi Developer
Fri, 12 Mar 2004 22:39:13 GMT

Re:shrink the select


"Sean Leyne" <sle...@atkin.com> schreef in bericht
news:3baf352d$1_1@dnews...

Quote
> Martijn,

> "Martijn Tonies" <m.ton...@upscene.com> wrote in message
> news:3baf2917$1_1@dnews...

> > "Eitan Mizrahi" <eit...@e-ntouch.com> schreef in bericht
> > news:3baf246d_2@dnews...
> > > I used Oracle before,
> > > In Oracle there is a simple way to do so :

> > > Select ... from ...
> > >   where rownum <= 30

> > > Is there any simplier way to do select of 5 records like oracle does ?

> > No.

> Actually, not completely true -- the original criteria is equivalent to
the
> new SELECT FIRST X syntax of the Firebird.  So there is a simple way to do
> it.

True - the Firebird answer was given in another reply - mine answer was
about InterBase ( I do that too some times :))

--
Martijn Tonies
Upscene Productions

InterBase Workbench - The Developer Tool for InterBase
http://www.interbaseworkbench.com

- Show quoted text -

Quote

> Sean

Sergio Samayo

Delphi Developer
Fri, 12 Mar 2004 22:25:29 GMT

Re:shrink the select


Be aware that SKIP & LIMIT are Firebird specific.

--
Sergio Samayoa
Lgica Software
http://www.geocities.com/logicasw/

"Sean Leyne" <sle...@atkin.com> escribi en el mensaje
news:3baf352d$1_1@dnews...

Quote
> Martijn,

> "Martijn Tonies" <m.ton...@upscene.com> wrote in message
> news:3baf2917$1_1@dnews...

> > "Eitan Mizrahi" <eit...@e-ntouch.com> schreef in bericht
> > news:3baf246d_2@dnews...
> > > I used Oracle before,
> > > In Oracle there is a simple way to do so :

> > > Select ... from ...
> > >   where rownum <= 30

> > > Is there any simplier way to do select of 5 records like oracle does ?

> > No.

> Actually, not completely true -- the original criteria is equivalent to
the
> new SELECT FIRST X syntax of the Firebird.  So there is a simple way to do
> it.

> Sean

Sean Leyn

Delphi Developer
Fri, 12 Mar 2004 23:30:57 GMT

Re:shrink the select


Sergio,

Quote
> Be aware that SKIP & LIMIT are Firebird specific.

I thought that I was specific about that.

Quote
> "Sean Leyne" <sle...@atkin.com> escribi en el mensaje
> news:3baf352d$1_1@dnews...
....
> > new SELECT FIRST X syntax of the Firebird.  So there is a simple way to
do
> > it.

Eitan Mizrah

Delphi Developer
Sat, 13 Mar 2004 00:06:42 GMT

Re:shrink the select


I did something like this :

set term ^;

create procedure x

returns (a timestamp)

as

begin

for select a_date_time

from a_table

into :a

do begin

suspend

end;

end^

set term ;^

The error

-------------

Dynamic SQL Error
SQL error code = -104

Token unknown - line 10, char 3
end
Statement:  create ....

(I run the sql at IBConsole 1.0.0.315, database dialect is 3).

Quote
"Phil Shrimpton" <p...@nospam.shrimpton.co.uk> wrote in message

news:MPG.161942bdfb10f36d989892@newsgroups.borland.com...

Quote
> In article <3baf31ea_2@dnews>, eit...@e-ntouch.com wrote...

> Hi,

> > Sorry of the newbie question,
> >   but I tried both yours code and Phil code, and it doesn't compiled !

> > -- line 2, char 22 is "varchar" (the line was : returns (field1
varchar),

> Have you stated the varchars length ? e.g field1 varchar(20)

> Phil

Wood

Delphi Developer
Sat, 13 Mar 2004 01:47:50 GMT

Re:shrink the select


Quote
"Eitan Mizrahi" <eit...@e-ntouch.com> wrote in message

news:3baf5a16_2@dnews...

Quote
> I did something like this :

> set term ^;

> create procedure x

> returns (a timestamp)

> as

> begin

> for select a_date_time

> from a_table

> into :a

> do begin

> suspend

> end;

This should be
  suspend;

  end

Notice the difference in where the semi-colon is.

HTH
Woody

- Show quoted text -

Quote

> end^

> set term ;^

> The error

> -------------

> Dynamic SQL Error
> SQL error code = -104

> Token unknown - line 10, char 3
> end
> Statement:  create ....

> (I run the sql at IBConsole 1.0.0.315, database dialect is 3).

> "Phil Shrimpton" <p...@nospam.shrimpton.co.uk> wrote in message
> news:MPG.161942bdfb10f36d989892@newsgroups.borland.com...

> > In article <3baf31ea_2@dnews>, eit...@e-ntouch.com wrote...

> > Hi,

> > > Sorry of the newbie question,
> > >   but I tried both yours code and Phil code, and it doesn't compiled !

> > > -- line 2, char 22 is "varchar" (the line was : returns (field1
> varchar),

> > Have you stated the varchars length ? e.g field1 varchar(20)

> > Phil

Go to page: [1] [2]

Other Threads

1. CRT unit shrinks screen size?

2. How to: Shrink wrap BDE network product

3. Shrinking Paradox 5 tables

4. Shrinking tString List

5. Printer font shrinking

6. Printer Font Size Shrinks

7. Shrink to fit

8. grow/shrink arrays

9. Toolbar and shrinking client-area

10. Shrink DLL size???