locked
Table valued function in a inner join RRS feed

  • Question

  • Hello,

     I have a question in regards to using a table valued function in Joins

    This is how i am using it and it is throwing a error.

    Select * from Table1 inner join table2 on table1.id = table2.id_fk

    inner join fn_getvalues(Table1.id,table1.startdate,table1.endate,null)  fn on fn.id = table2.id_fk

    It is throwing a error saying not able to

    multi-part indentifier could not be bound.. table1.id table1.startdate

    Can somebody tell what am i doing wrong here..

    Tuesday, September 30, 2014 2:50 PM

Answers

  • Give this a try:

    SELECT * 
    FROM Table1 
    CROSS APPLY fn_getvalues(Table1.id,Table1.startdate,Table1.endate,null)
    JOIN table2 on table1.id = table2.id_fk


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    • Proposed as answer by Rob.Kachmar Tuesday, September 30, 2014 3:53 PM
    • Marked as answer by Charlie Liao Tuesday, October 7, 2014 9:43 AM
    Tuesday, September 30, 2014 3:21 PM
    • Proposed as answer by Naomi N Tuesday, September 30, 2014 3:56 PM
    • Marked as answer by Charlie Liao Tuesday, October 7, 2014 9:43 AM
    Tuesday, September 30, 2014 3:27 PM
  • Unfortunately, you can't send table fields to a table valued function within a JOIN.  This can only be done with a scalar function.  See if this alternate approach works for you.

    SELECT *
      FROM dbo.Table1
      JOIN dbo.Table2
        ON Table1.id = Table2.id_fk
      JOIN (
            SELECT fn1.*
              FROM (
                    SELECT * 
                      FROM dbo.Table1 AS A
                      JOIN dbo.Table2 AS B
                        ON A.id = B.id_fk
                   ) Table1and2
             OUTER APPLY
                   (
                    SELECT *
                      FROM dbo.fn_getvalues(Table1and2.id,Table1and2.startdate,Table1and2.endate,null)
                   ) fn1
           ) fn
        ON Table2.id_fk = fn.id
        
    
    /*
    -- ============================================================================
    -- CREATING TEST DATA TO MIMIC YOUR ENVIRONMENT
    IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL DROP TABLE dbo.Table1;
    IF OBJECT_ID('dbo.Table2', 'U') IS NOT NULL DROP TABLE dbo.Table2;
    GO
    
    CREATE TABLE dbo.Table1 (id int, startdate date, endate date);
    CREATE TABLE dbo.Table2 (id_fk int);
    GO
    
    INSERT dbo.Table1 VALUES (1, '01/01/1901', '01/01/1902'), (2, '01/01/1903', '01/01/1904')
    INSERT dbo.Table2 VALUES (1), (2)
    GO
    
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID('dbo.fn_getvalues') AND XTYPE IN ('FN', 'IF', 'TF'))
        DROP FUNCTION dbo.fn_getvalues
    GO
    CREATE FUNCTION dbo.fn_getvalues(@id int, @startdate date, @endate date, @parm4 int = NULL)
    RETURNS @ValueTable TABLE (id int)
    AS
    BEGIN
    INSERT @ValueTable (id)
    SELECT id 
      FROM dbo.Table1 
     WHERE id        = @id
       AND startdate = @startdate
       AND endate    = @endate
    RETURN
    END
    GO
    -- SELECT * FROM dbo.fn_getvalues(1,'01/01/1901','01/01/1902',null)
    -- ============================================================================
    --*/
    

    • Marked as answer by Charlie Liao Tuesday, October 7, 2014 9:43 AM
    Tuesday, September 30, 2014 3:51 PM

All replies

  • I ran into something like this before and fond this article helpful:

    http://stackoverflow.com/questions/4123413/the-multi-part-identifier-columnname-could-not-be-bound

    OUTER APPLY is the key.

    Please let me know if this helps

    Thanks

    Carl


    • Edited by CarlGanz Tuesday, September 30, 2014 4:11 PM
    • Proposed as answer by CarlGanz Tuesday, September 30, 2014 4:46 PM
    • Unproposed as answer by CarlGanz Tuesday, September 30, 2014 4:46 PM
    Tuesday, September 30, 2014 3:17 PM
  • Give this a try:

    SELECT * 
    FROM Table1 
    CROSS APPLY fn_getvalues(Table1.id,Table1.startdate,Table1.endate,null)
    JOIN table2 on table1.id = table2.id_fk


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    • Proposed as answer by Rob.Kachmar Tuesday, September 30, 2014 3:53 PM
    • Marked as answer by Charlie Liao Tuesday, October 7, 2014 9:43 AM
    Tuesday, September 30, 2014 3:21 PM
    • Proposed as answer by Naomi N Tuesday, September 30, 2014 3:56 PM
    • Marked as answer by Charlie Liao Tuesday, October 7, 2014 9:43 AM
    Tuesday, September 30, 2014 3:27 PM
  • Unfortunately, you can't send table fields to a table valued function within a JOIN.  This can only be done with a scalar function.  See if this alternate approach works for you.

    SELECT *
      FROM dbo.Table1
      JOIN dbo.Table2
        ON Table1.id = Table2.id_fk
      JOIN (
            SELECT fn1.*
              FROM (
                    SELECT * 
                      FROM dbo.Table1 AS A
                      JOIN dbo.Table2 AS B
                        ON A.id = B.id_fk
                   ) Table1and2
             OUTER APPLY
                   (
                    SELECT *
                      FROM dbo.fn_getvalues(Table1and2.id,Table1and2.startdate,Table1and2.endate,null)
                   ) fn1
           ) fn
        ON Table2.id_fk = fn.id
        
    
    /*
    -- ============================================================================
    -- CREATING TEST DATA TO MIMIC YOUR ENVIRONMENT
    IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL DROP TABLE dbo.Table1;
    IF OBJECT_ID('dbo.Table2', 'U') IS NOT NULL DROP TABLE dbo.Table2;
    GO
    
    CREATE TABLE dbo.Table1 (id int, startdate date, endate date);
    CREATE TABLE dbo.Table2 (id_fk int);
    GO
    
    INSERT dbo.Table1 VALUES (1, '01/01/1901', '01/01/1902'), (2, '01/01/1903', '01/01/1904')
    INSERT dbo.Table2 VALUES (1), (2)
    GO
    
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID('dbo.fn_getvalues') AND XTYPE IN ('FN', 'IF', 'TF'))
        DROP FUNCTION dbo.fn_getvalues
    GO
    CREATE FUNCTION dbo.fn_getvalues(@id int, @startdate date, @endate date, @parm4 int = NULL)
    RETURNS @ValueTable TABLE (id int)
    AS
    BEGIN
    INSERT @ValueTable (id)
    SELECT id 
      FROM dbo.Table1 
     WHERE id        = @id
       AND startdate = @startdate
       AND endate    = @endate
    RETURN
    END
    GO
    -- SELECT * FROM dbo.fn_getvalues(1,'01/01/1901','01/01/1902',null)
    -- ============================================================================
    --*/
    

    • Marked as answer by Charlie Liao Tuesday, October 7, 2014 9:43 AM
    Tuesday, September 30, 2014 3:51 PM