May 6, 2009 at 12:24 pm
-- HERE IS THE PROBLEM.. I CAN NOT USE THE T-SQL (INSERT EXEC) IN A FUNCTION
-- ADDITIONALLY, WELL, I AM GETTING THIS INFORMATION FROM A "LINKED" SERVER
-- OF ADVANTAGE DATABASE SYSTEM (SYBASE) FLAVOR, SO, YOU SEE I HAVE TO USE THE OPENQUERY METHOD
-- TO GET THE INFORMATION BACK
-- ANY INFORMATION WILL HELP
Please see attached
thank you folks..
Cheers,
John Esraelo
May 6, 2009 at 12:28 pm
why can't you use a stored procedure instead of a function to get the data that you want?
you could execute a stored procedure and insert into your temp table, then join the temp table against other tables to get your data.
if you posted the code instead of the png image, we could copy and paste, and then adapt it.
Lowell
May 6, 2009 at 12:30 pm
You will need to import the data you need from the Sybase database to a temporary or staging table and then use that table in your function(s)/query(ies).
May 6, 2009 at 12:34 pm
Good point, although, if stored procedure can do and return what the "function caller" needs then sure..
here is the thing;
There is another application that calls this function:
" select a, b, c from UDF_GetEmp_ID(@EmpID) "
But, in case of the SP, is it not true that you can not use the "select" statement in above when calling an SP??
In another word.. sp_GetEmp_ID is the only phrase needed to get to run..well, of course the param too.
Cheers,
John Esraelo
May 6, 2009 at 12:38 pm
If I read you right, correct, you can't use a stored procedure in a query like a table. that's why you would need to load the data returned from a stored procedure into a table before you can query it. Same thing needs to be done here.
There may be a work around, but I'd have to do some research and I have to leave the office for a bit so I don't have time right now.
May 6, 2009 at 12:39 pm
Interesting observation my friend; however, I am trying to eliminate the "staging" area completely.
I do have a visual foxpro 5 second priodical asynchronization data going to sql from VFP folder(s) and there are some issues with that while upsetting the businessflow.
So, using (in the test enviornment of course) the Advantage Database System, which understands VFP tables, will allow the other applications to get the data directly from the VFP instead of going and getting the data from SQL tables. With a price; I will have to modify the existing SQL functions to fit the bill.
I am not sure if I made sense or not but please ask more questions, it has been a long day, sorry.
:))
Cheers,
John Esraelo
May 6, 2009 at 12:41 pm
Of course Lynn, thank you much!
Cheers,
John Esraelo
May 6, 2009 at 1:38 pm
Hi Lowel,
Here is the thing about the code, I did not know why the indentation were not the same as the "preview" here in the post zone.. however, I had removed all the TABs with spaces and still the code were left-justified.
I will try to repost it and hopefully is not going to give us a head-ache, more than we need, right?
thx a bunch!!
Cheers,
John Esraelo
May 6, 2009 at 2:24 pm
Would you please post the code for your tvf?
May 6, 2009 at 2:35 pm
-- Here are you and thank you again for your assistance::
--
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION ADV_UDF_GetEmp_ID
(
@EmpID nvarchar(10)
)
RETURNS
@tblGetEmp_ID TABLE
(
tIDint,
tLNamenvarchar(25),
tFNamenvarchar(25),
tSSNnvarchar(11),
tHireDatdatetime,
tStartdatedatetime,
tTermDatedatetime,
tStatusnvarchar(20),
tTerminatednvarchar(5),
tHirednvarchar(5),
tJobNumnvarchar(10),
tDivisionCodenvarchar(5),
tHRClassnvarchar(10)
)
AS
BEGIN
declare @Run as nvarchar(4000),
@Sel as nvarchar(2000),
@Where as nvarchar(1500),
@var as nvarchar(500),
@MyNum as nvarchar(20),
@MyNum_Middle as nvarchar(20)
-- I AM USING THE NEXT TWO LINE FOR TESTING PURPOSES AND DO NOT AFFECT WHAT WE ARE DISCUSSING
-- THE PROBLEM IS SOMETHING ELSE AND NOT PASSING A VARIABLE HERE.
select @MyNum = '''''22026'''
select @MyNum_Middle = '''''22026'''''
select @Run = 'select * from openquery(ads_link1111, '
select @Sel = ' ''
SELECT[BRGEMP].[ID],
ltrim(rtrim([BRGEMP].[LNAME])) ,
case lenGTH([BRGEMP].[flname])
When 0 then [BRGEMP].[FNAME]
else [BRGEMP].[FLNAME]
end as "FName",
ltrim(rtrim([BRGEMP].[SSN])) "SSN",
convert([BRGAPPL].[HIREDATE], sql_date) "HireDate",
convert([BRGAPPL].[STARTDATE], sql_date) "StartDate",
convert([BRGAPPL].[TERMDATE], sql_date) "TermDate",
case BRGEMP.STATUS
when 1 then ''''Applicant''''
when 2 then ''''Terminated''''
when 3 then ''''Active''''
when 4 then ''''Unknown''''
end ,
BRGAPPL.TERMINATED,
BRGAPPL.HIRED,
'''''''' AS JobNum,
(select
from [elsoffi]
where [elsoffi]. = (select [p_elsoffi]
from [brgemp]
where (ltrim(rtrim([brgemp].[ID])) = ' + @MyNum_Middle + '))) DivisionCode,
CASE (select POSTYPE
from [brgpostp] p
where p. = ([brgemp].[p_brgpostp])
)
WHEN 1 THEN ''''FIELD''''
WHEN 2 THEN ''''OFFICE''''
WHEN 3 THEN ''''OFFICER''''
WHEN 4 THEN ''''SHOP''''
ELSE ''''UNKNOWN''''
end "HR_Class"
FROM
BRGEMP LEFT OUTER JOIN BRGAPPL ON [BRGEMP]. = [BRGAPPL].[P_BRGEMP] '
select @Where = ' where brgemp.id = '
select @Run = @Run + @Sel + @Where + @MyNum + ''''')'
Insert @tblGetEmp_ID
exec (@run)
RETURN
END
Cheers,
John Esraelo
May 6, 2009 at 2:49 pm
Not sure how performant this will be, and it may not be the best way to accomplish it.
create a view that accomplishes the openquery with NO criteria, you are asking that the view return all the data from the linked server. Use that view in your tvf and apply the criteria there. You should be able to do that without using dynamic SQL.
May 6, 2009 at 2:54 pm
At this stage; I do not have an issue nor worry about the performance and the criterion selections.
All I am interested in is getting the result-set back 😀
Cheers,
John Esraelo
May 8, 2009 at 10:38 am
Wow... I guess for the first time I am stuck between a rock and a hard place in a simple TSQL.
It seems like there should be a way to have and allow the actions to take place in User Defined Functions...
hnmmmmm it's only sounding logical right to have Insert, Update and Deletes not to mention calling another function with or without a parameter within any given function.. but then again.. what do I know right..
I know, I am sounding frustrated, becuase simply, it is frustrating..:w00t:
Any ideas will help, thx.
:alien:
Cheers,
John Esraelo
May 8, 2009 at 11:22 am
Does this mean creating a view using the OPENROWSET function didn't work? It is how I got around not being able to use NEWID() in a function, I put it in a view then called the view in my UDF.
May 8, 2009 at 11:49 am
I guess I need to re-visit my previous posting here::
> I get connected to Adv Database via "linked server object" in sql
> use openquery calling the "linked" server--> using--> local odbc using Advantage OLE
> I finally get the data all good in query analyzer
NOW:
I would like to build the function that I need so the "other" internal "application" running in parallel get get that data using something in the line of "select a, b, c from AdvLinkedData (MyParam)"
so the request comes from an App --> to --> sql server (staging) --> Advantage --> visual foxpro tables viola..
So in another word I NEED TO get the data in a form of a table and NOT Scalara or Inline funcion nor a stored procedure .. simply because you can not pass a "table" through an sp, NOW if we can then please let me know so I can finish this thing up.. actually I might try that right now..
😀
Cheers,
John Esraelo
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply