Alan McDonald wrote:

>>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
>
>

Thank you! That makes sense - even if I think it's unreasonable for an
otherwise perfectly good parser to not be able figure that out <g>. Sheesh.

Now that I know how - is there a preferred method for this kind of thing
- inline selects versus forselect constructs? Or is it another example
of Firebird flexibility and it's just personal preference?

Daniel