July 27, 2009 at 7:17 pm
Can someone please help. I get the following error message. 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.
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)))
July 27, 2009 at 8:13 pm
Just a guess, but I think your parenthesis are likely out of whack. Have you tried commenting out the last union all Select to see if that is the one causing the problem?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 27, 2009 at 8:58 pm
Hi there. Thanks for your help.
I just tried that and then get this error:
Msg 102, Level 15, State 1, Line 61
Incorrect syntax near 'Latin1_General_CI_AS'.
Line 61 is the last row of the second query.
July 27, 2009 at 9:07 pm
Yup, you are missing a closing parentheses at that point. You have not closed the NOT EXISTS sub-query.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 27, 2009 at 9:17 pm
Hi there. I am getting a little confused. Here is my latest version:
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 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 [rts-sql1\GP].TRAF.dbo.JC00701 TRAF
where
not exists (select 1 from [rts-sql1].[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 COLLATE Latin1_General_CI_AS
= CAST(Timesheet.ProjectID as VARCHAR(17))
UNION ALL
SELECT
CAST(timesheet.ProjectId as varchar(17)) as Job_ID_Timesheet,
timesheet.TaskCode as Cost_Code_Timesheet,
timesheet.Name as Cost_Code_Description_Timesheet,
traf.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
[rts-sql1].[Web TimeSheet].dbo.Task Timesheet
inner join [rts-sql1\GP].TRAF.dbo.JC00701 TRAF
on CAST(Timesheet.ProjectID as varchar(17))
= TRAF.WS_Job_Number COLLATE Latin1_General_CI_AS
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
The error which belongs to the last row is as follows:
Msg 102, Level 15, State 1, Line 96
Incorrect syntax near 'Name'.
July 27, 2009 at 11:23 pm
Hey there,
Thanks very much for your help. It all works now!!
July 27, 2009 at 11:25 pm
Hey there,
Thanks very much for your help. It all works now!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply