Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


collate


collate

Author
Message
shrestha_rosy
shrestha_rosy
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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
LutzM
LutzM
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9225 Visits: 13559
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search