May 11, 2009 at 3:21 pm
Is the following always true, @MyNum and @MyNum_Middle always the same value?
select @MyNum = '''''22026'''
select @MyNum_Middle = '''''22026'''''
May 11, 2009 at 3:23 pm
yes, that is not a problem I was experimenting something.. but they will be and should be using the @emp_id that comes in as a param..
Cheers,
John Esraelo
May 11, 2009 at 3:32 pm
John Esraelo (5/11/2009)
yes, that is not a problem I was experimenting something.. but they will be and should be using the @emp_id that comes in as a param..
Didn't say it was a problem, but looking at your query, it can be rewritten to eliminate a nice piece of RBAR in the select statement by adding a table in the FROM clause.
May 11, 2009 at 3:36 pm
I am on a conference call with the development team and my boss about this issue and see if I have job by the end of the day .. hehehe :w00t:
This may go to an hour or so...
Cheers,
John Esraelo
May 11, 2009 at 3:44 pm
While you are on the phone, can you tell me if this query returns the expected result set?
select
*
from
openquery(ads_link1111,
'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,
e. DivisionCode,
CASE p.POSTYPE
WHEN 1 THEN ''FIELD''
WHEN 2 THEN ''OFFICE''
WHEN 3 THEN ''OFFICER''
WHEN 4 THEN ''SHOP''
ELSE ''UNKNOWN''
end "HR_Class"
FROM
BRGEMP
INNER JOIN [elsoffi] e
on (e. = [BRGEMP].[p_elsoffi])
INNER JOIN [brgpostp] p
on (p. = [brgemp].[p_brgpostp])
LEFT OUTER JOIN BRGAPPL
ON [BRGEMP]. = [BRGAPPL].[P_BRGEMP]
')
;
You can try the following if you want to reduce the size of what is returned:
select
*
from
openquery(ads_link1111,
'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,
e. DivisionCode,
CASE p.POSTYPE
WHEN 1 THEN ''FIELD''
WHEN 2 THEN ''OFFICE''
WHEN 3 THEN ''OFFICER''
WHEN 4 THEN ''SHOP''
ELSE ''UNKNOWN''
end "HR_Class"
FROM
BRGEMP
INNER JOIN [elsoffi] e
on (e. = [BRGEMP].[p_elsoffi])
INNER JOIN [brgpostp] p
on (p. = [brgemp].[p_brgpostp])
LEFT OUTER JOIN BRGAPPL
ON [BRGEMP]. = [BRGAPPL].[P_BRGEMP]
WHERE
brgemp.id = 22026
')
;
May 11, 2009 at 3:53 pm
I modified a little and it runs and returns two records...
select
*
from
openquery(ads_link1111,
'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,
e. DivisionCode,
CASE p.POSTYPE
WHEN 1 THEN ''FIELD''
WHEN 2 THEN ''OFFICE''
WHEN 3 THEN ''OFFICER''
WHEN 4 THEN ''SHOP''
ELSE ''UNKNOWN''
end "HR_Class"
FROM
BRGEMP
INNER JOIN [elsoffi] e
on (e. = [BRGEMP].[p_elsoffi])
INNER JOIN [brgpostp] p
on (p. = [brgemp].[p_brgpostp])
LEFT OUTER JOIN BRGAPPL
ON [BRGEMP]. = [BRGAPPL].[P_BRGEMP]
WHERE
brgemp.id = ''22026''
')
Cheers,
John Esraelo
May 11, 2009 at 3:57 pm
Here is alittle more code, make what ever changes you need to make.
create view dbo.EmpData
as
select
*
from
openquery(ads_link1111,
'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 as "Status",
BRGAPPL.TERMINATED,
BRGAPPL.HIRED,
'' AS JobNum,
e. DivisionCode,
CASE p.POSTYPE
WHEN 1 THEN ''FIELD''
WHEN 2 THEN ''OFFICE''
WHEN 3 THEN ''OFFICER''
WHEN 4 THEN ''SHOP''
ELSE ''UNKNOWN''
end "HR_Class"
FROM
BRGEMP
INNER JOIN [elsoffi] e
on (e. = [BRGEMP].[p_elsoffi])
INNER JOIN [brgpostp] p
on (p. = [brgemp].[p_brgpostp])
LEFT OUTER JOIN BRGAPPL
ON [BRGEMP]. = [BRGAPPL].[P_BRGEMP]
')
;
GO
SELECT
ed.*
FROM
dbo.EmpData ed
WHERE
ed.ID = '22026'
GO
CREATE FUNCTION dbo.EmployeeData(
@EmpID nvarchar(20)
)
RETURNS table
AS
RETURN(
SELECT
ed.*
FROM
dbo.EmpData ed
WHERE
ed.ID = @EmpID
);
GO
select * from dbo.EmployeeData(N'22026');
GO
May 11, 2009 at 3:58 pm
John Esraelo (5/11/2009)
I modified a little and it runs and returns two records...
select
*
from
openquery(ads_link1111,
'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,
e.
DivisionCode,CASE p.POSTYPE
WHEN 1 THEN ''FIELD''
WHEN 2 THEN ''OFFICE''
WHEN 3 THEN ''OFFICER''
WHEN 4 THEN ''SHOP''
ELSE ''UNKNOWN''
end "HR_Class"
FROM
BRGEMP
INNER JOIN [elsoffi] e
on (e. = [BRGEMP].[p_elsoffi])
INNER JOIN [brgpostp] p
on (p. = [brgemp].[p_brgpostp])
LEFT OUTER JOIN BRGAPPL
ON [BRGEMP]. = [BRGAPPL].[P_BRGEMP]
WHERE
brgemp.id = ''22026''
')
Were the results correct??
May 11, 2009 at 4:04 pm
would this go inside the view also:
GO
SELECT
ed.*
FROM
dbo.EmpData ed
WHERE
ed.ID = '22026'
GO
Cheers,
John Esraelo
May 11, 2009 at 4:05 pm
John Esraelo (5/11/2009)
would this go inside the view also:
GO
SELECT
ed.*
FROM
dbo.EmpData ed
WHERE
ed.ID = '22026'
GO
No. This is testing the view.
May 11, 2009 at 4:07 pm
I see 🙂
Cheers,
John Esraelo
May 11, 2009 at 4:11 pm
So, what is happening?? Still on the conference call?
May 11, 2009 at 4:22 pm
There are highly interested in this issue and I wrapped up my stuff and hoping to finish this thing pdq..
:))
Cheers,
John Esraelo
May 11, 2009 at 4:39 pm
As I expected; the test for the view takes 20+ second to run and I have not tested it from running a function yet.. that's next
Cheers,
John Esraelo
May 11, 2009 at 4:43 pm
John Esraelo (5/11/2009)
As I expected; the test for the view takes 20+ second to run and I have not tested it from running a function yet.. that's next
The function should run about the same.
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply