Cannot find either column “dba” or the user-defined function or aggregate “schema.functionName”, or the name is ambiguous

There comes a time when you have to decide what is more important to you, maintenance of the code or the speed at which it runs? The reason it is running slow is because UDF's get processed on a row-by-row basis - SQL Server performs best using set-based operations. There is no reason why you cant keep your UDF's as there may be time where it is more practical to use them than to expand out the query.

My advice is this: If your data set is small feel free to use the UDF's; but if you are working with a large data set then take the time to write, test and optimize the query to obtain the best results - it will benefit you in the long run when you don't have users complaining their system is slow.

As Heinz Z. did, I discovered the problem.

One of your function parameters is char, while you pass the string literal 'ANY', which is considered a varchar. The database engine looks for an overloaded version of the function with varchar parameters, but doesn't find it.

Solution is either

• changing function parameter to varchar

• or cast parameter to char in the function call:

  SELECT * 
  FROM TABLE(TESTDAT.FNREPORT(DATE('10/23/2013'), 
                              DATE('10/23/2013'), 
                              CAST('ANY' AS CHAR(3))
            )) AS T
  

If doesn't work, try in any case to remove all parameters from the function and see if they are the culprits. Then you can investigate deeper adding one by one, try to work on dates format, for example you can try to pass current date instead of 10/23/2013.

Also you must investigate on why it thinks that FNREPORT is a type and not a function...