July 23, 2009 at 11:41 pm
Ok. I ran it and got this error:
Msg 156, Level 15, State 1, Line 62
Incorrect syntax near the keyword 'where'.
July 23, 2009 at 11:43 pm
I think this is what will actually be required based on your original code.
SELECT
timesheet.ProjectId as Job_ID_Timesheet,
timesheet.TaskCode as Cost_Code_Timesheet,
timesheet.Name as Cost_Code_Description_Timesheet,
cast(null as varchar) as Job_ID_TRAF,
cast(null as varchar) as Cost_Code_TRAF,
cast(null as varchar) as Cost_Code_Description_TRAF,
'Replicon row without matching Wennsoft row' as ErrorMsg
FROM
[server-name].[Web TimeSheet].dbo.Task Timesheet
where
not exists ( select
1
from
[server-name\GP].traf.dbo.JC00701 traf
where
CAST(((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4)) as NVARCHAR(50)) COLLATE Latin1_General_CI_AS = Timesheet.TaskCode
and CAST(traf.Cost_Code_Description as NVARCHAR(255)) COLLATE Latin1_General_CI_AS = Timesheet.Name
and traf.WS_Job_Number = Timesheet.ProjectID)
and Timesheet.TimeEntryAllowed in (1)
and Timesheet.TaskCode is not null
UNION ALL
SELECT
cast(null as varchar) as Job_ID_Timesheet,
cast(null as varchar) as Cost_Code_Timesheet,
cast(null as varchar) as Cost_Code_Description_Timesheet,
WS_Job_Number as Job_ID_TRAF,
CAST((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4) as NVARCHAR(50)) COLLATE Latin1_General_CI_AS AS Cost_Code_TRAF,
CAST(traf.Cost_Code_Description as NVARCHAR(255)) COLLATE Latin1_General_CI_AS as Cost_Code_Description_TRAF,
'Wennsoft row without matching Replicon row' as errormsg
FROM
[server-name\GP].traf.dbo.JC00701 TRAF
where
not exists ( select
1
from
[server-name].[Web TimeSheet].dbo.Task Timesheet
where
CAST(((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4)) as NVARCHAR(50)) COLLATE Latin1_General_CI_AS = Timesheet.TaskCode
and CAST(traf.Cost_Code_Description as NVARCHAR(255)) COLLATE Latin1_General_CI_AS = Timesheet.Name
and traf.WS_Job_Number = Timesheet.ProjectID)
UNION ALL
SELECT
timesheet.ProjectID as Job_ID_Timesheet,
timesheet.TaskCode as Cost_Code_Timesheet,
timesheet.Name as Cost_Code_Description_Timesheet,
WS_Job_Number as Job_ID_TRAF,
CAST((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4) as NVARCHAR(50)) COLLATE Latin1_General_CI_AS AS Cost_Code_TRAF,
CAST(traf.Cost_Code_Description as NVARCHAR(255)) COLLATE Latin1_General_CI_AS as Cost_Code_Description_TRAF,
'Replicon equal to Wennsoft' as errormsg
FROM
[server-name].[Web TimeSheet].dbo.Task Timesheet
inner join [server-name\GP].traf.dbo.JC00701 TRAF
on (Timesheet.ProjectID = TRAF.WS_Job_Number and
CAST(((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4)) as NVARCHAR(50)) COLLATE Latin1_General_CI_AS = Timesheet.TaskCode
and CAST(traf.Cost_Code_Description as NVARCHAR(255)) COLLATE Latin1_General_CI_AS = Timesheet.Name
where
traf.WS_Job_Number = timesheet.ProjectID -- This is redundant due to INNER JOIN above and could be deleted
July 23, 2009 at 11:52 pm
I think I am down to my last error now:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '0802M10 ' to data type int.
July 23, 2009 at 11:52 pm
paula (7/23/2009)
Ok. I ran it and got this error:Msg 156, Level 15, State 1, Line 62
Incorrect syntax near the keyword 'where'.
Which one, my rewrite or your modifiy original code?
July 23, 2009 at 11:56 pm
Nevermind, I figured it out. Missing a closing paren.
SELECT
timesheet.ProjectId as Job_ID_Timesheet,
timesheet.TaskCode as Cost_Code_Timesheet,
timesheet.Name as Cost_Code_Description_Timesheet,
cast(null as varchar) as Job_ID_TRAF,
cast(null as varchar) as Cost_Code_TRAF,
cast(null as varchar) as Cost_Code_Description_TRAF,
'Replicon row without matching Wennsoft row' as ErrorMsg
FROM
dbo.Task Timesheet
where
not exists ( select
1
from
dbo.JC00701 traf
where
CAST(((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4)) as NVARCHAR(50)) COLLATE Latin1_General_CI_AS = Timesheet.TaskCode
and CAST(traf.Cost_Code_Description as NVARCHAR(255)) COLLATE Latin1_General_CI_AS = Timesheet.Name
and traf.WS_Job_Number = Timesheet.ProjectID)
and Timesheet.TimeEntryAllowed in (1)
and Timesheet.TaskCode is not null
UNION ALL
SELECT
cast(null as varchar) as Job_ID_Timesheet,
cast(null as varchar) as Cost_Code_Timesheet,
cast(null as varchar) as Cost_Code_Description_Timesheet,
WS_Job_Number as Job_ID_TRAF,
CAST((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4) as NVARCHAR(50)) COLLATE Latin1_General_CI_AS AS Cost_Code_TRAF,
CAST(traf.Cost_Code_Description as NVARCHAR(255)) COLLATE Latin1_General_CI_AS as Cost_Code_Description_TRAF,
'Wennsoft row without matching Replicon row' as errormsg
FROM
dbo.JC00701 TRAF
where
not exists ( select
1
from
dbo.Task Timesheet
where
CAST(((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4)) as NVARCHAR(50)) COLLATE Latin1_General_CI_AS = Timesheet.TaskCode
and CAST(traf.Cost_Code_Description as NVARCHAR(255)) COLLATE Latin1_General_CI_AS = Timesheet.Name
and traf.WS_Job_Number = Timesheet.ProjectID)
UNION ALL
SELECT
timesheet.ProjectID as Job_ID_Timesheet,
timesheet.TaskCode as Cost_Code_Timesheet,
timesheet.Name as Cost_Code_Description_Timesheet,
WS_Job_Number as Job_ID_TRAF,
CAST((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4) as NVARCHAR(50)) COLLATE Latin1_General_CI_AS AS Cost_Code_TRAF,
CAST(traf.Cost_Code_Description as NVARCHAR(255)) COLLATE Latin1_General_CI_AS as Cost_Code_Description_TRAF,
'Replicon equal to Wennsoft' as errormsg
FROM
dbo.Task Timesheet
inner join dbo.JC00701 TRAF
on (Timesheet.ProjectID = TRAF.WS_Job_Number and
CAST(((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4)) as NVARCHAR(50)) COLLATE Latin1_General_CI_AS = Timesheet.TaskCode
and CAST(traf.Cost_Code_Description as NVARCHAR(255)) COLLATE Latin1_General_CI_AS = Timesheet.Name)
where
traf.WS_Job_Number = timesheet.ProjectID -- This is redundant due to INNER JOIN above and could be deleted
July 23, 2009 at 11:57 pm
If you read the comment on that last WHERE clause, it can be dropped.
July 24, 2009 at 12:06 am
Looks like you need to cast Timesheet.ProjectID as a VARCHAR(17) when comparing it to traf.WS_Job_Number.
Which may also require the COLLATE Latin1_General_CI_AS on traf.WS_Job_Number.
July 24, 2009 at 12:14 am
Can you please show me how to do that? Sorry, I am new to all this.
Why have you got 17 in brackets.
July 24, 2009 at 12:17 am
paula (7/24/2009)
Can you please show me how to do that? Sorry, I am new to all this.Why have you got 17 in brackets.
It is in parens, not brackets. Look at the CAST functions I added to your code, that should guide you.
July 24, 2009 at 12:19 am
Hopefully I caught it in all the right places.
SELECT
timesheet.ProjectId as Job_ID_Timesheet,
timesheet.TaskCode as Cost_Code_Timesheet,
timesheet.Name as Cost_Code_Description_Timesheet,
cast(null as varchar) as Job_ID_TRAF,
cast(null as varchar) as Cost_Code_TRAF,
cast(null as varchar) as Cost_Code_Description_TRAF,
'Replicon row without matching Wennsoft row' as ErrorMsg
FROM
dbo.Task Timesheet
where
not exists ( select
1
from
dbo.JC00701 traf
where
CAST(((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4)) as NVARCHAR(50)) COLLATE Latin1_General_CI_AS = Timesheet.TaskCode
and CAST(traf.Cost_Code_Description as NVARCHAR(255)) COLLATE Latin1_General_CI_AS = Timesheet.Name
and traf.WS_Job_Number COLLATE Latin1_General_CI_AS = cast(Timesheet.ProjectID as VARCHAR(17))
and Timesheet.TimeEntryAllowed in (1)
and Timesheet.TaskCode is not null
UNION ALL
SELECT
cast(null as varchar) as Job_ID_Timesheet,
cast(null as varchar) as Cost_Code_Timesheet,
cast(null as varchar) as Cost_Code_Description_Timesheet,
WS_Job_Number as Job_ID_TRAF,
CAST((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4) as NVARCHAR(50)) COLLATE Latin1_General_CI_AS AS Cost_Code_TRAF,
CAST(traf.Cost_Code_Description as NVARCHAR(255)) COLLATE Latin1_General_CI_AS as Cost_Code_Description_TRAF,
'Wennsoft row without matching Replicon row' as errormsg
FROM
dbo.JC00701 TRAF
where
not exists ( select
1
from
dbo.Task Timesheet
where
CAST(((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4)) as NVARCHAR(50)) COLLATE Latin1_General_CI_AS = Timesheet.TaskCode
and CAST(traf.Cost_Code_Description as NVARCHAR(255)) COLLATE Latin1_General_CI_AS = Timesheet.Name
and traf.WS_Job_Number COLLATE Latin1_General_CI_AS = cast(Timesheet.ProjectID as VARCHAR(17))
UNION ALL
SELECT
timesheet.ProjectID as Job_ID_Timesheet,
timesheet.TaskCode as Cost_Code_Timesheet,
timesheet.Name as Cost_Code_Description_Timesheet,
WS_Job_Number as Job_ID_TRAF,
CAST((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4) as NVARCHAR(50)) COLLATE Latin1_General_CI_AS AS Cost_Code_TRAF,
CAST(traf.Cost_Code_Description as NVARCHAR(255)) COLLATE Latin1_General_CI_AS as Cost_Code_Description_TRAF,
'Replicon equal to Wennsoft' as errormsg
FROM
dbo.Task Timesheet
inner join dbo.JC00701 TRAF
on (traf.WS_Job_Number COLLATE Latin1_General_CI_AS = cast(Timesheet.ProjectID as VARCHAR(17) and
CAST(((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4)) as NVARCHAR(50)) COLLATE Latin1_General_CI_AS = Timesheet.TaskCode
and CAST(traf.Cost_Code_Description as NVARCHAR(255)) COLLATE Latin1_General_CI_AS = Timesheet.Name)
where
traf.WS_Job_Number COLLATE Latin1_General_CI_AS = cast(Timesheet.ProjectID as VARCHAR(17) -- This is redundant due to INNER JOIN above and could be deleted
July 24, 2009 at 12:20 am
OK. Thanks very much. I will have a try now.
July 24, 2009 at 12:24 am
I also made a couple of changes to my rewrite. I'd like you to test it out as well when you get a chance.
WITH TRAF (
Job_ID_TRAF,
Cost_Code_TRAF,
Cost_Code_Description_TRAF
) as (
SELECT
WS_Job_Number COLLATE Latin1_General_CI_AS,
CAST((Cost_Code_Number_1) + (Cost_Code_Number_2) +
(Cost_Code_Number_3) + (Cost_Code_Number_4) as NVARCHAR(50)) COLLATE Latin1_General_CI_AS,
CAST(Cost_Code_Description as NVARCHAR(255)) COLLATE Latin1_General_CI_AS
FROM
dbo.JC00701
),
Timesheet (
Job_ID_Timesheet,
Cost_Code_Timesheet,
Cost_Code_Description_Timesheet,
timeentryallowed_Timesheet
) as (
SELECT
cast(ProjectId as VARCHAR(17)),
TaskCode,
[Name],
TimeEntryAllowed
FROM
dbo.Task
)
SELECT
Job_ID_Timesheet,
Cost_Code_Timesheet,
Cost_Code_Description_Timesheet,
Job_ID_TRAF,
Cost_Code_TRAF,
Cost_Code_Description_TRAF,
CASE WHEN Job_ID_Timesheet is not null
AND Job_ID_TRAF is not null
THEN 'Replicon equal to Wennsoft'
WHEN Job_ID_TRAF is null
AND Job_ID_Timesheet is not null
THEN 'Replicon row without matching Wennsoft row'
WHEN Job_ID_Timesheet is null
AND Job_ID_TRAF is not null
THEN 'Wennsoft row without matching Replicon row'
ELSE 'We have a problem'
END as ErrorMsg
FROM
Timesheet
full outer join TRAF
on (Job_ID_Timesheet = Job_ID_TRAF
and Cost_Code_Timesheet = Cost_Code_TRAF
and Cost_Code_Description_Timesheet = Cost_Code_Description_TRAF)
WHERE
(Job_ID_TRAF is null
and timeentryallowed_Timesheet in (1)
and Cost_Code_Timesheet is not null)
or (Job_ID_TRAF is not null)
ORDER BY
CASE WHEN Job_ID_Timesheet is not null
AND Job_ID_TRAF is not null
THEN 3
WHEN Job_ID_TRAF is null
AND Job_ID_Timesheet is not null
THEN 1
WHEN Job_ID_Timesheet is null
AND Job_ID_TRAF is not null
THEN 2
ELSE 4
END asc;
July 24, 2009 at 12:28 am
OK. Sounds great.
July 24, 2009 at 12:46 am
Is this what you had in mind for the first part of the query looking at ProjectID:
SELECT
cast(timesheet.ProjectId as varchar(17)) as Job_ID_Timesheet,
timesheet.TaskCode as Cost_Code_Timesheet,
timesheet.Name as Cost_Code_Description_Timesheet,
cast(null as varchar) as Job_ID_TRAF,
cast(null as varchar) as Cost_Code_TRAF,
cast(null as varchar) as Cost_Code_Description_TRAF,
'Replicon row without matching Wennsoft row' as ErrorMsg
FROM [rts-sql1].[Web TimeSheet].dbo.Task Timesheet
where
not exists (select 1 from
[rts-sql1\GP].TRAF.dbo.JC00701 traf
where
CAST(((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4))
as NVARCHAR(50)) COLLATE Latin1_General_CI_AS = Timesheet.TaskCode
and CAST(traf.Cost_Code_Description as NVARCHAR(255))
COLLATE Latin1_General_CI_AS = Timesheet.Name
and traf.WS_Job_Number = Timesheet.ProjectID)
and Timesheet.TimeEntryAllowed in (1)
and Timesheet.TaskCode is not null
July 24, 2009 at 7:14 am
Yes, you also need the cast to varchar(17) for ProjectID in the select statement as well. Good catch, be sure to do it in the third query in the union as well.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply