joining temp table and user-define function problem
-
Folks,
I have 1 stored procedure and 1 function which each returns a table or record set. In my SQL script, I create a temp table (#tgstinfo) to store the return results from the stored procedure. And then I use that temp table to inner join with a function (dbo.fn_GetFamilyMembersOfCustomer()) that also returns a record set. However, I get an error at the following script segment when I inner join the temp table and udf [error highlighted at t.studentID on the line of "...from dbo.fn_GetFamilyMembersOfCustomer(@companyID , t.studentID , 3 , 4)..." ]
select t.*, w.RelativeFirstName as pFirstName, w.RelativeLastName as pLastName
from #tgstinfo t
left join (select top 1 RelativeFirstName, RelativeLastName, studentID
from dbo.fn_GetFamilyMembersOfCustomer(@companyID , t.studentID
, 3 , 4 )) as w
on w.studentID = t.studentID
-- and I get the error in QA
Server: Msg 170, Level 15, State 1, Procedure aagGetGPATranscript, Line 206
Line 206: Incorrect syntax near 't'.
Thanks for your ideas in advance.
-
You cannot use set of values as a parameter for UDF.
t.StudentId is not a value, it's a set of values.
You need rewrite the function to have only @CompanyId as a parameter because you already have SudentId in result set.
select t.*, w.RelativeFirstName as pFirstName, w.RelativeLastName as pLastName
from #tgstinfo t
left join dbo.fn_GetFamilyMembersOfCustomer(@companyID , 3 , 4 ) as w
on w.studentID = t.studentID
_____________
Code for TallyGenerator -
Thanks Mr. or Ms 500,
I agree with you to pass a value for udf dbo.fn_GetFamilyMembersOfCustomer(). But if so, then the left join table #tgstinfo can associate with all set of records that loses the filetering of studentID. Acutally, I want one studentID-specified row on the temp table #tgstinfo joining with one row of udf #tgstinfo who has the specified studentID matches; each unique student from the left joining table #tgstinfo associate with 1 row from the right table returned by udf dbo.fn_GetFamilyMembersOfCustomer().
Details of my udf:
-----------------
CREATE function dbo.fn_GetFamilyMembersOfCustomer(@companyID int, @givenCustomerID int, @entityTemplateGivenCustomerID int,
@entityTemplateIDRelatives int)
returns table
AS
/*
20060303 Trong made initial verion to return family members of a customer like parent, sibling, grandparent, spouse.
e.g. if @entityTemplateGivenCustomerID= 3 (student) and @entityTemplateIDRelative = 3 (student's sibling)
if @entityTemplateGivenCustomerID= 3 (student) and @entityTemplateIDRelative = 4 (student's parents)
if @entityTemplateGivenCustomerID= 4 (parent or spouse) and @entityTemplateIDRelative = 4 (parent or spouse), and so on.
EntityTemplateID is found in table "CustomerTy"
*/
return
select ct.EntityTemplateID, w.RelationTy , ct.description , cu.customerID, cu.customerStatusTy, cu.lastName as RelativeLastName, cu.FirstName as RelativeFirstName,
w.description as relationWithStudent, w.FromCustomerID as studentID
from customer cu
inner join CustomerTy ct on ct.companyID = cu.companyID and ct.customerTy = cu.customerTy
inner join ( select cr4.companyID, cr4.RelationTy, cr4.FromCustomerID, cr4.ToCustomerID, crt4.description
from CustomerRelation cr4 inner join CustomerRelationTy crt4 on crt4.companyID= cr4.companyID and crt4.RelationTy=cr4.RelationTy
where cr4.companyID = @companyID and cr4.FromCustomerID = @givenCustomerID ) w
on w.companyID = cu.companyID and cu.customerID = w.ToCustomerID
where cu.companyID = @companyID
and ct.EntityTemplateID = @entityTemplateIDRelatives
and w.RelationTy in (
select distinct cr2.RelationTy from CustomerRelation cr2
inner join CustomerRelationTy crt2 on crt2.companyID = cr2.companyID and crt2.RelationTy=cr2.RelationTy
where cr2.companyID = @companyID and cr2.FromCustomerID in (select cu3.customerID from customer cu3
inner join CustomerTy ct3 on ct3.companyID = cu3.companyID and ct3.customerTy = cu3.customerTy
where cu3.companyID = @companyID and ct3.EntityTemplateID = @entityTemplateGivenCustomerID
 
)
-
It's better to use a view in this case.
Function is just a wrong idea.
_____________
Code for TallyGenerator -
Sergiy,
I got you. Thanks for your kind explanations and suggestions.
BTW, if I do not use function, then view and stored procedure is used. what is your con and pro for using VIEW and STORED PROCEDURE?
Thanks
-
It's really incompartible things.
It's like to ask "what is your con and pro for using CAR and BOAT?"
As you can see UDF just does not work where VIEW should be used. Same for SP.
_____________
Code for TallyGenerator -
Sergiy,
Thanks. I am trying to use a VIEW instead of a UDF. Do we need to re-compile a view in which relevant tables creating the view is changed (structure, increased rows, etc.)?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply