September 30, 2008 at 10:46 am
I have the following Table UDF:
CREATE FUNCTION dbo.udf_GetBankAccountsExpected(@AgentIDInput Int, @BankAccountsExpected Int)
RETURNS @AgentID TABLE (AgentID INT NOT NULL, Counter INT NOT NULL)
AS
BEGIN
DECLARE @Counter Int
SET @Counter = 0
WHILE @Counter < @BankAccountsExpected
BEGIN
SET @Counter = @Counter + 1
INSERT @AgentID (AgentID, Counter)
VALUES (@AgentIDInput, @Counter)
END
RETURN
END
I need to join to that UDF in a query like this:
SELECT
tr.AgentID,
tr.NumberOfBankAccountsExpected,
tr.AgentBankAccountID,
tr.BankName
FROM
(
SELECT
A.AgentID,
(Select AR.NumberOfBankAccountsExpected
FROM dbo.AgentApplication AR
WHERE AR.AgentID = A.AgentID) AS NumberOfBankAccountsExpected,
AB.AgentBankAccountID,
AB.BankName
FROM Agent AS A
LEFT OUTER JOIN AgentBankAccount AS AB
ON A.AgentID = AB.AgentID
LEFT OUTER JOIN AddressDetail AS AD
ON AB.AddressID = AD.AddressID
WHERE EXISTS(Select AA.AgentApplicationID
from AgentApplication AS AA
where AA.AgentID = A.AgentID
and AA.AgentApplicationID = 14)
) AS TR
INNER JOIN dbo.udf_GetBankAccountsExpected(tr.AgentID,tr.NumberOfBankAccountsExpected) tx
ON tr.AgentID = tx.AgentID
When I try to run the query this way, I get a message that tr.AgentID and tr.NumberOfBankAccountsExpected cannot be bound.
How do I join to the UDF and feed in the AgentID and NumberOfBankAccountsExpected from the inner query?
Thank you for your help!
CSDunn
October 1, 2008 at 3:53 am
Could you please describe what are you trying to do? I suspect that this can be done a lot easier without the table function - it seems to me it is there only to multiply rows based on NumberOfBankAccountsExpected. Is that right, or did you post some simplified version of the function?
October 1, 2008 at 7:07 am
While Vladan is probably correct in noting that there is probably a simpler, better way to do this I will attempt to answer your question. I think you need to use the CROSS APPLY operation in place of INNER JOIN when joining to your function.
Query Cross Apply
Function(parameter1, parameter2)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 1, 2008 at 8:04 am
As far as I know, when a function is returning a table the input cannot change in the query.
Every different row in the above table join would return a different TABLE from the function.
That being said this appears to be a prime spot for using a number table.
(http://www.sqlservercentral.com/articles/Advanced+Querying/2547/)
simplified version
select agentID, numberOf BankAccountsExpected,
agtBnkAcct.AgentBankAcountID, agtBnkAcct.BankName,
nbr as counter
from agent agt
left join agentBankAccount agtBnkAcct ON agt.agentID = agtBnkacct.agentID
inner join agentApplication agtApp ON agt.agentID = agtApp.agentID
inner join numbers ON agtApp.numberOfBankAccountsExpected >= numbers.nbr
where agtApp.agentApplicationID = 14
Note: your query is almost dysfunctional. There is a subquery in the select statement against AgentApplication and an Exists() again against AgentApplication in the where statement. Simplify by joining agent to AgentApplication.
good luck
daryl
October 1, 2008 at 8:59 am
I agree with Daryl, it is not possible to return different table for each row... which the poster was trying to do. But the entire query is written in such a way that I have no idea what is required - that's why I asked the questions instead of trying to find all errors in the posted query.
If the question is "how to duplicate rows based on value in some column", then there is very easy solution using Tally table (also known as Numbers table - I see that Daryl already posted something in that line)... but anyway the question remains why you would want to duplicate the rows.
October 1, 2008 at 9:10 am
The results that come back from the query represent form information that is filled out online by 'Agents'. Each agent is expected to have a certain number of 'Outlets'. The idea is that if an Agent is expected to have three Outlets, and the Agent has completed two forms, then the Agent would see one empty form for the last exepected Outlet.
My first attempt at this was to create a table UDF to determine the number of expected Outlets per agent, and to join that back to the original query and show completed for data for existing outlets, and NULL data for remaing 'expected' outlets. I have been able to do this by having the call to the function in a View, then RIGHT joining the view back to the main query.
Thank you for your help!
CSDunn
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply