Hi Phil,

> I just discovered that one could call a stored procedure in the From
> section. I want to pass into the procedure the values
> person.firstname, person.middlename and person.lastname.
>
> Below is how I would imagine how this would look, but it is not the
> correct syntax. Can anyone tell me what is the correct syntax?
> (I did look in the manuals, but am not finding any reference to this
> particular situation).
>
> SELECT ADDRESS.STREET1,
> ADDRESS.STREET2,
> ADDRESS.CITY,
> ADDRESS.STATECD,
> ADDRESS.ZIPCODE,
> ADDRESS.OWNERID,
> PERSON.FIRSTNAME,
> PERSON.MIDDLENAME,
> PERSON.LASTNAME,
> FORMATNAME.FULLNAME
> FROM FORMATNAME(PERSON.FIRSTNAME, PERSON.MIDDLENAME,
> PERSON.LASTNAME, 'LNF'),
> PERSON INNER JOIN ADDRESS ON (PERSON.PERSONID = ADDRESS.OWNERID)
> WHERE ADDRESS.OWNERID IS NOT NULL
>
> FORMATNAME is a stored procedure that returns a properly formatted
> name based on the input parameters.

You can also use the procedure in a sub-select.

SELECT
ADDRESS.STREET1,
ADDRESS.STREET2,
ADDRESS.CITY,
ADDRESS.STATECD,
ADDRESS.ZIPCODE,
ADDRESS.OWNERID,
PERSON.FIRSTNAME,
PERSON.MIDDLENAME,
PERSON.LASTNAME,
(SELECT FULLNAME FROM FORMATNAME(PERSON.FIRSTNAME, PERSON.MIDDLENAME,
PERSON.LASTNAME, 'LNF'))
FROM
PERSON
JOIN ADDRESS ON (PERSON.PERSONID = ADDRESS.OWNERID)
WHERE
ADDRESS.OWNERID IS NOT NULL

Regards,
Arno Brinkman