Error converting data type varchar to numeric.

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 converting data type varchar to numeric.

Author

Topic

musman
Starting Member

17 Posts

Posted - 2008-09-30 : 08:03:12

Hello All. I am getting this error over and over. I dont know where did I do wrong but couldnt figure it out. Please help me getting this thing resolved. Thanks

Declare @IndexTable
Table(ApplicationID Varchar(30),PolicyNumber Varchar(300),PolicyNumberRoster Varchar(300),
OriginalPolicyNumber Varchar(300),ProgramCode Char(2),EnterpriseCd Char(2),
AgentName Varchar(50),RiskState Char(2),PolicyState Char(2),ApplicantName Varchar(100),
EffectiveDate Datetime,ExpirationDate Datetime,LastTransactionDt Datetime,
PremiumAmount Decimal(12,2),LOB Char(3),UserID Varchar(20),LOC Char(2),MCO Char(2),PCO Char(2),HistoryParent Varchar(30),HistorySequence Varchar(30),
SequenceNumber Int,UnderwritingStatusIndicator SmallInt,UserMessageIndicator SmallInt,
UnderwriterMessageIndicator SmallInt,PolicyStatusCd Char(2),PolicyStatus Varchar(30),MessageApplicationID Varchar(30),QuickQuoteInd Bit,
IteratedQuotes Varchar(Max),DaysLeftToIssue Int,ArchviedAfterBindHold Char(1),RenewalInd Char(1),RecordCount Int);
Insert Into @IndexTable SELECT
ApplicationID = InqPol.SYMBOL + ':' + InqPol.POLNUM + ':' + CONVERT(VARCHAR(2), InqPol.MODULE) + ':' + InqPol.LOB,
PolicyNumber = InqPol.SYMBOL + InqPol.POLNUM + '-' + CASE len(InqPol.Module) WHEN 1 THEN '0' + CONVERT(VARCHAR(2), InqPol.MODULE) ELSE CONVERT(VARCHAR(2), InqPol.MODULE) END,
PolicyNumberRoster = InqPol.SYMBOL + InqPol.POLNUM + '-' + CASE len(InqPol.MODULE) WHEN 1 THEN '0' + CONVERT(VARCHAR(2), InqPol.MODULE) ELSE CONVERT(VARCHAR(2), InqPol.MODULE) END,
OriginalPolicyNumber = '',
ProgramCode = State.StateCode,
EnterpriseCd='',
'' as AgentName,
RiskState = State.StateCode,
PolicyState = State.StateCode,
PolicyStatusCd = 'I',
ApplicantName = REPLACE(Isnull(InqPol.ORGANZTN,''),'''','#39;'),
EffectiveDate = InqPol.EFFDTE,
ExpirationDate = Convert(varchar,InqPol.EXPDTE,101),
LastTransactionDt = '' ,
PremiumAmount = InqPol.POLPREM,
LOB = InqPol.LOB,
UserID = InqPolUID.UserID,
LOC = REPLICATE ('0', 2-DATALENGTH(CAST(InqPol.LOC AS VARCHAR(2)))) + CAST(InqPol.LOC AS VARCHAR(2)),
MCO = InqPol.MCO,
PCO = REPLICATE ('0', 2-DATALENGTH(CAST(InqPol.PCO AS VARCHAR(2)))) + CAST(InqPol.PCO AS VARCHAR(2)),
HistoryParent = Null,
HistorySequence = Null,
SequenceNumber = '',
UnderwritingStatusIndicator = 0,
UserMessageIndicator=0,
UnderwriterMessageIndicator=0,
PolicyStatus = 'Available to Renew',
MessageApplicationID = '',
QuickQuoteInd = 0,
IteratedQuotes = 0
, CAST(InqPol.NBRDAYSLFT AS Varchar (10)) as DaysLeftToIssue
, ArchviedAfterBindHold = 0
,0 As RenewalInd
, '' As RecordCount
FROM DBPP1_Inquiry.dbo.PX002000R InqPol
LEFT JOIN SHSeleStateCode State on State.alias_state_code = InqPol.RATESTE and State.Host = 'PT'
LEFT JOIN DBPP1_Inquiry.dbo.PX002000 InqPolUID on InqPolUID.LOC = InqPol.LOC and InqPolUID.MCO = InqPol.MCO AND InqPolUID.MODULE = InqPol.MODULE AND InqPolUID.POLNUM = InqPol.POLNUM
WHERE InqPol.POLNUM > 0 AND RTRIM(InqPol.SYMBOL) + InqPol.POLNUM + CONVERT(VARCHAR(2), InqPol.MODULE) NOT IN
(SELECT rtrim(Symbol) + PolicyNumber + CONVERT(VARCHAR(2), ModuleName)
FROM SHPlanInfo WHERE SHPlanInfo.PolicyNumber LIKE InqPol.POLNUM
AND LazyDeleteInd <> 1 AND Issued = 0 AND PolicyStatusCd <> 'N' AND PolicyStatusCd <> 'O' ) AND InqPol.LOB LIKE 'WC%'
;With SearchSet as (Select Row_number() OVER (ORDER BY ApplicantName) AS RowNum,* From @IndexTable)
SELECT (SELECT (MAX(RowNum)/10) FROM Searchset) AS TotalRowCount,
ApplicantName = Substring(ApplicantName,0, 30),
PremiumAmount = '$' + Cast(PremiumAmount As Varchar),
EffectiveDate = Convert(varchar,EffectiveDate,101),
* FROM Searchset WHERE RowNum BETWEEN (1) AND (10)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 08:08:44

whats the datatype of POLPREM column in DBPP1_Inquiry.dbo.PX002000R ?

musman
Starting Member

17 Posts

Posted - 2008-09-30 : 08:13:30

Its POLPREM(numeric(11,2), null).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 08:22:10

Can you spot anywhere else in your query where you're setting value for numeric field?its very difficult to spot from posted code.

musman
Starting Member

17 Posts

Posted - 2008-09-30 : 08:31:48

I can run the Select query. But when I do insert in the table. I think there I get this error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 10:13:32

check if values you're trying to insert to table fields match their definition.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 11:26:33

[code]DECLARE @IndexTable TABLE
(
ApplicationID VARCHAR(30),
PolicyNumber VARCHAR(300),
PolicyNumberRoster VARCHAR(300),
OriginalPolicyNumber VARCHAR(300),
ProgramCode CHAR(2),
EnterpriseCd CHAR(2),
AgentName VARCHAR(50),
RiskState CHAR(2),
PolicyState CHAR(2),
ApplicantName VARCHAR(100),
EffectiveDate DATETIME,
ExpirationDate DATETIME,
LastTransactionDt DATETIME,
PremiumAmount DECIMAL(12, 2),
LOB CHAR(3),
UserID VARCHAR(20),
LOC CHAR(2),
MCO CHAR(2),
PCO CHAR(2),
HistoryParent VARCHAR(30),
HistorySequence VARCHAR(30),
SequenceNumber INT,
UnderwritingStatusIndicator SMALLINT,
UserMessageIndicator SMALLINT,
UnderwriterMessageIndicator SMALLINT,
PolicyStatusCd CHAR(2),
PolicyStatus VARCHAR(30),
MessageApplicationID VARCHAR(30),
QuickQuoteInd BIT,
IteratedQuotes VARCHAR(MAX),
DaysLeftToIssue INT,
ArchviedAfterBindHold CHAR(1),
RenewalInd CHAR(1),
RecordCount INT
)

INSERT @IndexTable
SELECT InqPol.SYMBOL + ':' + InqPol.POLNUM + ':' + CONVERT(VARCHAR(2), InqPol.MODULE) + ':' + InqPol.LOB AS ApplicationID,
InqPol.SYMBOL + InqPol.POLNUM + '-' + RIGHT('00' + InqPol.Module, 2) AS PolicyNumber,
InqPol.SYMBOL + InqPol.POLNUM + '-' + RIGHT('00' + InqPol.MODULE, 2) AS PolicyNumberRoster,
'' AS OriginalPolicyNumber,
State.StateCode AS ProgramCode,
'' AS EnterpriseCd,
'' AS AgentName,
State.StateCode AS RiskState,
State.StateCode AS RiskState,
'I' AS PolicyStatusCd,
REPLACE(ISNULL(InqPol.ORGANZTN, ''), '''', '#39;') AS ApplicantName,
InqPol.EFFDTE AS EffectiveDate,
CONVERT(VARCHAR(10), InqPol.EXPDTE, 101) AS ExpirationDate,
'' AS LastTransactionDt,
CASE ISNUMERIC(InqPol.POLPREM)
WHEN 1 THEN CONVERT(DECIMAL(12, 2), InqPol.POLPREM)
ELSE NULL
END AS PremiumAmount,
InqPol.LOB AS LOB,
InqPolUID.UserID AS UserID,
RIGHT('00' + InqPol.LOC, 2) AS LOC,
InqPol.MCO AS MCO,
RIGHT('00', InqPol.PCO, 2) AS PCO,
NULL AS HistoryParent,
NULL AS HistorySequence,
'' AS SequenceNumber,
0 AS UnderwritingStatusIndicator,
0 AS UserMessageIndicator,
0 AS UnderwriterMessageIndicator,
'Available to Renew' AS PolicyStatus,
'' AS MessageApplicationID,
0 AS QuickQuoteInd,
0 AS IteratedQuotes,
CASE ISNUMERIC(InqPol.NBRDAYSLFT)
WHEN 1 THEN CONVERT(INT, InqPol.NBRDAYSLFT)
ELSE NULL
END AS DaysLeftToIssue,
0 AS ArchviedAfterBindHold,
0 AS RenewalInd ,
'' AS RecordCount
FROM DBPP1_Inquiry.dbo.PX002000R AS InqPol
LEFT JOIN SHSeleStateCode AS [State] ON State.alias_state_code = InqPol.RATESTE
AND State.Host = 'PT'
LEFT JOIN DBPP1_Inquiry.dbo.PX002000 AS InqPolUID ON InqPolUID.LOC = InqPol.LOC
AND InqPolUID.MCO = InqPol.MCO
AND InqPolUID.MODULE = InqPol.MODULE
AND InqPolUID.POLNUM = InqPol.POLNUM
WHERE InqPol.POLNUM > 0
AND InqPol.LOB LIKE 'WC%'
AND NOT EXISTS (
SELECT *
FROM SHPlanInfo AS x
WHERE x.PolicyNumber LIKE InqPol.POLNUM
AND x.LazyDeleteInd <> 1
AND x.Issued = 0
AND x.PolicyStatusCd NOT IN ('N', 'O')
AND RTRIM(x.Symbol) + x.PolicyNumber + CONVERT(VARCHAR(2), x.ModuleName) <> RTRIM(InqPol.SYMBOL) + InqPol.POLNUM + CONVERT(VARCHAR(2), InqPol.MODULE)
)

;WITH SearchSet
AS (
SELECT ROW_NUMBER() OVER (ORDER BY ApplicantName) AS RowNum,
*
FROM @IndexTable
)

SELECT (SELECT (MAX(RowNum) / 10) FROM Searchset) AS TotalRowCount,
SUBSTRING(ApplicantName, 1, 30) AS ApplicantName,
'$' + CAST(PremiumAmount AS VARCHAR(12)) AS PremiumAmount,
CONVERT(VARCHAR(10), EffectiveDate, 101) AS EffectiveDate,
*
FROM Searchset
WHERE RowNum BETWEEN 1 AND 10[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 11:31:44

I dont think that CTE is required as it does nothing more than just generating rownumber which can be done with help of a derived table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 11:35:11

Also

The ISNUMERIC() check will return 1 even if it contains some alphabetic characters like e which can still break when casting to int.
And as OP has told earlier that POLPREM is Numeric ISNUMERIC(InqPol.POLPREM) check is also not required.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 11:44:24

Well, I hope OP has some restrictions of what can be inserted into the columns.
This is one way to start checking where the error is.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources