Collation errors, damned if I do and damned if I don't!

Question

Collation errors, damned if I do and damned if I don't!

david.dartnell

Ten Centuries

Points: 1276
More actions
August 13, 2014 at 1:00 am

#297884

Hi Everyone,
I am receiving a number of 'collation' related errors with the following query -
WITH
CTESAPData(DocDate, DocNum, xDocType, ItemCode)
AS
(
SELECT
T1.DocDate AS 'DocDate'
, T1.DocNum AS 'DocNum'
, 'Invoice' AS 'xDocType'
, T0.ItemCode AS 'ItemCode'
FROM AU.dbo.INV1 T0
INNER JOIN AU.dbo.OINV T1 ON T1.DocEntry = T0.DocEntry
UNION ALL
SELECT
T1.DocDate AS 'DocDate'
, T1.DocNum AS 'DocNum'
, 'Cube' AS 'xDocType'
, T0.ItemCode AS 'ItemCode'
FROM SCE.dbo.AU_SALES_R T0
INNER JOIN SCE.dbo.AU_SALES_H T1 ON T1.DocEntry = T0.DocEntry collate Latin1_General_CI_AS
)
SELECT T0.*
FROM CTESAPData AS T0
If I leave the collate section out then I get the following message!
However on the other hand if I leave the collation section in my query I get the following message!
I am basically "damned if I do, and damned if I don't"! If anybody has any suggestions here they will be greatly appreciated.
Kind Regards,
David

Viewing 7 posts - 1 through 6 (of 6 total)

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

pietlinden SSC Guru Points: 63509 More actions · Answer 1

Did you specify a collation on one or more columns in one of your tables? It sounds like you're trying to get the collations to match, and they don't.

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 2

Can you post the DDL including the collation for AU.dbo.INV1, AU.dbo.OINV, SCE.dbo.AU_SALES_R, SCE.dbo.AU_SALES_H and the database collation?

😎

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

I think you're putting the collate clause in the wrong place, pretty sure it needs to be on one of the T0.ItemCode fields (assuming ItemCode is a character field)

Does this work?

SELECT

T1.DocDate AS 'DocDate'

, T1.DocNum AS 'DocNum'

, 'Invoice' AS 'xDocType'

, T0.ItemCode AS 'ItemCode'

FROM AU.dbo.INV1 T0

INNER JOIN AU.dbo.OINV T1 ON T1.DocEntry = T0.DocEntry

UNION ALL

SELECT

T1.DocDate AS 'DocDate'

, T1.DocNum AS 'DocNum'

, 'Cube' AS 'xDocType'

, T0.ItemCode collate Latin1_General_CI_AS AS 'ItemCode'

FROM SCE.dbo.AU_SALES_R T0

INNER JOIN SCE.dbo.AU_SALES_H T1 ON T1.DocEntry = T0.DocEntry

david.dartnell Ten Centuries Points: 1276 More actions · Answer 4

Hi Gazareth,

Thanks for the suggestion, unfortunately it doesn't work, but instead produces the error below.

Kind Regards,

David

david.dartnell Ten Centuries Points: 1276 More actions · Answer 5

Hi Everyone,

A colleague has kindly just shared with me that Collation on specific columns is required in this instance, rather than on the join itself. Please see below...

Kind Regards,

David

Gazareth One Orange Chip Points: 27737 More actions · Answer 6

Gazareth

One Orange Chip

Points: 27737

August 14, 2014 at 3:07 am

#1737955

Yep, the collate comes before the alias.