joining temp table and user-define function proble

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

joining temp table and user-define function proble

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2006-03-10 : 15:00:13

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-10 : 15:12:18

It doesn't like the t.studentID being passed into the UDF. The derived table can only have one result set returned and not multiples which is what you'll get if you pass in t.studentID.

Tara Kizer
aka tduggan

johnsql
Posting Yak Master

161 Posts

Posted - 2006-03-14 : 14:24:22

Tara,
So how can we rewrite the joining sql scripts?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-14 : 14:26:33

In order to rewrite your code, we would need DDL for all tables involved, INSERT INTO statements for sample data, expected result set using that sample data, the UDF code, and anything else that we would need to replicate your problem on our own machines.

Tara Kizer
aka tduggan

johnsql
Posting Yak Master

161 Posts

Posted - 2006-03-15 : 17:28:53

-------------Prior to the script segment ABOVE as you see, the following scripts somewhere in my batch script ----------------------------------
create table #tgstinfo (StudentID int, firstname varchar(50), lastname
varchar(50), taxID varchar(50), address1 varchar(100),
city varchar(40), state varchar(40), zip varchar(20), phone varchar(20),
DOB varchar(50) )

-- get student info
insert #tgstinfo
exec aagGetGPAStudentsInfo @CompanyID , @vSYTermID , @vClassTyID

--------------------------------------------------------------------------------
-----------------------------
-- defintion of 1 stored procedure and 1 user-defined function; they are
compiled and work alone well.
--------------------------------------------------------------------------------
-----------------------------

CREATE procedure dbo.aagGetGPAStudentsInfo
@CompanyID int,
@SYTermID int,
@ClassTyID int
AS
set nocount on

/*******************************************************************************
*****************
20060206 john made initial version to return students' information.
20060215 john modified to include students of @ClassTyID of correct @SYTermID
20060307 john modified to include every students regardless status (active,
inactive). The calling
sp must filter based on constraint.
********************************************************************************
*****************/
-- student information
//////////////////////////////////////////////////////////////////////////////
Select distinct se.StudentID, cc.firstname, cc.lastname, cc.taxID, cc.address1,
cc.city, cc.state,cc.zip, cc.phone,
(case when (year(cc.Birthdate)< 1901 or cc.birthdate is null) then '-' else
convert(varchar,cc.Birthdate,101) end ) as DOB
from studentGrade s
inner join Studentenrollment se on s.Companyid = se.CompanyId and
s.enrollmentID = se.enrollmentId
inner join Section sc on sc.companyid = se.companyid and sc.sectionid =
se.sectionid
inner join Customer cc on cc.Companyid= se.Companyid and cc.CustomerID =
se.StudentID
inner join assignclass ac on se.companyid = ac.companyid and se.StudentID =
ac.CustomerID
where se.Companyid=@companyID and ac.classtyid = @classTyID
AND ac.termID = @SYTermID
order by cc.lastname, cc.firstname

-------------------------------------
CREATE function dbo.fn_GetFamilyMembersOfCustomer(@companyID int,
@givenCustomerID int, @entityTemplateGivenCustomerID int,
@entityTemplateIDRelatives int)
returns table
AS
/*
20060303 john 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
)
)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-15 : 17:35:16

The amount of code that you posted is almost impossible to review for free. If you can simplify your problem, perhaps we might be able to help.

Tara Kizer
aka tduggan

johnsql
Posting Yak Master

161 Posts

Posted - 2006-03-16 : 01:46:37

Focusing on the script segment that makes the SQL error, can we use something else other than an udf to join with the temp table #tgstinfo? That "something" else plays the role of a derived table and received t.studentID passed to it in joining.
Thanks.
P.S. You seem to be a person who has appeared in this forum site since 2004, don't you? What are about others like Kristen, Bret-8, Eye Chart, etc? Do they stay in here?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-16 : 12:37:36

Change the subject to "[UNRESOLVED] joining temp table and user-define function problem". This will get the attention of others that there is an issue in this post still. Some people might think it has been resolved already due to the number of posts inside it.

Tara Kizer
aka tduggan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-16 : 13:18:12

Ah... a classic WHERE IN ( WHERE IN ( WHERE IN ( etc ))) query in the UDF. I think it's time to learn about derived tables! The worst thing you can do is keep stuffing "criteria" like that in a WHERE clause. Step back, clearly state what you have in terms of tables and what you need, and re-write it logically.

And also, never use UDF's to look up values in tables in your database. Always use JOINS to relate tables -- that's what they are for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources