September 8, 2004 at 11:01 am
i am trying to join two tables via account number, which is present in both tables but in the following format:
Table 1: 000000000123
Table 2: 123
I tried the following join:
Select *
From Table1 as a
left join Table2 as b on (a.accountnumber=b.accountnumber)
However, the query returns no rows. How can i modify this query to disregard the zero's that appear before each accountnumber in table 1?
September 8, 2004 at 11:47 am
Assuming that the accountnumber in table 1 is a char or varchar column, and in table 2 it is an int column,
Select *
From Table1 as a
join Table2 as b on (cast(a.accountnumber as int) = b.accountnumber)
If they're both character fields, you could cast them both as int, assuming that they will be integer type values.
Steve
September 8, 2004 at 12:06 pm
i've tried the following join with no luck
join lane.dbo.serviceaddr ls on (cast(a.aglaccountnumber as int)=(cast(ls.ldcaglnum as int))
am i doing something wrong with the placement of my parenthesis?
September 8, 2004 at 3:08 pm
sure enough, you're missing a ) at the end
join lane.dbo.serviceaddr ls on (cast(a.aglaccountnumber as int)=(cast(ls.ldcaglnum as int)))
Everett Wilson
ewilson10@yahoo.com
September 9, 2004 at 7:34 am
I receive the following error:
Server: Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '8442080139' overflowed an int column. Maximum integer value exceeded.
here is the query i am using:
select *
from aglaccount a
join lane.dbo.serviceaddr ls on (cast(a.aglaccountnumber as int)=(cast(ls.ldcaglnum as int)))
September 9, 2004 at 8:00 am
The maximum value that an integer data type can contain is 2,147,483,647. Try using a bigint data type.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
September 9, 2004 at 8:42 am
Two things. One, you don't want to cast both columns because it will force a full index scan on both tables since you are manipulating the indexed value (SQL Server can't count on it being sorted anymore and so can't SEEK). That becomes moot if some other aspect of the index forces a scan anyhow (the accountnumber is not the first column of the index). Second, based on what you attempted earlier I'm going to assume that both columns are character based (otherwise why cast BOTH to int). If that is the case then choose the table that has the smallest number of index pages (if same doesn't matter which one) and convert that column to look like the other so that the largest index is seeked and the smallest one is scanned. You can use DBCC SHOWCONTIG (or other methods) to determine which index is the largest. Actually, you should do that regardless of whether or not they are both character based fields.
WHERE CAST( CAST( a.accountnumber as bigint ) as varchar) = b.accountnumber
OR
WHERE a.accountnumber = RIGHT( REPLICATE( '0', 12 ) + b.accountnumber, 12 )
Try to make sure that the resulting CAST or CONVERT results in the same data type as the column being compared against.
Also note that if the column can contain non-numeric values you can't cast it to a bigint (as was done in the first WHERE clause above) and will need to be processed a different way.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply