Use columns from select statement as parameter in function when joining

Question

Consider the following sql query:

SELECT TaskId,FromItemId, IHV_FROM.NodePath as FromPath
FROM VIEW_TASKS with (nolock) 
left Join fn_ITEM_HIERARCHY(FromItemId) IHV_FROM ON FromItemId = IHV_FROM.ItemId
WHERE ...

fn_ITEM_HIERARCHY is a function which takes one input parameter. I would like to use FromItemId from VIEW_TASKS as input parameter to the function. When doing it like above, i get the error (Microsoft SQL Server 2008) "Invalid column name".

The SELECT statement returns multiple values, so it is not possible to assign the value to a variable in a separate select statement. Any ideas?

score 2 · Accepted Answer · answered Sep 17 '13 at 09:31

2

You probably need to use OUTER APPLY in this case:

SELECT TaskId,FromItemId, IHV_FROM.NodePath as FromPath
FROM VIEW_TASKS with (nolock) 
OUTER APPLY fn_ITEM_HIERARCHY(FromItemId) IHV_FROM
WHERE ...
;

If the function always returns rows or if you only want results for values where the function does return rows, use CROSS APPLY instead.

answered Sep 17 '13 at 09:31

Andriy M

76,112
17
94
154

Yes. I actually did. OUTER APPLY was the correct approach for me. – Jan Petter Jetmundsen Sep 17 '13 at 09:37

score 1 · Answer 2 · answered Sep 17 '13 at 09:22

1

Use Cross apply as shown here

http://blog.sqlauthority.com/2008/01/03/sql-server-2005-last-ran-query-recently-ran-query/

answered Sep 17 '13 at 09:22

Madhivanan

13,470
1
24
29

score 0 · Answer 3 · answered Sep 17 '13 at 09:22

0

use alias

SELECT t.TaskId, t.FromItemId, IHV_FROM.NodePath as FromPath
FROM VIEW_TASKS t with (nolock) 
left Join fn_ITEM_HIERARCHY(FromItemId) IHV_FROM ON t.FromItemId = IHV_FROM.ItemId

answered Sep 17 '13 at 09:22

Andrey Khmelev

1,141
8
13

Sadly this oes not work. The intellisence dots the FromItemId within the paranthesis. (and the result is still invalid column name) – Jan Petter Jetmundsen Sep 17 '13 at 09:25

score 0 · Answer 4 · answered Sep 17 '13 at 09:47

0

use OUTER APPLY instead of left join

answered Sep 17 '13 at 09:47

Marsh

173
1
14

Use columns from select statement as parameter in function when joining

4 Answers4