?
Solved

Joining to a User Defined Table-Valued Function

Posted on 2007-03-22
4
Medium Priority
?
275 Views
Last Modified: 2007-04-16
I am trying to join to a table valued function in SQL Server 2000. However I cannot pass a column value from a table as a parameter. But I can pass a hard value such as 14548 or even a variable set outside the select statement.

Does not work:
SELECT LDM.LDMID,
      LDM.OrigQORID,
      LDM.DestQORID
FROM dbo.RGLDM LDM
      INNER JOIN dbo.usf_ContainerRentals(LDM.LDMID) F ON LDM.OrigQORID = F.QORID
WHERE LDM.Active = 1
ORDER BY LDM.Sequence

Does Work
SELECT LDM.LDMID,
      LDM.OrigQORID,
      LDM.DestQORID
FROM dbo.RGLDM LDM
      INNER JOIN dbo.usf_ContainerRentals(14548) F ON LDM.OrigQORID = F.QORID
WHERE LDM.Active = 1
ORDER BY LDM.Sequence

Does Work:
DECLARE @MyID INT
SET @MyID = 14548
SELECT LDM.LDMID,
      LDM.OrigQORID,
      LDM.DestQORID
FROM dbo.RGLDM LDM
      INNER JOIN dbo.usf_ContainerRentals(@MyID) F ON LDM.OrigQORID = F.QORID
0
Comment
Question by:dpbouchard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 1600 total points
ID: 18775733
And that is correct.  When you execute the UDF to generate the table for the JOIN, this is done prior to the row-by-row  joining, so that the UDF is executed just once.  Since it only runs once, telling it to run based on the value in each row is nonsensical.

If you need it to run once for each row.... well, I think you need to be returning something other than a table.

So, please define what you are attempting to do in a bit more detail, and we'll see if we can help.

0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 400 total points
ID: 18776478
this is a new feature in SQL 2005 (the cross apply operator).
0
 
LVL 1

Author Comment

by:dpbouchard
ID: 18781861
emoreau, that is a great question. If this is available in 2005 that will solve the problem, for we are migrating next month. If anyone knows the answer to that and point me to where I can verify it, that would be great.

bhess1,
We have an identity "QORID" that is set in a global database. We have several other databases (on the same server) that reference this identity. I need to be able to join on a row in that particular database from the global. But with each record the QORID changes so the database that the record comes from also changes acording to what the QORID is. (BTW...We have a table that maps the QORID to the database name that it applies to.)
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question