May 11, 2009 at 4:44 pm
What I haven't quite figured out is how the function is going to be used?
May 11, 2009 at 4:44 pm
I am getting
Msg 2010, Level 16, State 1, Procedure ADV_UDF_GetEmp_ID, Line 16
Cannot perform alter on 'ADV_UDF_GetEmp_ID' because it is an incompatible object type.
Does not let me modify the function::
alter FUNCTION ADV_UDF_GetEmp_ID
(
@Level1EmpID nvarchar(20)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
-- adv_usp_getemp_id @Level1EmpID
select * from myview
WHERE
myview.id = @level1empid
)
Cheers,
John Esraelo
May 11, 2009 at 4:49 pm
John Esraelo (5/11/2009)
I am gettingMsg 2010, Level 16, State 1, Procedure ADV_UDF_GetEmp_ID, Line 16
Cannot perform alter on 'ADV_UDF_GetEmp_ID' because it is an incompatible object type.
Does not let me modify the function::
alter FUNCTION ADV_UDF_GetEmp_ID
(
@Level1EmpID nvarchar(20)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
-- adv_usp_getemp_id @Level1EmpID
select * from myview
WHERE
myview.id = @level1empid
)
But the select from the view works just fine by itself?
May 11, 2009 at 4:50 pm
I have to leave. I have a soccer practice to run at 6:00.
May 11, 2009 at 4:51 pm
Of course, this is how all my problems begun;
everything that I wanted to do was fine except when bringing all that into a function and function in sql has limitations and restrictions.
Such as, of course you know, calling a procedures, exec a dynamic string, performing insert/update/delete, and couple more..
:((
I even removed the "where" clause and it still does not like it..
Cheers,
John Esraelo
May 11, 2009 at 4:53 pm
have fun..
and
thank you for your patience..
Cheers,
John Esraelo
May 12, 2009 at 12:29 am
You really haven't explained why it had to be a function. What is it you are trying to accomplish? How is(was) the function going to used? Depending on what you are trying to accomplish, it is possible that the use of a function is not what you need.
May 12, 2009 at 1:23 am
I thought I was very clear during the serveral reviewing and rewriting the scenario.
The reason for using the function is that the caller has to have a "select a, b, d from funcname(param)" and you can not have that with sp and you can not pass a param to a view.. so between the proc, func and view there is a nightmare that is kicking me in the rear..
is like a catch 22 all over.
Anyway, I got your method tired and brings in what I want.. but, there is no way that I can bring the POs and the Invoices in like that and then filter the entire joined tables in a function..
It's not practical and it's putting a dent into the network.. it's a good proof of concept for small tables and not necessary can be said for the half a dozen joined tables.
So, this is not going to work for us and we may have to rewrite the other applications function calls and methods or even drop Advantage if worse comes to worst..
anyway, I am not 100 percent disappointed and still have some umf left in me.. and who knows there may be some ways that I have not explored yet.
Cheers,
John Esraelo
May 12, 2009 at 6:56 am
John Esraelo (5/12/2009)
I thought I was very clear during the serveral reviewing and rewriting the scenario.The reason for using the function is that the caller has to have a "select a, b, d from funcname(param)" and you can not have that with sp and you can not pass a param to a view.. so between the proc, func and view there is a nightmare that is kicking me in the rear..
is like a catch 22 all over.
Anyway, I got your method tired and brings in what I want.. but, there is no way that I can bring the POs and the Invoices in like that and then filter the entire joined tables in a function..
It's not practical and it's putting a dent into the network.. it's a good proof of concept for small tables and not necessary can be said for the half a dozen joined tables.
So, this is not going to work for us and we may have to rewrite the other applications function calls and methods or even drop Advantage if worse comes to worst..
anyway, I am not 100 percent disappointed and still have some umf left in me.. and who knows there may be some ways that I have not explored yet.
Actually, no, you still haven't really explained what you are doing. You have given a sketchy idea of what is going on, but that is it. Is this something they do ad hoc? Is it a report? What?
May 12, 2009 at 8:54 am
What the business is doing with this function call is trivial; however, the concept behind calling a function with the current conditions is almost impossible and I think we might have been beating this one with a stick.. The dataset MUST be filtered on the Advantage database side and that's the end of that.
There was a time that a function caller could invoke a select statement with a filter / criteria and now it can not because we can not have a dynamic SQL or a string Exec in a function.. simple as that..
I really appreciate your help on this.
thx
Cheers,
John Esraelo
May 12, 2009 at 9:04 am
Okay, I understand your requirement that the data be filtered on the other side, but you aren't answering MY question. WHY does it have to be a function? How is it being used? Is it in a report that takes parameteres? Is it an ad hoc query? What? With all due respect, but you are stuck on one way of trying to do this and we need to see if there are other alternatives. I can't come up with any if I don't know how it is being used.
May 12, 2009 at 9:27 am
Are you messing with me?.. I think we should drop this at this point.
I do not have to explain any more, but just being nice, it does not matter if this is an ad hoc, for report, or for testing the dataset.. the bottom line is that there is a function call and we need to fulfill the requirement, that's all. This is not a perfect world and it is alright for some things not to be done the way we want them.. that is the reason why we have new additions and enhancements to some systems.
I would like to thank you again for your persistence in finding a solution.
thx
Cheers,
John Esraelo
May 12, 2009 at 9:47 am
Okay, I'm done. Just so you know, there is almost always another way to accomplish a task, you just have to be willing to look for it, and sometimes, that means throwing out everything you have done to this point.
As Jeff sometimes says on this site, sometimes you have to realize you are in box before you can think outside of it.
I am trying to help you come up with a solution to your problem, but not knowing the requirements (and saying it MUST be a function isn't a requirement, its someone tell you how to do something, not what they need) I can't.
May 12, 2009 at 10:10 am
The caller needs to get a table value PERIOD. Functions are the one that do that with the criteria and param.. that's all. but since we can not have "open_query" performed in a function then I am screwed.. I am really getting hit hard here on this site and I do not understand why..
I do not want to use:
> global temp tables
> temp tables
> static table being used as a temp table
> SPID_Key_table
not to mention the fact that VIEWs as you suggested DO NOT accept parameters since they are virtual tables.
I have explored every possible way, yes, more than you do and there is no solution without a cost, and yes you are right on that.. but, please stop telling me that we don't know what the requirements are.. I NEED A TABLE VALUE TO BE RETURNED THAT THE RELATED PROCESS (does not have to be a function) ACCEPTS A CRITERIA AND FILTERS ON THE LINKED SERVER (and NOT on in the function). Do you have an answer to this? If not then please give me a break.
thx
Cheers,
John Esraelo
May 12, 2009 at 10:20 am
John Esraelo (5/12/2009)
The caller needs to get a table value PERIOD.
How is it being called? Is it a Report? Is it an application? Is it an ad-hoc query?
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply