July 22, 2010 at 2:28 pm
Error : Cannot resolve collation conflict for UNION operation.
I getting these error becuase one query i using collate to join two table from different database and after union
i don't have use collat in query . how should i solved these problem
SELECTDISTINCT '100' AS CONO,
--OH.CUST_ID, OH.CO_NUMBER,
dbo.GetClosedCustomerOrder(OH.CO_NUMBER, dbo.GetCustomerOrderType(OH.CO_NUMBER)) ORNO,
--CX.M3_CustomerID CUNO,
ISNULL(CM.CustomerContactEmail, '') CUNO, dbo.GetCustomerOrderType(OH.CO_NUMBER) ORTP,
'TOR' FACI, 'TOR' WHLO, OH.CUST_PO_NO CUOR, 'NTX' TAXC, --CX.M3_CustomerID PYNO,
ISNULL(CM.CustomerContactEmail, '') PYNO,
dbo.fn_ConvertDateForM3(OH.ORD_CREATE_DATE) CUDT,
'CHK' PYCD, 'USD' CUCD,
ISNULL(SDL.PrintLanguage, '') ADID,
CASE SUBSTRING(ISNULL(CM.CustomerContactEmail, ''), 1, 1) WHEN '6' THEN '001' ELSE '' END BLID,
dbo.fn_ConvertDateForM3(OH.ORD_CREATE_DATE) ORDT, 'HSC' As DIVI
FROMFSDATASQLM39.dbo.SH_ORDHD OH, [FSDB-M39].dbo.FS_Customer CM,
[FSDB-M39].dbo.FS_ShipToDeliveryLocation SDL,
( SELECTCO_NUMBER, MIN(LN_STA) Status FROM FSDATASQLM39.dbo.SH_ORDDT Group By CO_NUMBER HAVING Min(LN_STA) > 4) COL
WHEREOH.ORD_CREATE_DATE >= Convert(DateTime, '2007-01-01 00:00:00')
ANDOH.CO_NUMBER = COL.CO_NUMBER
ANDOH.CUST_ID = CM.CustomerID COLLATE Latin1_General_BIN
ANDOH.SHIP_TO_ID = SDL.ShipToDeliveryLocationID COLLATE Latin1_General_BIN
ANDSDL.CustomerKey = CM.CustomerKey
ANDOH.CO_NUMBER NOT LIKE 'RA%'
ANDOH.CO_NUMBER NOT LIKE 'F%GST'
ANDUPPER(LTRIM(RTRIM(OH.CUST_PO_NO))) NOT LIKE '%LB-DEMAND%'
ANDCOL.Status NOT IN ('9')
ANDLTRIM(RTRIM(OH.CUST_ID)) COLLATE DATABASE_DEFAULT NOT IN ( Select Distinct LTRIM(RTRIM(CustomerID)) From EXCLUDED_HISTORY )
Group by OH.CO_NUMBER, CM.CustomerContactEmail, OH.CUST_PO_NO, OH.ORD_CREATE_DATE, SDL.PrintLanguage, CM.CustomerID,
OH.SHIP_TO_ID, CM.CustomerName, OH.CUST_ID, OH.CO_NUMBER
i using collate above query but in below query i don't have use collate
UNION
-- Open Min Max Orders having Closed Lines
SELECT'100' AS CONO,
--OH.CustomerID, OH.CONumber,
dbo.GetClosedCustomerOrder (OH.CONumber, dbo.GetCustomerOrderType(OH.CONumber)) ORNO,
--CX.M3_CustomerID CUNO,
ISNULL(CM.CustomerContactEmail, '') CUNO, dbo.GetCustomerOrderType(OH.CONumber) ORTP,
'TOR' FACI, 'TOR' WHLO, OH.CustomerPONumber CUOR, 'NTX' TAXC, --CX.M3_CustomerID PYNO,
ISNULL(CM.CustomerContactEmail, '') PYNO, dbo.fn_ConvertDateForM3(OH.COCreatedDate) CUDT,
'CHK' PYCD, CASE OH.COControllingCode WHEN 'F' THEN 'EUR' ELSE 'USD' END CUCD, --ISNull(AD.M3_ShiptoID,'') ADID,
CASE ISNULL(SDL.PrintLanguage, '') WHEN '' THEN '001' Else SDL.PrintLanguage End ADID,
IsNull(CM.CustomerClass8,'') BLID, dbo.fn_ConvertDateForM3(OH.COCreatedDate) ORDT, 'HSC' As DIVI
FROM[FSDB-M39].dbo.FS_COHeader OH, [FSDB-M39].dbo.FS_Customer CM,
[FSDB-M39].dbo.FS_ShipToDeliveryLocation SDL,
( SELECTDISTINCT COHeaderKey FROM [FSDB-M39].dbo.FS_COLine WHERE COLineStatus < 5 ) COL,
[FSDB-M39].dbo.FS_COLine CL
WHEREOH.CustomerID = CM.CustomerID COLLATE Latin1_General_BIN
ANDOH.COHeaderKey = COL.COHeaderKey
ANDOH.COHeaderKey = CL.COHeaderKey
ANDOH.ShipToDeliveryLocationKey = SDL.ShipToDeliveryLocationKey
AND(OH.CONumber LIKE '%M%' OR OH.CONumber LIKE '%Z' OR OH.CONumber LIKE '%H'
OR (OH.CONumber LIKE '%S'
AND SUBSTRING(OH.CONumber, LEN(OH.CONumber) - 1 , 1) <> 'O'
AND SUBSTRING(OH.CONumber, LEN(OH.CONumber) - 1 , 1) <> 'D'))
ANDCL.COLineStatus IN ('5', '6', '7', '8')
ANDUPPER(LTRIM(RTRIM(OH.CustomerPONumber))) NOT LIKE '%LB-DEMAND%'
ANDdbo.GetClosedCustomerOrder (OH.CONumber, dbo.GetCustomerOrderType(OH.CONumber)) = 'F56784M'
GROUP BY OH.CONumber, CM.CustomerContactEmail, OH.CustomerPONumber, OH.COCreatedDate, SDL.PrintLanguage,
CM.CustomerID, OH.ShipToDeliveryLocationID, CM.CustomerName, OH.CustomerID, CM.CustomerClass8,
OH.COControllingCode
July 22, 2010 at 3:07 pm
You need to have the same collation per column for each union statement.
Example
SELECT col1 collate Latin1_General_CS_AS, col2 collate Greek_CI_AS
FROM MyTable
WHERE condition
UNION ALL
SELECT col1 collate Latin1_General_CS_AS, col2 collate Greek_CI_AS
FROM MyTable2
WHERE condition
will work whereas the following won't
SELECT col1 collate Greek_CI_AS, col2 collate Latin1_General_CS_AS
FROM MyTable
WHERE condition
UNION ALL
SELECT col1 collate Latin1_General_CS_AS, col2 collate Greek_CI_AS
FROM MyTable2
WHERE condition
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply