SQLTeam.com | Weblogs | Forums

The conversion of Varchar Value error


#1

I have written a quick SQL query to pull data with discounted codes associated with accounts. When I attempt to run the query for a specific account number, I receive a conversion of the varchar value overflowed an INT2 column error. Not sure why. It recommends I use a larger integer column, but the column I'm using is the column that houses accounts numbers. Here's the code for the query I wrote:

IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C IF OBJECT_ID('TEMPDB..#TMP2C') IS NOT NULL DROP TABLE #TMP2C

SELECT
[SUB_ACCT_NO_MON]
,[BILL_CDE_MON]
,[DSCNT_AMT_MON]
,[DSCNT_CDE_MON]
,[ENTRY_REFNO_MON]
,[LOB_MON]
,[PRIN_MON]
,[SRV_TYP_MON]
,[TRAN_AMT_MON]
,[TRAN_CDE_MON]
,[TRAN_DTE_MON]

INTO #TMP1C

FROM [Vantage].[dbo].[MON_TRAN_BASE] (NOLOCK)

WHERE PRIN_MON IN ('6000','7500')

AND TRAN_DTE_MON BETWEEN '2015-12-01' AND GETDATE()

--SELECT * FROM #TMP1C

SELECT DISTINCT
B.[PRIN_DSC]
,B.[SYS_DSC]
--,B.[AGNT_DSC]
,case when C.[EXT_STAT_SBB] = ' ' then 'Active'
end as 'CUSTOMER_STATUS'
,A.[SUB_ACCT_NO_MON]
,C.[RES_NAME_SBB]
,H.[ADDR1_HSE]
,H.[RES_CITY_HSE]
,H.[POSTAL_CDE_HSE]
,A.[BILL_CDE_MON]
,A.[DSCNT_AMT_MON]
,B.[DSC_CDE_DSC]
,B.[STMT_DESCR_DSC]
,A.[ENTRY_REFNO_MON]
,C.[CUR_BAL_SBB]

INTO #TMP2C

FROM #TMP1C AS A (NOLOCK), [Vantage].[dbo].[DSC_DISCOUNT] AS B (NOLOCK),
[Vantage].[dbo].[SBB_BASE] AS C (NOLOCK), [Vantage].[dbo].[HSE_BASE] AS H (NOLOCK)

WHERE A.[PRIN_MON] = C.[PRIN_SBB]
AND A.[SUB_ACCT_NO_MON] = C.[SUB_ACCT_NO_SBB]
AND C.[HSE_KEY_SBB] = H.[HSE_KEY_HSE]
AND A.[DSCNT_CDE_MON] = B.[DSC_CDE_DSC]

AND B.[DSC_CDE_DSC] IN ('A5','AN','B0','B8','B9','ZG','ZE','ZK','ZR','ZM','ZF','Z6','ZL',
'ZI','OA','ZZ','ZJ','ZD','Y5','ZB')

and B.[STMT_DESCR_DSC] LIKE '%DISCOUNT%'

and C.[PRIN_SBB] = '8495600080734603'

SELECT * FROM #TMP2C


#2

INT2: 2-byte integer between -32767 and 32767.

so, what should happen if your source varchar column holds 32768?


#3

What are your underlying table definitions? Are you comparing a VARCHAR to INT?[quote="Damian39, post:1, topic:4946"]
WHERE A.[PRIN_MON] = C.[PRIN_SBB] AND A.[SUB_ACCT_NO_MON] = C.[SUB_ACCT_NO_SBB] AND C.[HSE_KEY_SBB] = H.[HSE_KEY_HSE] AND A.[DSCNT_CDE_MON] = B.[DSC_CDE_DSC]
[/quote]


#4

A.[PRIN_MON] = C.[PRIN_SBB] Both of these are Varchar.


#5

Same with the other joins. They are all the same format.


#6

Having the table DDL would REALLY help diagnose the issue.

Is there a reason why you are using NOLOCK? That is not the "go faster" switch for the database. It's the "potentially unreliable data" switch.


#7

Not entirely sure how I can provide you with the DDL for the tables that I'm using. I use NOLOCK, because there are multiple people creating and running queries using the same tables. I don't want to lock a table while my query runs.


#8

You're misunderstanding locking. Unless someone runs a query with TABLOCK or updates a significant portion of the table, the table won't be locked. Individual rows will take a SELECT lock with the default READCOMMITTED setting, which is what you want.

Using NOLOCK can result in phantom, duplicate and skipped rows, among other nasty things. You don't want that.


#9

As for the DDL, here is a link that can explain what/how to do that. Without knowing the definition of your tables, trying to diagnose the issue is like trying to guess what is wrong with your car by listening to you try and mimic the noises while you drive down the freeway.

How to Ask Questions on Database Forums...

There is also a TON of forum posts related to NOLOCK and why it is evil. Just search on that term and you can find plenty of justification for not using it.