October 1, 2008 at 9:39 am
Can anyone help explain a way to resolve the error I receive from the following statement:
-------------------------------------------------------------------------------------------------
select distinct a.jobid, a.custphoneno, a.dispatchregionid,a.originalduedate,a.timeofdump
,c.sector
,null as Action_Taken
,referaltime
from mcommopenorders a
join
(select phone ,referaltime
from occ_archives.dbo.tbl_R15analysis
where (replace(left(referaltime,8),'-','') >= convert(varchar,getdate()-1,12))
union
select custphoneno, eventdatetime
from occ.dbo.tbl_R15OrdersToday) as b
on a.custphoneno = b.phone
left join zones c
on a.Da collate Latin1_General_CI_AS = c.Dispatchareas collate Latin1_General_CI_AS
where (left(address,4)='!MC!')
and (pattern is null)
and (jobstatus in ('PENDING_DISPATCH','ASSIGNED'))
and (dispatchtypecategory ='SA-Cable'
or dispatchtypecategory is null)
and (workforcerequired = 'g')
and (convert(varchar,a.originalduedate,112) = convert(varchar,getdate(),112))
and (sector not like '%Aliant')
and (convert(varchar,a.assignedfordate,112) = convert(varchar,getdate(),112)or a.assignedfordate
is null)
-------------------------------------------------------------------------------------------------
Msg 468, Level 16, State 9, Procedure vu_R15_Potential2ndMisses, Line 3
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation.
October 1, 2008 at 9:45 am
Can you provide the DDL (create statement) for the tables including collation for character columns?
😎
October 1, 2008 at 10:07 am
Hi,
It's actually a View, not a table.
October 1, 2008 at 10:12 am
Still need the DDL for the under lying tables to the view.
😎
October 1, 2008 at 10:23 am
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MCommOpenOrders](
[JOBID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SERVICEORDERNUM] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DUEDATE] [datetime] NULL,
[ORIGINALDUEDATE] [datetime] NULL,
[JOBSTATUS] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DISPATCHUNITID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STATUSUPDATEDBY] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JOBTYPE] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DISPATCHREGIONID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WORKGROUP] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DA] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DURATION] [numeric](20, 0) NULL,
[APPTSTARTTIME] [datetime] NULL,
[APPTENDTIME] [datetime] NULL,
[NAME] [varchar](90) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ASSIGNEDFORDATE] [datetime] NULL,
[ADDRESS] [varchar](339) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CUSTPHONENO] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BCRIS_CALENDER] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CREATIONDATE] [datetime] NULL,
[TROUBLECATEGORY] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DISPATCHTYPECATEGORY] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WORKFORCEREQUIRED] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PATTERN] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SERVICEAFFECTING] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DISPATCHDATETIME] [datetime] NULL,
[TimeOfDump] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
-------------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_R15analysis](
[OriginalCt] [varchar](22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[R15Ct] [varchar](22) COLLATE Latin1_General_CI_AI NULL,
[Phone] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[jobtype] [varchar](22) COLLATE Latin1_General_CI_AI NULL,
[troublecategory] [varchar](22) COLLATE Latin1_General_CI_AI NULL,
[wfr] [varchar](6) COLLATE Latin1_General_CI_AI NULL,
[workgroup] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Region] [varchar](22) COLLATE Latin1_General_CI_AI NULL,
[originalduedate] [datetime] NULL,
[CurrentDuedate] [datetime] NULL,
[CurrentTech] [varchar](22) COLLATE Latin1_General_CI_AI NULL,
[Coded_By] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[referaltime] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Bell_Received] [datetime] NULL,
[DP1id] [varchar](5) COLLATE Latin1_General_CI_AI NULL,
[dp1tm] [datetime] NULL,
[dp2id] [varchar](5) COLLATE Latin1_General_CI_AI NULL,
[dp2tm] [datetime] NULL,
[CT] [datetime] NULL,
[DISP] [varchar](5) COLLATE Latin1_General_CI_AI NULL,
[timeofdump] [datetime] NULL,
[TroubleRcd] [varchar](10) COLLATE Latin1_General_CI_AI NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
-------------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_R15OrdersToday](
[ID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NPA] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CUSTPHONENO] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DUEDATE] [datetime] NULL,
[JOBTYPEID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TROUBLECATEGORY] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REASONCODE] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TECHID] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DISPATCHAREAID] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DISPATCHREGIONID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[WORKGRPID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CREATIONDATE] [datetime] NULL,
[TROUBLERECEIVEDDATE] [datetime] NULL,
[eventDateTime] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
October 2, 2008 at 2:12 pm
What about "zones" ?
October 3, 2008 at 1:17 pm
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ZONES](
[ZoneProfile] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[Dispatchareas] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[Dispatchregions] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[Sector] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[TierC_Group] [varchar](10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
October 3, 2008 at 1:42 pm
What are the collation settings for all the databases involved ?
October 7, 2008 at 9:21 am
There are two databases called OCC and OCC_ARCHIVES.
OCC has the collation setting Latin1_General_CI_AS
- tables MCommOpenOrders, tbl_R15OrdersToday and Zones are under this database
OCC_ARCHIVES has the collation setting Latin1_General_CI_AI
- table tbl_R15_analysis falls under this database
October 7, 2008 at 9:41 am
Try COLLATE function in your query,
October 7, 2008 at 9:44 am
where specifically in the query should i use the Collate function?
I already have it in the middle part of the query.
October 7, 2008 at 1:37 pm
SQLbeginner (10/7/2008)
where specifically in the query should i use the Collate function?I already have it in the middle part of the query.
Anywhere you're comparing or joining between the 2 databases. Think of it like converting 1 collation to the other. So if DB_A is Collation_A, and DB_B is Collation_B then your code will be:
select DB_A.dbo.TableA, DB_B.dbo.TableB
from DB_A.dbo.tableA TbA
join DB_B.dbo.tableB TbB on TbA.FldA collate Collation_B = TbB.FldB
That converts FldA to B's collation
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply