Use columns from select statement as parameter in function when joining [duplicate]

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?

Andriy M · Accepted Answer · 2013-09-17 09:31:26Z

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, 2013 at 9:31

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jan Petter Jetmundsen Over a year ago

Yes. I actually did. OUTER APPLY was the correct approach for me.

Madhivanan · Accepted Answer · 2013-09-17 09:22:09Z

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, 2013 at 9:22

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Comments

Andrey Khmelev · Accepted Answer · 2013-09-17 09:22:26Z

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, 2013 at 9:22

Andrey Khmelev

1,1718 silver badges13 bronze badges

1 Comment

Jan Petter Jetmundsen Over a year ago

Sadly this oes not work. The intellisence dots the FromItemId within the paranthesis. (and the result is still invalid column name)

Marsh · Accepted Answer · 2013-09-17 09:47:02Z

0

use OUTER APPLY instead of left join

answered Sep 17, 2013 at 9:47

Marsh

1732 silver badges16 bronze badges

Collectives™ on Stack Overflow

Use columns from select statement as parameter in function when joining [duplicate]

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Linked

Related