Error when trying to concatenate in select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error when trying to concatenate in select

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-05-11 : 15:49:35

This is the error I get:
Msg 244, Level 16, State 2, Line 1
The conversion of the varchar value '220700253329_' overflowed an INT2 column. Use a larger integer column.

It happens when I add this portion to the select statement:

AND convert(varchar(100), policy.policy_base+'_'+policy.policy_suffix) IN (
'bogus_data'
--SELECT policy_base+'_'+policy.policy_suffix
--FROM policy_transaction_fee
--WHERE (fee_code = 'ins')
--GROUP BY policy_base
--HAVING (SUM(fee_amount) > 0)
)

(I have left the commented portion so you can see where I am headed.

Here is the whole statement:

SELECT policy.policy_base --[Policy_Base]
,
policy.policy_suffix --[Policy_Suffix]
,
(SELECT Max(policy_transaction.SEQUENCE)
FROM policy_transaction
WHERE policy_transaction.policy_base = policy.policy_base
AND policy_transaction.policy_suffix = policy.policy_suffix) + 1 AS SEQUENCE --[Sequence]
,
'AD' --[Transaction_Type]
,
Getdate() --[Effective_Date]
,
-10.00 --[Installment_Fee]
,
Getdate() --[Entered_Date]
,
policy.endorsement --[Endorsement]
,
policy.company --[Company]
,
policy.subcompany --[Subcompany]
FROM policy_transaction
INNER JOIN policy
ON policy_transaction.policy_base = policy.policy_base
AND policy_transaction.policy_suffix = policy.policy_suffix
INNER JOIN policy_transaction_fee
ON policy_transaction.policy_base = policy_transaction_fee.policy_base
AND policy_transaction.policy_suffix = policy_transaction_fee.policy_suffix
WHERE (policy_transaction.entered_date >= '2008-08-28')
AND (policy.status <> 'ca')
AND (policy.eft_flag = 'y')
AND (policy.current_flag = 'y')
AND (policy.installment_fees >= 10)
AND (policy_transaction_fee.fee_code = 'ins')
AND Convert(VARCHAR(100),policy.policy_base + '_' + policy.policy_suffix) IN ('bogus_data'--SELECT policy_base
--FROM policy_transaction_fee
--WHERE (fee_code = 'ins')
--GROUP BY policy_base
--HAVING (SUM(fee_amount) > 0)
)
GROUP BY policy.policy_base,
policy.policy_suffix,
policy.endorsement,
policy.company,
policy.subcompany

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-11 : 16:08:03

What are the datatypes of policy.policy_base and policy.policy_suffix? Perhaps you need to change to convert these values individually to varchar BEFORE you concatenate them

Be One with the Optimizer
TG

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-05-11 : 16:10:32

That did it. Thank you for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources