June 15, 2006 at 10:01 am
running this statement
INSERT INTO PS_JWI_GENRPT_TMP1
select R.BUSINESS_UNIT,
R.PROJECT_ID,
R.EMPLID,
CASE when J.JOBCODE in ('100061','200061', '400061', '100064', '200064', '100073', '200073', '100070', '200070') THEN 'Proj+' when J.JOBCODE in ('100062','200062', '400062', '100065', '200065', '100074', '200074', '100071', '200071') THEN 'Proj' ELSE J.JOBCODE end as JOB,
SUM(R.RESQTY) as HOURS,
SUM(R.REVENUE) as REVENUE
from SCOR0116.Production.dbo.REVENUE R, SCOR0013.HR8D.dbo.PS_JOB J
where R.TRANS_DT between '01/01/2006' and '04/18/2006' and
R.EMPLID = J.EMPLID and
J.EFFDT = (select MAX(J1.EFFDT) from SCOR0013.HR8D.dbo.PS_JOB J1
where J.EMPLID = J1.EMPLID and
J.EMPL_RCD = J1.EMPL_RCD and
J.EFFSEQ = J1.EFFSEQ)and J.EFFSEQ = 0 and
J.JOBCODE in ('100061','200061','400061','100064','200064','100073','200073','100070','200070','100062','200062','400062','100065','200065','100074','200074','100071','200071')
group by R.BUSINESS_UNIT, R.PROJECT_ID, R.EMPLID, J.JOBCODE
3 total linked servers the select runs but it does not run with the insert statement get an error
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'J' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'J' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'J' does not match with a table name or alias name used in the query.
Any Ideas?????????????
June 15, 2006 at 10:30 am
Hi,
Note : The corrections to be made in red
INSERT INTO PS_JWI_GENRPT_TMP1
select R.BUSINESS_UNIT,
R.PROJECT_ID,
R.EMPLID,
CASE when J.JOBCODE in ('100061','200061', '400061', '100064', '200064', '100073', '200073', '100070', '200070') THEN 'Proj+' when J.JOBCODE in ('100062','200062', '400062', '100065', '200065', '100074', '200074', '100071', '200071') THEN 'Proj' ELSE J.JOBCODE end as JOB,
SUM(R.RESQTY) as HOURS,
SUM(R.REVENUE) as REVENUE
from SCOR0116.Production.dbo.REVENUE R, SCOR0013.HR8D.dbo.PS_JOB J
where R.TRANS_DT between '01/01/2006' and '04/18/2006' and
R.EMPLID = J.EMPLID and
J.EFFDT Exists (select MAX(J1.EFFDT) from SCOR0013.HR8D.dbo.PS_JOB J1
where J.EMPLID = J1.EMPLID and
J.EMPL_RCD = J1.EMPL_RCD and
J.EFFSEQ = J1.EFFSEQ)and J.EFFSEQ = 0 and
J.JOBCODE in ('100061','200061','400061','100064','200064','100073','200073','100070','200070','100062','200062','400062','100065','200065','100074','200074','100071','200071')
group by R.BUSINESS_UNIT, R.PROJECT_ID, R.EMPLID, J.JOBCODE
Regards,
Amit Gupta
MCDBA
June 15, 2006 at 11:03 am
Your EXISTS is invalid syntax, and even if correct, would not give an equivalent resultset.
Also note the original problem:
>>3 total linked servers the select runs but it does not run with the insert statement get an error
The SELECT runs on its own, but only errors when used in conjunction with an INSERT.
I would suggest changing the sub-select in the WHERE to a derived table:
INNER JOIN
(
SELECT EMPLID, EMPL_RCD, EFFSEQ, MAX(J1.EFFDT) As MaxEffDT
FROM SCOR0013.HR8D.dbo.PS_JOB
GROUP BY EMPLID, EMPL_RCD, EFFSEQ
) dt
On ( J.EMPLID = dt.EMPLID and
J.EMPL_RCD = dt.EMPL_RCD and
J.EFFSEQ = dt.EFFSEQ and
J.EFFDT = dt.MaxEffDT )
June 15, 2006 at 12:38 pm
Thanks for the suggestion PW but the funny thing is this statement works fine exactly the way it is in our production environment except we are linking only two servers the HR is not on a different server. Even more confusing the below statement structured the same that links 3 servers also runs without a problem.
INSERT INTO PS_JWI_GENRPT_TMP1
select J.LOCATION,
R.PROJECT_ID,
R.EMPLID,
CASE when J.JOBCODE in ('100061','200061', '400061', '100064', '200064', '100073', '200073', '100070', '200070') THEN 'Proj+' when J.JOBCODE in ('100062','200062', '400062', '100065', '200065', '100074', '200074', '100071', '200071') THEN 'Proj' ELSE J.JOBCODE end as JOB,
SUM(R.HOURS)AS HOURS,
SUM(R.COST) AS COST
from SCOR0019.HR8D.dbo.PS_JOB J, SCOR0116.Production.dbo.COSTING R
where R.DATE_UNDER_RPT between '01/01/2006' and '04/18/2006' and
R.EMPLID = J.EMPLID and
J.EFFDT = (select MAX(J1.EFFDT) from SCOR0019.HR8D.dbo.PS_JOB J1
where J.EMPLID = J1.EMPLID and
R.TIME_RPTG_CD in ('REG','OT') and
J.EMPL_RCD = J1.EMPL_RCD and
J.EFFSEQ = J1.EFFSEQ) and
J.EFFSEQ = 0 and
J.JOBCODE in ('100061','200061','400061','100064','200064','100073','200073','100070','200070','100062','200062','400062','100065','200065','100074', '200074','100071','200071')
group by J.LOCATION, R.PROJECT_ID, R.EMPLID, J.JOBCODE
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply