This problem is tricky to explain but I'll give it a go.

I am trying to build a single select statement that takes a data column
from a join early in the select and passes this into a function in a
later join.

e.g. function_a takes 1 parameter as input and returns a list of 2
columns called Sequence and Item

       SELECT.....
       join table_a a
       on a.x = y
       join function_a(a.field)
       on Item = a.number

Above, y is a variable unconnected with the table.

The idea is that the "join function" statement returns a list od
Sequence and Item data based on passing in the field value from
table_a, and then restricts this further by matching Items on variable
z.

The issue is that it appears to be impossible to refer to the a.field
column in in the "join function" statement. Referencing a.number in the
"on" statement is fine.

Thus it appears that the actual join element cannot reference columns
in and of the other joins (this results in a "Incorrect syntax near
'.'" statement.

Is this the case?

Table values functions cannot take column names as input arguments ( this is
not an inline function ). In SQL 2005 you can use APPLY operators, but in
SQL 2000, you will have to re-write the query to include the logic used by
the function directly in the query.

Thanks for the info

I'm using 2000 so a rewrite is on the cards!

Anith Sen wrote:
Show quoteHide quote

How can I Rearrange my Data
Query taking long-long time
tring’
3 most-recent contact dates
Timestamp in User-Defined Function Causes Error