Post reply

Subqueries in SQL with different data types

  • January 14, 2014 at 9:08 am

    #303227

    Hello All,

    I have a sql statement which ends with the lines:

    AND P.txtSchoolID NOT IN (SELECT CONTACT.SERIALNUMBER FROM CONTACT)

    The problem is P.txtSchoolID is nvchar and contact.serialnumber is varchar so SQL generates a collation conflict error message.

    Does anyone know if there is a way of getting around this?

    Thanks for any info,

    Tony

  • January 14, 2014 at 9:12 am

    #1679959

    Tony

    If it's a collation conflict you're suffering from, you can use the COLLATE clause in your query. If the problem is different data types, you can use CAST or CONVERT to convert one column's data to the data type of the other column.

    John

  • January 14, 2014 at 9:35 am

    #1679965

    Post the error if you want it more specific.

  • January 14, 2014 at 11:18 am

    #1680006

    tonynlennard (1/14/2014)

    AND P.txtSchoolID NOT IN (SELECT CONTACT.SERIALNUMBER FROM CONTACT)

    The problem is P.txtSchoolID is nvchar and contact.serialnumber is varchar so SQL generates a collation conflict error message.

    You can add the desired collation by using the COLLATE statement. You can specify a specific collation or specify the database default collation.

    AND P.txtSchoolID collate database_default NOT IN (SELECT CONTACT.SERIALNUMBER collate database_default FROM CONTACT)

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • January 14, 2014 at 2:32 pm

    #1680067

    Hello,

    Thank you for everyone's help - the collate statement did the trick.

    Tony

Viewing 5 posts - 1 through 4 (of 4 total)

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