July 26, 2009 at 5:11 pm
Why is it the number 17 with varchar and not 16 or 18,19 etc.
July 26, 2009 at 7:52 pm
It may take me some more time to try out your code because I need to understand it first.
I hope you don't mind waiting.
July 26, 2009 at 8:42 pm
paula (7/26/2009)
Why is it the number 17 with varchar and not 16 or 18,19 etc.
Because, WS_Job_Number is defined as [char](17) and ProjectId is defined as an int, therefore I wanted to be sure it was large enough to hols the entire WS_JOB_Number value.
July 27, 2009 at 12:38 am
Hi there again. I have been fixing up the code and I am still getting errors. The latest error is:
Msg 205, Level 16, State 1, Line 3
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Here is the updated code:
SELECT
CAST(timesheet.ProjectId as varchar(17))COLLATE Latin1_General_CI_AS 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].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 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 [server-name].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 = CAST(Timesheet.ProjectID as VARCHAR(17))
COLLATE Latin1_General_CI_AS
UNION ALL
SELECT
CAST(timesheet.ProjectId as varchar(17))COLLATE Latin1_General_CI_AS
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].TRAF.dbo.JC00701 TRAF
on (CAST(Timesheet.ProjectID as varchar(17)) COLLATE Latin1_General_CI_AS
= 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)))
I am not really sure why this error is coming as I have checked that each query had 7 columns. If you have any suggestions I would really appreciate it.
July 27, 2009 at 11:04 pm
Hi There,
I have finally worked it out. A big thanks to you for all of your help.
Now I just need to test it to make sure that the data it is giving me is what I expect.
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply