August 2, 2011 at 1:40 pm
I am trying to do left outer join and seeing the error:
Conversion failed when converting the varchar value 'No ' to data type int.
Can anybody please see into this?
Thanks..
August 2, 2011 at 1:45 pm
Chances are you're doing an implicit conversion. Make it an explicit conversion taking the column that's an int and make it a varchar. If you need more help post the script to create the tables and the query that's getting the error.
August 2, 2011 at 1:46 pm
Nothing more to say.
You ask the server to join an int column to a varchar so it fails because it can't convert the varchar to an int (yes that's the way it should be done).
August 2, 2011 at 1:47 pm
cfradenburg (8/2/2011)
Chances are you're doing an implicit conversion. Make it an explicit conversion taking the column that's an int and make it a varchar. If you need more help post the script to create the tables and the query that's getting the error.
Converting int to varchar might force the server to do index scans rather than seek. This is far from optimal.
The best solution is to use int <or correct datatype> on both columns.
August 2, 2011 at 1:50 pm
Ninja's_RGR'us (8/2/2011)
Converting int to varchar might force the server to do index scans rather than seek. This is far from optimal.
Indeed. But if the DDL can't be modified it's more optimal than code that throws an error.
August 2, 2011 at 1:51 pm
cfradenburg (8/2/2011)
Ninja's_RGR'us (8/2/2011)
Converting int to varchar might force the server to do index scans rather than seek. This is far from optimal.Indeed. But if the DDL can't be modified it's more optimal than code that throws an error.
Depends on datasize ;-).
August 2, 2011 at 2:13 pm
Thanks...
i am trying to run the following script...
Select m.acctid, m.MPLMerchantNumber, m.MPLStoreNumber,
m.MPLRegisterNumber,
c.lutdescription,
m.MPLMerchantLevel,
m.parent01aid,m.parent02aid 'InstitutionID',
a.AddressType, a.addressline1, a.addressline2, a.POBox, a.city, a.StateorProvince,
a.Country, a.postalcode
from merchantplaccounts m with (nolock)
join ccardlookup c with (nolock) on m.acctid = c.lutcode
left outer join address a with(nolock) on a.parent02aid = m.acctid
and a.parent02atid = '32'
and c.lutid = 'storename'
And it is throwing the error..
So Is it fine to do char to int ?
August 2, 2011 at 2:17 pm
Help us help you, how can I possibly answer that without seeing the tables' definition?
August 2, 2011 at 2:32 pm
Ninja's_RGR'us (8/2/2011)
Help us help you, how can I possibly answer that without seeing the tables' definition?
m.acctid(PK,int,not null)
m.MPLMerchantNumber(char,null)
m.MPLStoreNumber(char,null)
m.MPLRegisterNumber(char,null)
c.lutdescription(char,null)
m.MPLMerchantLevel(int,null)
m.parent01aid(int,null)
m.parent02aid(int,null)
a.AddressType(char,null)
a.addressline1(varchar,null)
a.addressline2(varchar,null)
a.POBox(varchar,null)
a.city(varchar,null)
a.StateorProvince(char,null),
a.Country(char,null),
a.postalcode(char,null)
c.lutid(pk,char,not null)
August 2, 2011 at 2:39 pm
Why so many char instead of varchar?
Which join is failing?
P.S. Don't use nolock unless you want incorrect results. http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
August 2, 2011 at 2:53 pm
left outer join is getting the error..
August 2, 2011 at 2:57 pm
Looks like this one might be the problem to me... on m.acctid = c.lutcode
August 2, 2011 at 2:57 pm
Left outer join is failing..
And giving the error:
Conversion failed when converting the varchar value 'No ' to data type int.
August 2, 2011 at 3:00 pm
left outer join address a with(nolock) on a.parent02aid = CONVERT(VARCHAR(50), m.acctid )
I still think refactoring is the solution if it matters.
August 2, 2011 at 3:02 pm
If i do normal inner join removing the left outer join , the query runs good...
what might be the problem?
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply