asked on

Conversion failed when converting the varchar value 'IE 6410566Q' to data type int

Hi,when i run my below query seperately it runs fine but it fails with a conversion error when its run
as part of the whole query. Any reason why ? Many Thanks

       SELECT CAST(COUNT(*) AS varchar(25)) FROM xbilling
                   UNION
                   select 'IE 6410566Q'

whole query

SELECT CAST(COUNT(*) AS varchar(50)) FROM xbilling bi WHERE bi.xasset = d.xasset AND bi.trantype = 'Rental' AND bi.datedue >= '20191001'
UNION
select 'IE 6410566Q'

Error Conversion failed when converting the varchar value 'IE 6410566Q' to data type int

Pavel Celba

Your query works for me on SQL Server 2012 -2017 what is your version?

Pavel Celba

The "whole query" does not contain everything because it contains expression bi.xasset = d.xasset

What means d.xasset ? What data types are the above 2 columns?

gvamsimba

ASKER

Hi, mine is 2005 (old school) but the query works for me too...

but when I ran the query as part of the OVERALL query it fails with that conversion error.

Any idea why this would happen ?

gvamsimba

ASKER

[XAsset] is just a joining key...its an INT

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pavel Celba

I am able to generate the same error with the following command:

SELECT COUNT(*) FROM xbilling
UNION
SELECT CAST(COUNT(*) AS varchar(25)) FROM xbilling bi WHERE bi.trantype = 'Rental' AND bi.datedue >=  '20191001'
UNION
select 'IE 6410566Q'

Open in new window

gvamsimba

ASKER

select distinct 'IE 6410566Q' from customer works fine
WHOLE query contains only one UNION and it works fine when I put a integer value like 10 for example instead of 'IE 6410566Q'

gvamsimba

ASKER

even the below works fine If I run separately..very strange

SELECT CAST(COUNT(*) AS varchar(25)) FROM xbilling a inner join xasset b on a.xasset = b.xasset
                   where a.trantype = 'Rental' AND a.datedue >= '20191001'
                   UNION
                   select 'IE 6410566Q'

gvamsimba

ASKER

what do you thing could be reason for the error ??

Thank you

gvamsimba

ASKER

Hi pcelba, sorry , its my fault.. They are using another sub query underneath referencing this column and I have now corrected it and it works fine now. Many Thanks for your help

gvamsimba

ASKER

Perfect

Pavel Celba

LOL, it is obvious we don't see the problem at the first view.

I am glad it works.