Results 1 to 7 of 7
  1. #1
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1

    Question Unanswered: Need Help with converting Varchar & Nvarchar

    Hi I'm struggling with a bit of converssion

    I am trying to join two tables together, on the same field except they have different data types, please see the properties below

    Code:
    Table	COLUMN_NAME	DATA_TYPE	CHARACTER MAXIMUM LENGTH	CHARACTER OCTET LENGTH	CHARACTER SET NAME	COLLATION NAME
    1	itemClass	nvarchar	          512	                      1024	                 UNICODE 	Latin1_General_CI_AI
    2	PGCode	        varchar	                  3	                       3                         iso_1	        Latin1_General_CI_AS
    in the code for the join,

    Code:
    left join common.dbo.qryPRDGroupDets on CAST(qryData_GB1_ByColumn.itemclass as varchar(3)) = Cast(common.dbo.qryPRDGroupDets.PGCode as varchar(3))


    I have tried using the CAST function on one side of the join then on both, to no avail, could you please point me in the right direction, before my fringe reaches the back of my head.



    Thanks
    Last edited by MarkWhyte; 09-30-13 at 11:33.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What problem are you encountering.

    This noddy test works for me...
    Code:
    DECLARE @t table (
       itemClass nvarchar(512)
     , PGCode    varchar(3)
    )
    
    INSERT INTO @t (PGCode, itemClass) VALUES ('ABC', 'abcdefg');
    INSERT INTO @t (PGCode, itemClass) VALUES ('DEF', 'DEFASAFASFASFOANF AOAWPFS S ');
    INSERT INTO @t (PGCode, itemClass) VALUES ('GHI', 'abc');
    
    SELECT *
    FROM   @t
    WHERE  Cast(itemClass as varchar(3)) = PGCode;
    George
    Home | Blog

  3. #3
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1
    Hi Gvee

    I keep getting an ODBC -- Call Failed

    " Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AI" in the equal to operation

    here the whole of my code

    Code:
    SELECT 'MasterBClass'= left(B.bclassid, 2), 'MasterBClassName'= B1.bclassname, B.bclassname, bclass, itemClass, CAST(qryData_GB1_ByColumn.itemclass as varchar(3)) as test
    FROM 
    
    qryData_GB1_ByColumn
    
    left join bclass B on qryData_GB1_ByColumn.bclass = B.bclassid
    left join bclass B1 on left(B.bclassid, 2) = B1.bclassid
    
    left join common.dbo.qryPRDGroupDets on CAST(qryData_GB1_ByColumn.itemclass as varchar(3)) = Cast(common.dbo.qryPRDGroupDets.PGCode as varchar(3))
    
    Group By B1.bclassname, B.bclassname, bclass, itemClass, left(B.bclassid, 2)
    
    Order By left(B.bclassid, 2),  bclass ASC
    Regards

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ahhh, I missed the collation part...
    Code:
    DECLARE @t table (
       itemClass nvarchar(512) COLLATE Latin1_General_CI_AI
     , PGCode    varchar(3)    COLLATE Latin1_General_CI_AS
    )
    
    INSERT INTO @t (PGCode, itemClass) VALUES ('ABC', 'abcdefg');
    INSERT INTO @t (PGCode, itemClass) VALUES ('DEF', 'DEFASAFASFASFOANF AOAWPFS S ');
    INSERT INTO @t (PGCode, itemClass) VALUES ('GHI', 'abc');
    
    SELECT *
    FROM   @t
    WHERE  Cast(itemClass As varchar(3)) COLLATE Latin1_General_CI_AS = PGCode;
    George
    Home | Blog

  5. #5
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1
    Thanks Gvee

    Thats brilliant, just to clarify are you changing the languages ?

    Regards

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The Collation keyword just tells SQL Server how to treat the strings as far as sorting and comparing. You can change the language (actually the code page), if you really want to, but you can lose the case insensitivity pretty quickly.

    The larger question, of course, is why you have two separate collations in your database (one accent sensitive, and the other accent insensitive)?

  7. #7
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1
    Ahh Ok, thanks for your input MCrowley

    The answer to your question is I don’t know, I just have to build reports across our various servers that have been put together by our diligent IT department.

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •