collate

  • 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

  • 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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply