July 23, 2009 at 7:02 pm
Hey there everyone,
I get the following error..... Please help me!!
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
This is my code!! Can someone please edit my code with the correct COLLATE statement so that it will work. I have been trying for days but it just doesn't work. Thanks heaps to everyone in advance.
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 ((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4))
= Timesheet.TaskCode
and traf.Cost_Code_Description = Timesheet.Name
and traf.WS_Job_Number = Timesheet.ProjectID)
and Timesheet.timeentryallowed in (1)
and Timesheet.TaskCode is not null
UNION ALL
SELECTcast(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,
(traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4) AS Cost_Code_TRAF,
traf.Cost_Code_Description 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 ((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4))
= Timesheet.TaskCode
and traf.Cost_Code_Description = Timesheet.Name
and traf.WS_Job_Number = Timesheet.ProjectID)
UNION ALL
SELECTtimesheet.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,
(traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4) AS Cost_Code_TRAF,
traf.Cost_Code_Description 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((traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4)) = timesheet.TaskCode
and traf.Cost_Code_Description = timesheet.Name
where traf.WS_Job_Number = timesheet.ProjectID
July 23, 2009 at 7:13 pm
Need the DDL (CREATE TABLE statements) for the tables involved. I'm not sure which columns are character columns and which aren't.
July 23, 2009 at 7:48 pm
Hi there,
Thanks you so much for the reply. Just to let you know I am looking to Create a View with this query. Just out of interest, is it possible to solve this issue purely with the COLLATE statement, and therefore without a Create Table statement. Here is the field information:
timesheet.ProjectID = int
timesheet.TaskCode = nvarchar
timesheet.Name = nvarchar
timesheet.timeentryallowed = bit
traf.Cost_Code_Number_1 = char
traf.Cost_Code_Number_2 = char
traf.Cost_Code_Number_3 = char
traf.Cost_Code_Number_4 = char
traf.Cost_Code_Description = char
traf.WS_Job_Number = char
Thanks again!!
Much Appreciated!!!
July 23, 2009 at 9:21 pm
I think I may see a way to improve this query but I am going to need to see the DDL including the collations for the tables. Please script the tables, and you can remove any fields not used in the queries. That will make them shorter and my job a bit easier.
July 23, 2009 at 9:34 pm
Thanks again for your help.
How do I get a copy of the DDL?
July 23, 2009 at 9:39 pm
In the Object Explorer in SSMS, right click on the table and just follow the menu items until you script the table to the clip board. From there paste it into a code block ([ code ] your code [ /code ]) with NO spaces inside the square brackets. Do that for each table. You can, after pasting the code, delete columns that are not included anywhere in the code we are working with. This will shorten the DDL scripts and make it easier to identify the appropriate columns.
July 23, 2009 at 9:41 pm
Tried that on a table on my system here at home. You will need to go into the scripts and add the collation for the character fields as appropriate in the DDL. Do that after you cut out the unneeded columns.
July 23, 2009 at 10:02 pm
Here it is for the task table:
USE [Web TimeSheet]
GO
/****** Object: Table [dbo].[Task] Script Date: 07/24/2009 13:55:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Task](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProjectId] [int] NOT NULL,
[RootId] [int] NULL,
[ParentId] [int] NULL,
[TimeEntryAllowed] [bit] NOT NULL,
[Name] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[Description] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[TaskCode] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[EstimatedCost] [money] NULL,
[EstimatedCostCurrencyId] [int] NULL,
[EstimatedHours] [numeric](19, 4) NULL,
[ActualHours] [numeric](19, 4) NULL,
[EstimatedExpenses] [money] NULL,
[EstimatedExpensesCurrencyId] [int] NULL,
[EntryStartDate] [datetime] NOT NULL,
[EntryEndDate] [datetime] NOT NULL,
[ExpenseEntryStartDate] [datetime] NOT NULL,
[ExpenseEntryEndDate] [datetime] NOT NULL,
[ClosedCount] [int] NOT NULL,
[HierarchyLevel] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[AssignmentFlag] [int] NULL,
[Billable] [int] NULL,
[LevelCount] [int] NOT NULL,
[ApprovalRequired] [bit] NOT NULL,
[Level1] [int] NULL,
[Level2] [int] NULL,
[Level3] [int] NULL,
[Level4] [int] NULL,
[Level5] [int] NULL,
[Level6] [int] NULL,
[Level7] [int] NULL,
[Level8] [int] NULL,
[Level9] [int] NULL,
[Level10] [int] NULL,
[EstimatedCost_RU] [money] NULL,
[EstimatedCostCurrencyId_RU] [int] NULL,
[EstimatedHours_RU] [numeric](19, 4) NULL,
[ActualHours_RU] [numeric](19, 4) NULL,
[EstimatedExpenses_RU] [money] NULL,
[EstimatedExpensesCurrencyId_RU] [int] NULL,
[Info1] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Info2] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Info3] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Info4] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Info5] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Info6] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Info7] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Info8] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Info9] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Info10] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[HierarchyTaskName] [ntext] COLLATE Latin1_General_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [Web TimeSheet]
GO
ALTER TABLE [dbo].[Task] WITH CHECK ADD CONSTRAINT [fTKEstimatedCostCurrencyId] FOREIGN KEY([EstimatedCostCurrencyId])
REFERENCES [dbo].[CurrencyInfo] ([Id])
GO
ALTER TABLE [dbo].[Task] WITH CHECK ADD CONSTRAINT [fTKEstimatedExpensesCurrencyId] FOREIGN KEY([EstimatedExpensesCurrencyId])
REFERENCES [dbo].[CurrencyInfo] ([Id])
GO
ALTER TABLE [dbo].[Task] WITH CHECK ADD CONSTRAINT [fTKParentId] FOREIGN KEY([ParentId])
REFERENCES [dbo].[Task] ([Id])
GO
ALTER TABLE [dbo].[Task] WITH CHECK ADD CONSTRAINT [fTKProjectId] FOREIGN KEY([ProjectId])
REFERENCES [dbo].[Project] ([Id])
GO
ALTER TABLE [dbo].[Task] WITH CHECK ADD CONSTRAINT [fTKRootId] FOREIGN KEY([RootId])
REFERENCES [dbo].[Task] ([Id])
Here it is for JC00701 table:
USE [TRAF]
GO
/****** Object: Table [dbo].[JC00701] Script Date: 07/24/2009 13:59:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[JC00701](
[WS_Job_Number] [char](17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Project_Number] [char](17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Cost_Code_Number_1] [char](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Cost_Code_Number_2] [char](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Cost_Code_Number_3] [char](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Cost_Code_Number_4] [char](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Cost_Code_Alias] [char](27) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Cost_Code_Description] [char](31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Cost_Element] [smallint] NOT NULL,
[WS_Manager_ID] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[WS_Inactive] [tinyint] NOT NULL,
[Profit_Type_Number] [smallint] NOT NULL,
[Profit_Amount] [numeric](19, 5) NOT NULL,
[Type_of_Transaction] [smallint] NOT NULL,
[Next_Subdivision_Type] [smallint] NOT NULL,
[WS_Account_Index_1] [int] NOT NULL,
[WS_Account_Index_2] [int] NOT NULL,
[Added_By_CO_Module] [tinyint] NOT NULL,
[Track_Production_Qty] [tinyint] NOT NULL,
[Cost_Code_Act_Cost_TTD] [numeric](19, 5) NOT NULL,
[Cost_Code_Act_Cost_YTD] [numeric](19, 5) NOT NULL,
[Actual_Units_TTD] [numeric](19, 5) NOT NULL,
[Cost_Code_Estimated_Cst] [numeric](19, 5) NOT NULL,
[Change_Order_Est_Cost] [numeric](19, 5) NOT NULL,
[Cost_Code_Rvsd_Est_Cost] [numeric](19, 5) NOT NULL,
[Cost_Code_Forecast_Cost] [numeric](19, 5) NOT NULL,
[Cost_Code_Rvsd_Forecast] [numeric](19, 5) NOT NULL,
[Forecasted_Units] [numeric](19, 5) NOT NULL,
[Committed_Cost] [numeric](19, 5) NOT NULL,
[Committed_Units] [numeric](19, 5) NOT NULL,
[Cost_Code_Est_Unit] [numeric](19, 5) NOT NULL,
[Estimated_Amt_Units] [numeric](19, 5) NOT NULL,
[Production_Estimate_Qty] [numeric](19, 5) NOT NULL,
[Production_Actual_Qty] [numeric](19, 5) NOT NULL,
[Production_Qty_Unit] [numeric](19, 5) NOT NULL,
[Production_Qty_Curr_Per] [numeric](19, 5) NOT NULL,
[Production_Best] [numeric](19, 5) NOT NULL,
[Production_Best_Date] [datetime] NOT NULL,
[Production_Measure_Code] [char](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OVHD1_UOM] [char](21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OVHD1_Type] [smallint] NOT NULL,
[OVHD1_Percent] [smallint] NOT NULL,
[OVHD1_Estimated_Units] [numeric](19, 5) NOT NULL,
[OVHD1_Actual_Units] [numeric](19, 5) NOT NULL,
[OVHD1_Forecasted_Units] [numeric](19, 5) NOT NULL,
[OVHD1_Amt_Per_Unit] [numeric](19, 5) NOT NULL,
[OVHD1_Estimated_Amount] [numeric](19, 5) NOT NULL,
[OVHD1_Actual_Amount] [numeric](19, 5) NOT NULL,
[OVHD1_Profit_Type] [smallint] NOT NULL,
[OVHD1_Profit_Amount] [numeric](19, 5) NOT NULL,
[OVHD2_UOM] [char](21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OVHD2_Type] [smallint] NOT NULL,
[OVHD2_Percent] [smallint] NOT NULL,
[OVHD2_Estimated_Units] [numeric](19, 5) NOT NULL,
[OVHD2_Actual_Units] [numeric](19, 5) NOT NULL,
[OVHD2_Forecasted_Units] [numeric](19, 5) NOT NULL,
[OVHD2_Amt_Per_Unit] [numeric](19, 5) NOT NULL,
[OVHD2_Estimated_Amount] [numeric](19, 5) NOT NULL,
[OVHD2_Actual_Amount] [numeric](19, 5) NOT NULL,
[OVHD2_Profit_Type] [smallint] NOT NULL,
[OVHD2_Profit_Amount] [numeric](19, 5) NOT NULL,
[Bill_Type] [smallint] NOT NULL,
[Sched_Completion_Date] [datetime] NOT NULL,
[ACTCOMPDATE] [datetime] NOT NULL,
[Schedule_Start_Date] [datetime] NOT NULL,
[ACTSTARTDATE] [datetime] NOT NULL,
[Billing_Schedule_Line] [char](9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Estimated_Measure_Code] [char](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Amount_Pct_Complete] [smallint] NOT NULL,
[Units_Pct_Complete] [smallint] NOT NULL,
[Production_Pct_Complete] [smallint] NOT NULL,
[Field_Pct_Complete] [smallint] NOT NULL,
[Posted_To] [tinyint] NOT NULL,
[WRKRCOMP] [char](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Change_Order_Est_Units] [numeric](19, 5) NOT NULL,
[Labor_Group_Name] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[USERID] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[User_Define_1] [char](31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[User_Define_2] [char](31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[USERDEF1] [char](21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[USERDEF2] [char](21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[User_Def_Integer_1] [int] NOT NULL,
[User_Def_Integer_2] [int] NOT NULL,
[User_Defined_Integer_3] [int] NOT NULL,
[User_Defined_Integer_4] [int] NOT NULL,
[User_Defined_Dollar_1] [numeric](19, 5) NOT NULL,
[User_Defined_Dollar_2] [numeric](19, 5) NOT NULL,
[User_Defined_Dollar_3] [numeric](19, 5) NOT NULL,
[User_Defined_Dollar_4] [numeric](19, 5) NOT NULL,
[USRDAT01] [datetime] NOT NULL,
[USRDAT02] [datetime] NOT NULL,
[User_Defined_Date_3] [datetime] NOT NULL,
[User_Defined_Date_4] [datetime] NOT NULL,
[User_Defined_CB_1] [tinyint] NOT NULL,
[User_Defined_CB_2] [tinyint] NOT NULL,
[User_Defined_CB_3] [tinyint] NOT NULL,
[User_Defined_CB_4] [tinyint] NOT NULL,
[VNDRNMBR] [char](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ITEMNMBR] [char](31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PORDNMBR] [char](21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CC_Rvsd_Forecast_Units] [numeric](19, 5) NOT NULL,
[Rate_Per_Unit] [numeric](19, 5) NOT NULL,
[Original_Estimate_Units] [numeric](19, 5) NOT NULL,
[Original_Forecast_Units] [numeric](19, 5) NOT NULL,
[Wennsoft_Affiliate] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Wennsoft_Branch] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Wennsoft_Region] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MODIFDT] [datetime] NOT NULL,
[Modified_Time] [datetime] NOT NULL,
[MDFUSRID] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SV_Language_ID] [smallint] NOT NULL,
[Time_Zone] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[WSReserved_CB1] [tinyint] NOT NULL,
[WSReserved_CB2] [tinyint] NOT NULL,
[WSReserved_CB3] [tinyint] NOT NULL,
[WSReserved_CB4] [tinyint] NOT NULL,
[WSReserved_CB5] [tinyint] NOT NULL,
[WSReserved_CB6] [tinyint] NOT NULL,
[WSReserved_CB7] [tinyint] NOT NULL,
[WSReserved_CB8] [tinyint] NOT NULL,
[WSReserved_CB9] [tinyint] NOT NULL,
[WSReserved_CB10] [tinyint] NOT NULL,
[WSReserved_STR1] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[WSReserved_STR2] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PKJC00701] PRIMARY KEY CLUSTERED
(
[WS_Job_Number] ASC,
[Cost_Element] ASC,
[Cost_Code_Number_1] ASC,
[Cost_Code_Number_2] ASC,
[Cost_Code_Number_3] ASC,
[Cost_Code_Number_4] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[WS_Job_Number]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[Project_Number]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[Cost_Code_Number_1]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[Cost_Code_Number_2]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[Cost_Code_Number_3]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[Cost_Code_Number_4]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[Cost_Code_Alias]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[Cost_Code_Description]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[Cost_Element]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[WS_Manager_ID]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[WS_Inactive]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[Profit_Type_Number]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Profit_Amount]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[Type_of_Transaction]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[Next_Subdivision_Type]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[WS_Account_Index_1]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[WS_Account_Index_2]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[Added_By_CO_Module]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[Track_Production_Qty]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Cost_Code_Act_Cost_TTD]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Cost_Code_Act_Cost_YTD]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Actual_Units_TTD]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Cost_Code_Estimated_Cst]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Change_Order_Est_Cost]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Cost_Code_Rvsd_Est_Cost]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Cost_Code_Forecast_Cost]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Cost_Code_Rvsd_Forecast]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Forecasted_Units]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Committed_Cost]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Committed_Units]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Cost_Code_Est_Unit]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Estimated_Amt_Units]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Production_Estimate_Qty]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Production_Actual_Qty]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Production_Qty_Unit]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Production_Qty_Curr_Per]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Production_Best]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[JC00701].[Production_Best_Date]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[Production_Measure_Code]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[OVHD1_UOM]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[OVHD1_Type]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[OVHD1_Percent]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[OVHD1_Estimated_Units]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[OVHD1_Actual_Units]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[OVHD1_Forecasted_Units]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[OVHD1_Amt_Per_Unit]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[OVHD1_Estimated_Amount]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[OVHD1_Actual_Amount]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[OVHD1_Profit_Type]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[OVHD1_Profit_Amount]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[OVHD2_UOM]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[OVHD2_Type]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[OVHD2_Percent]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[OVHD2_Estimated_Units]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[OVHD2_Actual_Units]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[OVHD2_Forecasted_Units]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[OVHD2_Amt_Per_Unit]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[OVHD2_Estimated_Amount]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[OVHD2_Actual_Amount]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[OVHD2_Profit_Type]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[OVHD2_Profit_Amount]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[Bill_Type]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[JC00701].[Sched_Completion_Date]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[JC00701].[ACTCOMPDATE]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[JC00701].[Schedule_Start_Date]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[JC00701].[ACTSTARTDATE]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[Billing_Schedule_Line]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[Estimated_Measure_Code]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[Amount_Pct_Complete]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[Units_Pct_Complete]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[Production_Pct_Complete]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[Field_Pct_Complete]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[Posted_To]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[WRKRCOMP]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Change_Order_Est_Units]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[Labor_Group_Name]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[USERID]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[User_Define_1]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[User_Define_2]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[USERDEF1]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[USERDEF2]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[User_Def_Integer_1]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[User_Def_Integer_2]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[User_Defined_Integer_3]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[User_Defined_Integer_4]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[User_Defined_Dollar_1]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[User_Defined_Dollar_2]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[User_Defined_Dollar_3]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[User_Defined_Dollar_4]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[JC00701].[USRDAT01]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[JC00701].[USRDAT02]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[JC00701].[User_Defined_Date_3]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[JC00701].[User_Defined_Date_4]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[User_Defined_CB_1]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[User_Defined_CB_2]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[User_Defined_CB_3]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[User_Defined_CB_4]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[VNDRNMBR]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[ITEMNMBR]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[PORDNMBR]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[CC_Rvsd_Forecast_Units]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Rate_Per_Unit]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Original_Estimate_Units]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[JC00701].[Original_Forecast_Units]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[Wennsoft_Affiliate]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[Wennsoft_Branch]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[Wennsoft_Region]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[JC00701].[MODIFDT]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[JC00701].[Modified_Time]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[MDFUSRID]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[SV_Language_ID]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[Time_Zone]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[WSReserved_CB1]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[WSReserved_CB2]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[WSReserved_CB3]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[WSReserved_CB4]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[WSReserved_CB5]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[WSReserved_CB6]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[WSReserved_CB7]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[WSReserved_CB8]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[WSReserved_CB9]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[JC00701].[WSReserved_CB10]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[WSReserved_STR1]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[JC00701].[WSReserved_STR2]' , @futureonly='futureonly'
GO
USE [TRAF]
GO
ALTER TABLE [dbo].[JC00701] WITH CHECK ADD CHECK ((datepart(hour,[ACTCOMPDATE])=(0) AND datepart(minute,[ACTCOMPDATE])=(0) AND datepart(second,[ACTCOMPDATE])=(0) AND datepart(millisecond,[ACTCOMPDATE])=(0)))
GO
ALTER TABLE [dbo].[JC00701] WITH CHECK ADD CHECK ((datepart(hour,[ACTSTARTDATE])=(0) AND datepart(minute,[ACTSTARTDATE])=(0) AND datepart(second,[ACTSTARTDATE])=(0) AND datepart(millisecond,[ACTSTARTDATE])=(0)))
GO
ALTER TABLE [dbo].[JC00701] WITH CHECK ADD CHECK ((datepart(hour,[MODIFDT])=(0) AND datepart(minute,[MODIFDT])=(0) AND datepart(second,[MODIFDT])=(0) AND datepart(millisecond,[MODIFDT])=(0)))
GO
ALTER TABLE [dbo].[JC00701] WITH CHECK ADD CHECK ((datepart(day,[Modified_Time])=(1) AND datepart(month,[Modified_Time])=(1) AND datepart(year,[Modified_Time])=(1900)))
GO
ALTER TABLE [dbo].[JC00701] WITH CHECK ADD CHECK ((datepart(hour,[Production_Best_Date])=(0) AND datepart(minute,[Production_Best_Date])=(0) AND datepart(second,[Production_Best_Date])=(0) AND datepart(millisecond,[Production_Best_Date])=(0)))
GO
ALTER TABLE [dbo].[JC00701] WITH CHECK ADD CHECK ((datepart(hour,[Sched_Completion_Date])=(0) AND datepart(minute,[Sched_Completion_Date])=(0) AND datepart(second,[Sched_Completion_Date])=(0) AND datepart(millisecond,[Sched_Completion_Date])=(0)))
GO
ALTER TABLE [dbo].[JC00701] WITH CHECK ADD CHECK ((datepart(hour,[Schedule_Start_Date])=(0) AND datepart(minute,[Schedule_Start_Date])=(0) AND datepart(second,[Schedule_Start_Date])=(0) AND datepart(millisecond,[Schedule_Start_Date])=(0)))
GO
ALTER TABLE [dbo].[JC00701] WITH CHECK ADD CHECK ((datepart(hour,[User_Defined_Date_3])=(0) AND datepart(minute,[User_Defined_Date_3])=(0) AND datepart(second,[User_Defined_Date_3])=(0) AND datepart(millisecond,[User_Defined_Date_3])=(0)))
GO
ALTER TABLE [dbo].[JC00701] WITH CHECK ADD CHECK ((datepart(hour,[User_Defined_Date_4])=(0) AND datepart(minute,[User_Defined_Date_4])=(0) AND datepart(second,[User_Defined_Date_4])=(0) AND datepart(millisecond,[User_Defined_Date_4])=(0)))
GO
ALTER TABLE [dbo].[JC00701] WITH CHECK ADD CHECK ((datepart(hour,[USRDAT01])=(0) AND datepart(minute,[USRDAT01])=(0) AND datepart(second,[USRDAT01])=(0) AND datepart(millisecond,[USRDAT01])=(0)))
GO
ALTER TABLE [dbo].[JC00701] WITH CHECK ADD CHECK ((datepart(hour,[USRDAT02])=(0) AND datepart(minute,[USRDAT02])=(0) AND datepart(second,[USRDAT02])=(0) AND datepart(millisecond,[USRDAT02])=(0)))
July 23, 2009 at 11:07 pm
Okay, the following code parses and runs (with no result set generated, as I have no test data) in my sandbox database. You will need to make the appropriate changes to the FROM clause in each of the CTE definitions for the appropriate database tables.
Give this code a shot in a test environment. Let us know if it works or not. If not, I know what changes need to be made to your existing code to make it work. I think this code is a bit cleaner and hopefully easier to understand.
WITH TRAF (
Job_ID_TRAF,
Cost_Code_TRAF,
Cost_Code_Description_TRAF
) as (
SELECT
WS_Job_Number,
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 -- Modify to the appropriate database table
),
Timesheet (
Job_ID_Timesheet,
Cost_Code_Timesheet,
Cost_Code_Description_Timesheet,
timeentryallowed_Timesheet
) as (
SELECT
ProjectId,
TaskCode,
[Name],
TimeEntryAllowed
FROM
dbo.Task -- Modify to the appropriate database table
)
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
THEN 'Replicon row without matching Wennsoft row'
WHEN Job_ID_Timesheet is 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)
July 23, 2009 at 11:13 pm
WOW!! Thank you so much for spending the time doing that.
Just a quick question before I run it..... Does this code do any writing to the database or is it just reading data?
July 23, 2009 at 11:17 pm
Just reads. I didn't see any UPDATE statement in your previous posts.
July 23, 2009 at 11:18 pm
If the data looks okay, we may need to add an ORDER BY clause to the code as well. I'm not sure how you'd want it sorted.
July 23, 2009 at 11:24 pm
Just another question if I may. I would be interested if you don't mind having a look at what changes would need to be made to my existing code.
Thanks again!!!
July 23, 2009 at 11:32 pm
Here is your modified 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,
(traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4) AS Cost_Code_TRAF,
traf.Cost_Code_Description 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,
(traf.Cost_Code_Number_1) + (traf.Cost_Code_Number_2) +
(traf.Cost_Code_Number_3) + (traf.Cost_Code_Number_4) AS Cost_Code_TRAF,
traf.Cost_Code_Description 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
I haven't tested it as I would need to make additional adjustments as my system is set up case sensitive, so ProjectId ProjectID and I will get syntax errors trying to parse it.
July 23, 2009 at 11:39 pm
Sorry, I may need to make a few more adjustments to your original code.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply