Solved

Joining to a User Defined Table-Valued Function

Posted on 2007-03-22
4
271 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:
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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

736 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