Don,

It looks like you have copied a standard query into PSQL. You are
naming fields (which has no relevant) and missing colons that are
important in your outer select.

for select distinct
p1.firstname || ' ' || p1.lastname,
p1.Lastname,
p1.Firstname,
p1.personid
from
people p1
join Classes c1 on p1.personid = c1.teacherid
where
p1.persontype = 1
Order by p1.lastname, p1.firstname
into
:teachername, :lastname, :firstname, :teacherid
do
begin
....

The inner select looks fine to me.

The acid test is iSQL. If your query runs in iSQL, but doesn't run in
whatever other tool then there is probably a bug in the other tool. I
have never heard of ASTA, is it a set of components for Delphi? There
are lots of connection components that work with Firebird, perhaps it
is unhappy because it doesn't understand selectable stored procedures.

Adam











--- In firebird-support@yahoogroups.com, "Don Gollahon"
<dlgllhn@I...> wrote:

>
> I have a stored procedure that requires no parameters:
>
> ALTER PROCEDURE UNQTEACHERCLASSES
> RETURNS ( TEACHERNAME VARCHAR( 50 )
> , LASTNAME VARCHAR( 30 )
> , FIRSTNAME VARCHAR( 30 )
> , TEACHERID VARCHAR( 32 )
> , CLASSID INTEGER
> , CLASSNAME VARCHAR( 30 )
> , HOMEROOM CHAR( 1 ) )
> AS
> BEGIN
> for select distinct
> p1.firstname || ' ' || p1.lastname as TeacherName,
> p1.Lastname, p1.Firstname,
> p1.personid as teacherid
> from
> people p1
> join Classes c1 on p1.personid = c1.teacherid
> where
> p1.persontype = 1
>
> Order by p1.lastname, p1.firstname
> into
> teachername, lastname, firstname, teacherid
>
> do
> begin
> select first 1 classid, classname, homeroom
> from classes
> where teacherid = :teacherid
> order by homeroom desc
> into :classid, :classname, :homeroom;
>
> SUSPEND;
> end
> END
>
>
> It works fine when called in IB_SQL.exe. I'm trying to call it

from ASTA

> and it says table "" doesn't exist. I also tried to then make a

view based

> on the SP using IB_Expert and it gives the same error:
>
> create view UNQClassesTeacher (
> TEACHERNAME
> , LASTNAME
> , FIRSTNAME
> , TEACHERID
> , CLASSID
> , CLASSNAME
> , HOMEROOM )
> as
>
> select TEACHERNAME, LASTNAME, FIRSTNAME,
> TEACHERID, CLASSID, CLASSNAME, HOMEROOM
> from UNQTeacherClasses ;
>
> How can I get this to work?
>
> Firebird 1.5, IBObjects 4.3, Delphi 6, ASTA 3.
>
> _________________________________________
> Don Gollahon
> dlgllhn@I...
> ICQ#: 115831669
> MSN: dlgllhn@t...
> "What in Eternity does it matter?"
> _________________________________________
>
>
>
>
> [Non-text portions of this message have been removed]
>