collate error when creating a View

  • dopydb

    SSCertifiable

    Points: 6631

    hi

    I have a query that runs fine, it has 2 collate statements due to the 2 dbs being setup differently.

    but as soon as I try to make the query a view on the same db I run the query on, it complains about collation resolution? and I cant create the view

    anyone any ideas for me?

    select DISTINCT T.CODE,SH.SRREFERENCE,H.CODE as HaulierId,H.NAME

    ,SH.TOTALQTYPLANNED,(PostedGrnValue/NULLIF(T.TOTALQTYPLANNED,0))*SH.TOTALQTYPLANNED as costperdrop

    ,(case ISNULL(InterWhSale,'N')

    when 'Y' then TargetWarehouse

    else SM.Customer

    end)AS CUSTOMER,

    SM.InterWhSale,MerchandiseValue as SalesOrderValue,SourceWarehouse,A.AlphaValue as Region

    from [Plato English].[dbo].[TRANSPORTREQUEST] T

    LEFT JOIN [Plato English].[dbo].HAULIER H ON H.HAULIERID = T.HAULIERID

    LEFT JOIN [Plato English].[dbo].STATUSNAME SN ON SN.STATUSNAMEID = T.PHYSICALSTATUSNAMEID

    LEFT JOIN [Plato English].[dbo].STATUSNAME SN2 ON SN2.STATUSNAMEID = T.FINANCIALSTATUSNAMEID

    left join (select StockCode,sum(CurGrnValue) as PostedGrnValue from SysproCompanyW.dbo.GrnDetails group by StockCode)x

    *********collation join 1***********************************************

    on x.StockCode collate Latin1_General_BIN = T.CODE

    LEFT JOIN (SELECT TR.CODE,SUM(TVE.AMOUNT)AS PLATOVOUCHERS FROM [Plato English].[dbo].transportvoucher TV LEFT JOIN

    [Plato English].[dbo].transportvoucherentry TVE ON TV.transportvoucherid = TVE.transportvoucherid LEFT JOIN

    [Plato English].[dbo].transportrequest TR ON TV.transportrequestid = TR.transportrequestid

    WHERE TV.ISACTIVE = 'T'

    AND TVE.ISACTIVE = 'T'

    and TOPOST ='T'

    GROUP BY TR.CODE)Q ON Q.CODE = T.CODE

    left join [Plato English].[dbo].TRANSPORTREQUESTSHIPMENTS TRSH ON TRSH.TRANSPORTREQUESTID = T.TRANSPORTREQUESTID

    LEFT JOIN [Plato English].[dbo].SHIPMENT SH ON SH.SHIPMENTID = TRSH.SHIPMENTID

    *********collation join 2****************************************

    left join SysproCompanyW.dbo.SorMaster SM on SM.SalesOrder collate Latin1_General_BIN = SH.SRREFERENCE

    left join SysproCompanyW.dbo.ArTrnSummary AR on AR.SalesOrder = SM.SalesOrder

    left join SysproCompanyW.dbo.K3_CUS_AdmForm_REG001 A on A.KeyField = SM.Customer

    WHERE T.CODE IS NOT NULL

    AND T.FINISHDATE < '2016-04-01' and T.FINISHDATE >= '2016-03-01'

    AND SM.OrderStatus > '2'

    AND InterWhSale <>'Y'

  • John Mitchell-245523

    SSC Guru

    Points: 148259

    Presumably the collation of the database you're creating the view on isn't Latin1_General_BIN? I think the easiest way to solve this would just be to put the same COLLATE clause on both sides of the join predicates.

    John

  • dopydb

    SSCertifiable

    Points: 6631

    hi John

    apologies, as usual I was being dopy!

    I was collating the wrong side of the joins!!

    funny how quickly I find my errors after I post on here, must be some sort of magic! 😀

    mal

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

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