Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

collate Expand / Collapse
Author
Message
Posted Thursday, July 22, 2010 2:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 23, 2010 10:16 AM
Points: 5, Visits: 24
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


SELECT DISTINCT '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
FROM FSDATASQLM39.dbo.SH_ORDHD OH, [FSDB-M39].dbo.FS_Customer CM,
[FSDB-M39].dbo.FS_ShipToDeliveryLocation SDL,
( SELECT CO_NUMBER, MIN(LN_STA) Status FROM FSDATASQLM39.dbo.SH_ORDDT Group By CO_NUMBER HAVING Min(LN_STA) > 4) COL
WHERE OH.ORD_CREATE_DATE >= Convert(DateTime, '2007-01-01 00:00:00')
AND OH.CO_NUMBER = COL.CO_NUMBER
AND OH.CUST_ID = CM.CustomerID COLLATE Latin1_General_BIN
AND OH.SHIP_TO_ID = SDL.ShipToDeliveryLocationID COLLATE Latin1_General_BIN
AND SDL.CustomerKey = CM.CustomerKey
AND OH.CO_NUMBER NOT LIKE 'RA%'
AND OH.CO_NUMBER NOT LIKE 'F%GST'
AND UPPER(LTRIM(RTRIM(OH.CUST_PO_NO))) NOT LIKE '%LB-DEMAND%'
AND COL.Status NOT IN ('9')
AND LTRIM(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,
( SELECT DISTINCT COHeaderKey FROM [FSDB-M39].dbo.FS_COLine WHERE COLineStatus < 5 ) COL,
[FSDB-M39].dbo.FS_COLine CL
WHERE OH.CustomerID = CM.CustomerID COLLATE Latin1_General_BIN
AND OH.COHeaderKey = COL.COHeaderKey
AND OH.COHeaderKey = CL.COHeaderKey
AND OH.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'))
AND CL.COLineStatus IN ('5', '6', '7', '8')
AND UPPER(LTRIM(RTRIM(OH.CustomerPONumber))) NOT LIKE '%LB-DEMAND%'

AND dbo.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
Post #957573
Posted Thursday, July 22, 2010 3:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 6,842, Visits: 13,369
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





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #957601
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse