April 29, 2016 at 5:09 am
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'
April 29, 2016 at 5:18 am
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
April 29, 2016 at 5:22 am
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply