Monday, February 04, 2013 5:22 PMI'm trying to do a left join on a claim number and claim line number in one table to a claim numer and a claimline number in another table but one table has 1 through 9 but the second table has 01, 02, 03 through 09 so they can't match because one has 1 and the other has 01. What can I do to make that work?
Monday, February 04, 2013 5:24 PMModerator
Do a transform on the keys: CAST (yourkey as INT)
For quick assistance post code & DDL.
Kalman Toth SQL 2008 GRAND SLAM
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012
Monday, February 04, 2013 5:24 PM
Cast 2nd table's column to int e.g
from table1 left join table2 on table.id=cast(table2.id as int)
Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
Monday, February 04, 2013 5:25 PMModerator
What are the types of the columns? Try
select T1.*, T2.*
from Table1 T1 LEFT JOIN Table2 T2 ON RIGHT('00'+cast(T1.ClaimNumber as varchar(2)),2) = T2.ClaimNumber
For every expert, there is an equal and opposite expert. - Becker's Law
Monday, February 04, 2013 5:28 PMModerator
You can convert the numbers beginning with O to an integer
DECLARE @tbl1 TABLE( col1 INT); INSERT @tbl1 VALUES(1), (2), (3); DECLARE @tbl2 TABLE( col1 CHAR(2)); INSERT @tbl2 VALUES('01'), ('02'), ('03'); SELECT * FROM @tbl1 t JOIN @tbl2 tt ON t.col1 = CONVERT(INT, tt.col1);
David Dye My Blog
Monday, February 04, 2013 7:01 PM
Thank you every one for such quick responses. I was able to figure it out but I love the suggestions every one made. I had the syntax incorrect.
distinct a.CLAIM, a.line, a.error1, a.error2, a.error3, a.billed_amt
join [VPHP].dbo.b_claim_line b on a.CLAIM = b.claim_num
join [VPHP].dbo.b_claim_line c on c.CLAIM_LINE = case a.line when '01' then '1'
when '02' then '2'
when '03' then '3'
when '04' then '4'
when '05' then '5'
when '06' then '6'
when '07' then '7'
when '08' then '8'
when '09' then '9'
a.DT_ENCOUNTER = '2012/08/01' and a.ERROR_LEVEL = '8'
by a.CLAIM, a.LINE, a.ERROR1, a.ERROR2, a.error3, a.billed_amt
- Marked As Answer by Iric WenModerator Monday, February 18, 2013 2:25 AM
Monday, February 04, 2013 7:10 PMyou're joining mismatched data types you're going to have to lose somewhere. Whether it should be on the left or right hand side of a join depends entirely on the data density, and what indices are actually on the tables involved(e.g. It really doesn't cost a whole lot if a.foo isn't indexed anyway).
Monday, February 04, 2013 7:23 PM
Why do you group when there are no aggregate functions (SUM, AVG, MIN, MAX, etc)?
Why are you using joins when you only select data from HFCAencounters?
Why are you joining the same table twice on different keys?
What if a.line = '001' ? you should use a CAST or CONVERT as suggested to join the integers. In your code you do not change the data type from string to numeric. I would expect the values to be numbers in CLAIM_LINE