Solved

Joining to a User Defined Table-Valued Function

Posted on 2007-03-22
4
269 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
4 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 400 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 100 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

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