Posted by Ken Post on 10/25/05 04:15

Hi All:
I've read a whole slew of posts about creating temp tables using stored
proceedures to get the crosstab ability, but I'm wondering if, for this
specific case, there might be a more efficient way.

What makes this question different from the others that I've read is
that I'm using user defined functions, not tables. I actually think
that I've got the crosstab thing down, it's just passing the parameter
to the 2nd udf that's messing me up.

I've got a people table and an address table. Each person can have
multiple addresses. I need to create a dataset that has in each row
the name of the person, the first address, any second address, and any
third address. I only need to show the first 3, so if there's 100, I
can just ignore the rest.

I created a user defined function to return the 1st, 2nd, or 3rd
address for a given person.
udf_ReturnAddress(PersonID,MatchNumber)

Another user defined function returns the people that I'm looking for
(potential duplicates for a person in this case).
udf_ReturnPossibleDupsForAPerson(PersonID)


SELECT
Main.FoundPersonID, Main.LastName, A1.Street, A2.Street,
A3.Street
FROM
udf(ReturnPossibleDupsForAPerson(@PersonID) MainTable
CROSS JOIN
(SELECT Street1 FROM
udf_ReturnAddress(Main.FoundPersonID,1) Adr1) A1
CROSS JOIN
(SELECT Street1 FROM
udf_ReturnAddress(Main.FoundPersonID,2) Adr2) A2
CROSS JOIN
(SELECT Street1 FROM
udf_ReturnAddress(Main.FoundPersonID,3) Add3) A3


If, for the first parameter for the return address function, I replace
Main.FoundPersonID with the ID of a person, it works just fine. I
obviously don't want a static id as a parameter - I want to use the ID
of the person that the first udf found. Leaving the variable
MainTable.PersonID there causes an error in the query designer though.

I get "Error in list of function arguments: '.' not recognized.

So maybe my problem is that I just don't know how to pass the id of the
person that's found by the first UDF as the parameter of the function
to find the found person's 3 addresses.

Any guidance would be greatly appreciated!
Thanks
Ken

[Back to original message]