Getting error on INSERT.. SELECT using table valued function
mscertified
Msg 4104, Level 16, State 1, Procedure ESIMonthlyNC, Line 207
The multi-part identifier "P.ParentId" could not be bound.
(note it says BOUND and not FOUND)
on this line in SQL below:
LEFT JOIN dbo.GetParentsBankAccount(P.ParentId,@RunDate) As BA
This is a function that returns a single row table of data from a related
table. I need it to return the data for the current parent hence my parameter
- but it does not work. Any suggestions?
/* Insert all records for parents to be paid into temporary table */
/* All data from parent table */
/* All data from bank account table (via table function) */
/* Number of children (via scalar function) */
INSERT INTO #ESITemp
(ParentID,FirstName,MiddleInitial,LastName,GroupId,SSN,Phone1,
Phone2,Address1,Address2,City,State,Zip,FamilyPremium,EmployerPremium,
BankName,BankRouting,BankAccount,BankAccountHolder,AccountType,
AccountActiveDate,AccountInactiveDate,NumChildren)
SELECT P.ParentID, P.FirstName, P.MiddleInitial, P.LastName, P.GroupId,
P.SSN, P.Phone1,
P.Phone2, P.Address1, P.Address2, P.City, P.State, P.Zip, P.FamilyPremium,
P.EmployerPremium,BA.BankName,BA.BankRouting,BA.BankAccount,BA.BankAccountHolder,
BA.AccountType,BA.AccountActiveDate,BA.AccountInactiveDate,
dbo.GetParentsChildren(P.ParentId,@RunDate)
FROM dbo.Parent P LEFT JOIN dbo.MonthlyReport As MR
ON P.ParentId = MR.ParentId
LEFT JOIN dbo.GetParentsBankAccount(P.ParentId,@RunDate) As BA
ON P.ParentId = BA.ParentId
WHERE MR.ParentId IS NULL
Erland Sommarskog
> Getting:
> Msg 4104, Level 16, State 1, Procedure ESIMonthlyNC, Line 207
> The multi-part identifier "P.ParentId" could not be bound.
> (note it says BOUND and not FOUND)
Yes, P.ParentID could be a table source with column, or it could be a
(CLR) column with a type method called ParentId.
> on this line in SQL below:
> LEFT JOIN dbo.GetParentsBankAccount(P.ParentId,@RunDate) As BA
> This is a function that returns a single row table of data from a related
> table. I need it to return the data for the current parent hence my
> parameter
> - but it does not work. Any suggestions?
>...
> FROM dbo.Parent P LEFT JOIN dbo.MonthlyReport As MR
> ON P.ParentId = MR.ParentId
> LEFT JOIN dbo.GetParentsBankAccount(P.ParentId,@RunDate) As BA
> ON P.ParentId = BA.ParentId
> WHERE MR.ParentId IS NULL
You cannot pass a table column to a table-UDF and then join with the UDF.
Just think of it, what there are 10 rows in parent, and each 10 rows
generate another 10 rows from the UDF. How did you intend this to work?
Fortunately, there is a way out: use the OUTER APPLY operator. This
operator was added precisely to address this scenario. I suggest that
you look it up in Books Online.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx