Re: Multiple rows in singleton select error

Article View: borland.public.interbase.sql

Article #10973

Back to borland.public.interbase.sql Previous Next

#10973

From: Aage Johansen
Date: Wed, 01 Jan 2003 21:36

46 lines
1897 bytes

Elad Keat wrote:
> 
> I wrote this procedure to look for users within an certein age range,
> however, when I run it I get the error message: "Multiple rows in singleto
> select".
> Why is my select considered a singleton, and what can I do to get multiple
> rows back?


Look up the "FOR SELECT ..." construct - this will return one row for
each user found, but you also need a SUSPEND, something like

...
as
begin
  for select USER_ID
    ...
    into :ID
  do
  begin
    suspend;
  end
end

> 
> SET TERM !! ;
> CREATE PROCEDURE SEARCH_USERS
>  (MIN_AGE SMALLINT, MAX_AGE SMALLINT)
> RETURNS (ID INTEGER)
> AS
> BEGIN
>   SELECT USER_ID
>      FROM USERS
>      WHERE CAST (EXTRACT (YEAR FROM CAST ('NOW' AS DATE)) AS SMALLINT) -
>      CAST (EXTRACT (YEAR FROM DATE_OF_BIRTH) AS SMALLINT)
>   BETWEEN :MIN_AGE AND :MAX_AGE
>      INTO :ID;
> END!!
> SET TERM ; !!
> 
> Thanks,
> Elad


--
Aage J.

Technical Details

Message-ID: <3E135F4D.1854F62B@offline.no>

Path:

arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!sn-xit-02!sn-xit-03!sn-xit-01!sn-xit-09!supernews.com!news.maxwell.syr.edu!eusc.inter.net!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail

References: <3e134e06@newsgroups.borland.com>

Show Full Headers

Complete Article Headers

X-Google-Language: ENGLISH,ASCII-7-bit
X-Google-Thread: 114a0e,4e898bb479eb6127
X-Google-Attributes: gid114a0e,public
X-Google-ArrivalTime: 2003-01-01 13:38:54 PST
Message-ID: <3E135F4D.1854F62B@offline.no>
Date: Wed, 01 Jan 2003 22:36:13 +0100
From: Aage Johansen <aagjohan@offline.no>
X-Mailer: Mozilla 4.72 [en] (Windows NT 5.0; I)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: borland.public.interbase.sql
Subject: Re: Multiple rows in singleton select error
References: <3e134e06@newsgroups.borland.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 80.212.34.185
X-Trace: newsgroups.borland.com 1041456974 80.212.34.185 (1 Jan 2003 13:36:14 -0800)
Lines: 46
Path: arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!sn-xit-02!sn-xit-03!sn-xit-01!sn-xit-09!supernews.com!news.maxwell.syr.edu!eusc.inter.net!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail

Reply to Parent Message