Implicit Conversoin error

[t16turbo]

hi there,

i'm trying to perform a union using the following SQL:

Select  ATEHistory.DepartCode, ATEHistory.TransDate, ATEHistory.Client+' - '+ ATEHistory.clntname,  rtrim(ATEHistory.lastname)+', '+rtrim(ATEHistory.firstname),rtrim(ATEHistory.EmplyCode), ATEHistory.description, ATEHistory.Quantity from dbo.ATEHistory 
UNION ALL 
SELECT  SUBSTRING(CLARITYHistory.DEPARTCODE, 3, 4), CLARITYHistory.PRSTART, CLARITYHistory.CUSTOMER_CODE+' - '+CLARITYHistory.CUSTOMER, CLARITYHistory.LAST_NAME+', '+CLARITYHistory.FIRST_NAME+' - '+CLARITYHistory.EXTERNAL_ID, CLARITYHistory.LOCATION, CLARITYHistory.ACTUALS  FROM dbo.CLARITYHistory

this complains of the error:

Server Message:  Number  257, Severity  16
Server 'dst_test', Line 1:
Implicit conversion from datatype 'TEXT' to 'VARCHAR' is not allowed.  Use the CONVERT function to run this query.

this only happened when I changed a value in the 2nd select from CLARITYHistory.PREXTERNALID to CLARITYHistory.EXTERNAL_ID.




can anyone tell me what might be happening.
shall I just use a convert function as the error message suggests?
can anyone tell me how.

thanks in advance,
Matt

 

[t16turbo]

just made a change to the first SELECT in the Union.
(I had change it to test some ideas)

here it is as it should be with the +' - '+ included

Select  ATEHistory.DepartCode, ATEHistory.TransDate, ATEHistory.Client+' - '+ ATEHistory.clntname,  rtrim(ATEHistory.lastname)+', '+rtrim(ATEHistory.firstname)+' - '+rtrim(ATEHistory.EmplyCode), ATEHistory.description, ATEHistory.Quantity from dbo.ATEHistory 
UNION ALL 
SELECT  SUBSTRING(CLARITYHistory.DEPARTCODE, 3, 4), CLARITYHistory.PRSTART, CLARITYHistory.CUSTOMER_CODE+' - '+CLARITYHistory.CUSTOMER, CLARITYHistory.LAST_NAME+', '+CLARITYHistory.FIRST_NAME+' - '+CLARITYHistory.EXTERNAL_ID, CLARITYHistory.LOCATION, CLARITYHistory.ACTUALS  FROM dbo.CLARITYHistory

 

[yelworcm]

Which column involved is the text column?

In general, a union query imposes the datatypes from the first query on the second query. So if you have

select charcol
from characters
union
select intcol
from numbers
order by 1

You will get the numbers ordered as if they were character strings. If you reverse the queries, you can get data type conversion errors.

 

[t16turbo]

thanks for that.

I used Convert on the second union to change EXTERNAL_ID to a varchar and it seems to have done the job.