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_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_PRECISION_RADIX NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_CATALOG CHARACTER_SET_SCHEMA CHARACTER_SET_NAME COLLATION_CATALOG COLLATION_SCHEMA COLLATION_NAME DOMAIN_CATALOG DOMAIN_SCHEMA DOMAIN_NAME
DOCS_ALBANY DOCSADM CLIENT SYSTEM_ID 1 NULL NO int NULL NULL 10 10 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
DOCS_ALBANY DOCSADM CLIENT CLIENT_ID 2 NULL YES varchar 10 10 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL
DOCS_ALBANY DOCSADM CLIENT CLIENT_NAME 3 NULL YES varchar 60 60 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL
DOCS_ALBANY DOCSADM CLIENT DISABLED 4 NULL YES varchar 1 1 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL
DOCS_ALBANY DOCSADM CLIENT TARGET_DOCSRVR 5 NULL YES int NULL NULL 10 10 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
DOCS_ALBANY DOCSADM MATTER SYSTEM_ID 1 NULL NO int NULL NULL 10 10 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
DOCS_ALBANY DOCSADM MATTER CLIENT_ID 2 NULL YES int NULL NULL 10 10 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
DOCS_ALBANY DOCSADM MATTER MATTER_ID 3 NULL YES varchar 10 10 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL
DOCS_ALBANY DOCSADM MATTER MATTER_NAME 4 NULL YES varchar 60 60 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL
DOCS_ALBANY DOCSADM MATTER BILLABLE 5 NULL YES varchar 1 1 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL
DOCS_ALBANY DOCSADM MATTER DISABLED 6 NULL YES varchar 1 1 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL
DOCS_ALBANY DOCSADM MATTER TARGET_DOCSRVR 7 NULL YES int NULL NULL 10 10 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
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