SQLTeam.com | Weblogs | Forums

Function being call in where clause


#1

Hello sql expert,

I am getting this error when executed below query:
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_Split", or the name is ambiguous.

How do I use the split function on my where clause? The split function name is: dbo.splitfuntion

Here is my query:

Declare @Column2value varchar(max)
Set @Column2value = 'ABC,CDE,FGH,IJK'

Select column1, column2, column3, column4
From MediumTable
Where column2 = dbo.splitfuntion (@Column2value,',')

When funtion being call in the where clause, is that the best efficient way?


#2

I strongly suspect that it is a table-valued function, not a scalar function, so you need to do something like this, including changing the column name to what is actually returned by the function, of course:

Select mt.column1, mt.column2, mt.column3, mt.column4
From MediumTable mt
Where Exists(Select 1 From dbo.splitfuntion (@Column2value,',') sf Where sf.column_name = mt.column2)


#3

Assuming your function is a table valued function, do something like this:

DECLARE @Column2value VARCHAR(MAX);
SET @Column2value = 'ABC,CDE,FGH,IJK';

SELECT  column1 ,
        column2 ,
        column3 ,
        column4
FROM    MediumTable m
		INNER JOIN dbo.splitfuntion(@Column2value, ',') s ON
			s.OutputColumnNameFromSplitFunction = m.column2;

On second thoughts, what @ScottPletcher suggested might be better because if you have dups in the @Column2value, if you use my method, you will get duplicate output.


#4

Hi Scott,

Quick question. On the Where sf.column_name = mt.column2,

I dont see any other field except 1 on the SELECT statement of Select 1 From dbo.splitfuntion (@Column2value,',') sf

So, what is the sf.column_name ?

Thanks Scott


#5

Hi Scott,

Never mind, I got it...that is for the field in the split function.

Thanks Scott and James