> This is the text of a select procedure I have written to replace a
> view. It works - so I can continue doing this in this manner,
> particularly if this how I'm "supposed" to do it. But it seems a little
> clumsy, and I don't know how this compiles compared to a different
> implementation.
>
> Given a table "addresss", with a foreign key zipcode_id in the table
> zipcode, and the function get_zipinfo(), which returns a zipcode, city,
> and state based on an id -
>
> CREATE PROCEDURE ADDRESSES (ADDRESS_ID INTEGER)
> RETURNS (ID INTEGER, ADDRESS VARCHAR (10), STREET VARCHAR (50),
> STREET_TYPE VARCHAR (30), SUITE VARCHAR (20), CITY VARCHAR (30), STATE
> CHARACTER (2), ZIPCODE CHARACTER (10))
> AS
> DECLARE VARIABLE zipcode_id INTEGER;
> BEGIN
> FOR SELECT address.id, address, street, street_type, suite, zipcode_id
> FROM address, street_types
> WHERE address.id = :address_id and street_type_id = street_types.id
> INTO :id, :address, :street, :street_type, :suite, :zipcode_id
> DO BEGIN
> FOR SELECT city, state, zipcode FROM get_zipinfo(:zipcode_id)
> INTO :city, :state, :zipcode
> DO
> SUSPEND;
> END
> END !!
>
> The other way I tried (but could not get sucessfully created) was -
>
> CREATE PROCEDURE ADDRESSES (ADDRESS_ID INTEGER)
> RETURNS (ID INTEGER, ADDRESS VARCHAR (10), STREET VARCHAR (50),
> STREET_TYPE VARCHAR (30), SUITE VARCHAR (20), CITY VARCHAR (30), STATE
> CHARACTER (2), ZIPCODE CHARACTER (10))
> AS
> BEGIN
> FOR SELECT address.id, address, street, street_type, suite,
> (SELECT city, state, zipcode FROM get_zipinfo(:zipcode_id) )
> FROM address, street_types
> WHERE address.id = :address_id and street_type_id = street_types.id
> INTO :id, :address, :street, :street_type, :suite, :city, :state,
> :zipcode
> DO
> SUSPEND;
> END !!
>
> I kept getting an error on the first comma in the second select -
> (SELECT city,
>
> Dunno why - syntax SEEMS reasonable . . .
>
> Daniel

The syntax in your second statement is, unfortunately, not reasonable, since
you are trying to select 3 fields in a select statement slot which is
expecting one and only one field value. No wonder it barfs.

FOR SELECT address.id, address, street, street_type, suite,
(SELECT city FROM get_zipinfo(:zipcode_id) as city,
(SELECT state FROM get_zipinfo(:zipcode_id) as state,
(SELECT zipcode FROM get_zipinfo(:zipcode_id) as zipcode

would have worked.
Alan