November 18, 2016 at 1:50 pm
I am trying to join 2 tables together and when running the query I get the following error.
Msg 245, Level 16, State 1, Line 6
Conversion failed when converting the varchar value '16071HR' to data type int.
select c.client_id,
c.client_name,
m.matter_id,
m.matter_name, m.billable
from docsadm.client as c
left join docsadm.matter as m
on c.client_id = m.client_id
where c.client_name <> ''
and m.matter_name <> ''
and c.client_id not like '%.%'
order by c.client_id
docsadm.client table columns client_name and client_id are both varchar datatype fields
docsadm.matter table columns matter_name and matter_id and billable are varchar datatype fields
I am not sure why it is trying to convert the datatypes to int.
Any help would be appreciated.
November 18, 2016 at 2:13 pm
tschuler-738392 (11/18/2016)
...from docsadm.client as c
left join docsadm.matter as m
on c.client_id = m.client_id
where c.client_name <> ''
and m.matter_name <> ''
and c.client_id not like '%.%'
order by c.client_id
docsadm.client table columns client_name and client_id are both varchar datatype fields
docsadm.matter table columns matter_name and matter_id and billable are varchar datatype fields
What is the datatype of matter.client_id column? You said it's varchar in client table, but if it is int in matter table and you join on it, it does an implicit conversion to int datatype.
November 18, 2016 at 2:15 pm
the docsadm.matter.client_id field is int
How would I go about converting this in my query so that I get the desired results.
November 18, 2016 at 2:24 pm
First, lets make it readable
SELECT c.client_id,
c.client_name,
m.matter_id,
m.matter_name,
m.billable
FROM docsadm.client AS c
LEFT JOIN docsadm.matter AS m ON c.client_id = m.client_id
WHERE c.client_name <> ''
AND m.matter_name <> ''
AND c.client_id NOT LIKE '%.%'
ORDER BY c.client_id
This line is your issue
AND c.client_id NOT LIKE '%.%'
If client_id is an int data type, you cannot do a like.
What are you trying do with this?
It appears that you are trying to filter rows that have a decimal point maybe?
If that's the case, then an int field will not have a decimal point.
If it does have a decimal, then it's not an int
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 18, 2016 at 2:29 pm
Yeah, I see that. I took out the and c.client_id not like '%.%'
Still get the error.
Running the following now but same issue.
select c.client_id,
c.client_name,
m.matter_id,
m.matter_name, m.billable
from docsadm.client as c
left join docsadm.matter as m
on c.client_id = m.client_id
I do see the problem with the matter table having the client_id as in and client table having client_id as varchar. Is there a way to get around this?
November 18, 2016 at 2:41 pm
tschuler-738392 (11/18/2016)
Yeah, I see that. I took out the and c.client_id not like '%.%'Still get the error.
Running the following now but same issue.
select c.client_id,
c.client_name,
m.matter_id,
m.matter_name, m.billable
from docsadm.client as c
left join docsadm.matter as m
on c.client_id = m.client_id
I do see the problem with the matter table having the client_id as in and client table having client_id as varchar. Is there a way to get around this?
This really makes no sense.
If you are trying to join on these fields, and they are in fact different types, and matter actually contains values like "ABC123", then you will never get valid results.
This is from a Hummingbird document management system, correct?
If I remember correctly, the keys are integer identity fields, and there were also "client defined" matter fields. A matter named "12345" means nothing to a user, but "WDR123" might.
I would like to see the results of this query:
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Client'
OR TABLE_NAME = 'Matter'
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 18, 2016 at 2:42 pm
I thought it was stated that client_id in the client table was varchar? In that case the LIKE '%.%' isn't an issue.
The bit pointed out by Chris is the main issue.
If client_id in both tables represents the same thing, then they should be the same datatype.
If changing the datatype of the column is not feasible, then a workaround would be to explicitly CAST/CONVERT m.client_id to varchar in the join.
Cheers!
November 18, 2016 at 2:51 pm
TABLE_CATALOGTABLE_SCHEMATABLE_NAMECOLUMN_NAMEORDINAL_POSITIONCOLUMN_DEFAULTIS_NULLABLEDATA_TYPECHARACTER_MAXIMUM_LENGTHCHARACTER_OCTET_LENGTHNUMERIC_PRECISIONNUMERIC_PRECISION_RADIXNUMERIC_SCALEDATETIME_PRECISIONCHARACTER_SET_CATALOGCHARACTER_SET_SCHEMACHARACTER_SET_NAMECOLLATION_CATALOGCOLLATION_SCHEMACOLLATION_NAMEDOMAIN_CATALOGDOMAIN_SCHEMADOMAIN_NAME
DOCS_ALBANYDOCSADMCLIENTSYSTEM_ID1NULLNOintNULLNULL10100NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
DOCS_ALBANYDOCSADMCLIENTCLIENT_ID2NULLYESvarchar1010NULLNULLNULLNULLNULLNULLiso_1NULLNULLSQL_Latin1_General_CP1_CI_ASNULLNULLNULL
DOCS_ALBANYDOCSADMCLIENTCLIENT_NAME3NULLYESvarchar6060NULLNULLNULLNULLNULLNULLiso_1NULLNULLSQL_Latin1_General_CP1_CI_ASNULLNULLNULL
DOCS_ALBANYDOCSADMCLIENTDISABLED4NULLYESvarchar11NULLNULLNULLNULLNULLNULLiso_1NULLNULLSQL_Latin1_General_CP1_CI_ASNULLNULLNULL
DOCS_ALBANYDOCSADMCLIENTTARGET_DOCSRVR5NULLYESintNULLNULL10100NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
DOCS_ALBANYDOCSADMMATTERSYSTEM_ID1NULLNOintNULLNULL10100NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
DOCS_ALBANYDOCSADMMATTERCLIENT_ID2NULLYESintNULLNULL10100NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
DOCS_ALBANYDOCSADMMATTERMATTER_ID3NULLYESvarchar1010NULLNULLNULLNULLNULLNULLiso_1NULLNULLSQL_Latin1_General_CP1_CI_ASNULLNULLNULL
DOCS_ALBANYDOCSADMMATTERMATTER_NAME4NULLYESvarchar6060NULLNULLNULLNULLNULLNULLiso_1NULLNULLSQL_Latin1_General_CP1_CI_ASNULLNULLNULL
DOCS_ALBANYDOCSADMMATTERBILLABLE5NULLYESvarchar11NULLNULLNULLNULLNULLNULLiso_1NULLNULLSQL_Latin1_General_CP1_CI_ASNULLNULLNULL
DOCS_ALBANYDOCSADMMATTERDISABLED6NULLYESvarchar11NULLNULLNULLNULLNULLNULLiso_1NULLNULLSQL_Latin1_General_CP1_CI_ASNULLNULLNULL
DOCS_ALBANYDOCSADMMATTERTARGET_DOCSRVR7NULLYESintNULLNULL10100NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
November 18, 2016 at 3:06 pm
Yes, it is OpenText DM system. Formerly known as hummingbird.
November 18, 2016 at 3:07 pm
Thank you. This worked.
November 18, 2016 at 3:20 pm
so in analyzing the data it appears that the system_id from the client table is what matches to the matter_id table. Thanks for all your help guys.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply