November 5, 2012 at 10:34 am
Hello All,
I am relatively new to SQL, and am working in SQL Server 2008. I am having an issue with a query and cannot find the solution. I have searched online in many forums, but haven't come up with anything that works.
here is a breakdown of the issue:
I am working with two Tables, dbo.Table1 and dbo.Table2, in a database entitled 'DStudy'.
I am trying to build an Inner Join query for these two tables, based on the Column named "Account_Num". The "Account_Num" column exists in both dbo.Table1 & dbo.Table2.
The dbo.Table1 Account_Num column details are: PK, decimal(13,0), not null
The dbo.Table2 Account_Num column details are: decimal(13,0), not null
The condition within the query that I am using is based on a column entitled "BBL", which is a varchar(100), null, column, contained in dbo.Table1.
See the query that I initially used below which generated this error:
"Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '05000010216' overflowed an int column. Maximum integer value exceeded."
QUERY:
select *
from dbo.Table2 as a inner join
dbo.Table1 as b on a.Account_No = b.Account_No
where BBL=1015180039
This query works whenever the "BBL" value specified in the syntax begins with a number 1 or 2. I switched the number to a BBL value that started with a value other than 1 or 2 and the query works w/o an error.
I looked into CASTING functions because I thought I might need to CAST the BBL column as BIGINT, instead of VARCHAR. I was able to develop the revised queries below in an attempt to get the query to work without the error message, but was unsuccessful. Here are the queries I tried:
SELECT
CASE WHEN ISNUMERIC(BBL)>0
THEN CAST(BBL AS BIGINT)
ELSE 0 END
FROM dbo.Table1
This query works but I need to connect it to the Inner Join syntax. Any thoughts on how I can do this?
I also ran this query:
select CAST(b.BBL As bigint) As BBL
from dbo.Table2 as a inner join
dbo.Table1 as b on a.Account_No = b.Account_No
Where b.BBL=1015210153
But I still get this error message:
"Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '05000010216' overflowed an int column. Maximum integer value exceeded."
Does anyone have any query modifications or workarounds to fix this issue? I thought I knew what the issue was (incompatible data type, with a CAST VARCHAR to BIGINT solution) but it turns out it wasn't the silver bullet answer I need.
If anyone has any insight, it would be greatly appreciated.
Thanks in advance!
All best,
Grace
November 5, 2012 at 11:01 am
The error is occurring on your WHERE clause because is trying to convert BBL into int to compare against 1015180039. If BBL is a varchar, you should compare against varchar and don't depend on implicit conversions.
Something like this:
select *
from dbo.Table2 as a inner join
dbo.Table1 as b on a.Account_No = b.Account_No
where BBL='1015180039'
November 5, 2012 at 11:53 am
Hi Luis,
Thank you for the response! When I added the single quotes into the syntax, the query ran successfully without an error.
However, my only concern is that the Query is returning 0 rows, when there should be 1 row returned.
Does the rest of the syntax statement look correct to you? I want SQL to establish the inner join relationship based on the the "Account_Num" fields in dbo.Table1 & dbo.Table 2. The "Where" part of the statement is how I'm asking SQL to find a specific row in the database within the "BBL" column in dbo.Table1, while including the column information from dbo.Table 2 as well
Should I switch dbo.Table1 and dbo.Table2 in the syntax?
If you have any further insight as to why I would get 0 rows from this query, let me know. And again, thank you for taking time to help with this!
Best,
Grace
November 5, 2012 at 1:48 pm
The query seems fine, but you need to know what is causing the problem.
Remember that if you're comparing strings, you have to be careful with leading spaces or zeros.
'123' is different to ' 123' and '0123'.
You can try this option to narrow down your problem.
select *
from dbo.Table2 as a inner join
dbo.Table1 as b on a.Account_No = b.Account_No
where CAST( BBL AS bigint)=1015180039
--If it returns the correct row, you might have leading spaces or zeros.
For further help, read the article linked in my signature.
November 5, 2012 at 2:02 pm
Hi Luis!!
I tried the last query you provided and it worked! The query ran successfully and returned the expected records.
This is great, I think you were right about the leading spaces and/or zeros.
I really appreciate you taking the time to respond to my thread. You have no idea how helpful this is. I felt a bit like I was chasing my tail on this one.:w00t:
Thanks again for the help. This forum is extremely useful. I only hope my SQL knowledge expands to the point where I can help people in forums too!
All best,
Grace
November 5, 2012 at 4:05 pm
I'm glad that I could help.
Now, you should be careful when handling data. Noone will assure you that you won't find a value that's not supported by bigint.
Try to control what's in the database.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply