Error converting data type varchar to numeric

microsoft.public.sqlserver.programming

24 Aug 2006 4:04 PM

hi!

my table definition is as follows:

                [amt_allowed] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
    [deduct] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [copay] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [cob] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [coins] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [amt_paid] [varchar] (9)

I get a fixed length text file with the fields.

I am using all varchar in my table just to run the following validation
for the above-mentioned fields:

--ERROR_CODE_C413
-- check for valid amount allowed
update ases_MI60_source.dbo.services_source_carrier
set error_code =error_code +', C413'
where tos_code <> 'Z' and trans_code <> 'E' and amt_allowed NOT LIKE
'%[^0-9]%' AND DATALENGTH(amt_allowed) <> 9

--ERROR_CODE_C414
-- check for valid deductible
update ases_MI60_source.dbo.services_source_carrier
set error_code =error_code +', C414'
where deduct NOT LIKE '%[^0-9]%' AND DATALENGTH(deduct) <> 9 and
trans_code = 'I'

--ERROR_CODE_C415
-- check for valid copay
update ases_MI60_source.dbo.services_source_carrier
set error_code =error_code +', C415'
where copay NOT LIKE '%[^0-9]%' AND DATALENGTH(copay) <> 9 and
trans_code = 'I'

--ERROR_CODE_C416
-- check for valid cob amount
update ases_MI60_source.dbo.services_source_carrier
set error_code =error_code +', C416'
where cob NOT LIKE '%[^0-9]%' AND DATALENGTH(cob) <> 9 and trans_code =
'I'

--ERROR_CODE_C417
-- check for valid coinsurance amount
update ases_MI60_source.dbo.services_source_carrier
set error_code =error_code +', C417'
where coins NOT LIKE '%[^0-9]%' AND DATALENGTH(coins) <> 9 and
trans_code = 'I'

I need to validate amt_paid like

amt_paid = amt_allowed - deduct - copay - cob - coins

I tried this:

update ases_MI60_source.dbo.services_source_carrier
set error_code =error_code +', C418.3'
where trans_code = 'I' and tos_code <> 'Z'
and (cast(amt_allowed as numeric) - cast(deduct as numeric) -
cast(copay as numeric) - cast(cob as numeric) - cast(coins as numeric))
<> cast(amt_paid as numeric)

but I get ther following error message:

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

how can I make sure that all those values equal to amt_paid correctly?

Thank you in advanced for your help!

T.

24 Aug 2006 4:12 PM

SQL Menace

try WHERE Value NOT LIKE '%[a-z]%'
AND ISNUMERIC(Value) = 1

check out this example

CREATE TABLE #foo (Value VARCHAR(20))
INSERT INTO #foo
SELECT '1' UNION ALL
SELECT '3' UNION ALL
SELECT 'B' UNION ALL
SELECT '2' UNION ALL
SELECT '33.331' UNION ALL
SELECT 'adad1' UNION ALL
SELECT '1d2' UNION ALL
SELECT '^' UNION ALL
SELECT '17777.999'

--returns ^
SELECT * FROM #foo
WHERE Value NOT LIKE '%[a-z]%'

--returns 1d2
SELECT * FROM #foo
WHERE ISNUMERIC(Value) = 1

--returns correct result
SELECT * FROM #foo
WHERE Value NOT LIKE '%[a-z]%'
AND ISNUMERIC(Value) = 1

Denis the SQL Menace
http://sqlservercode.blogspot.com/

TG wrote:
Show quote

> hi!
>
> my table definition is as follows:
>
>                 [amt_allowed] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
>     [deduct] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>     [copay] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>     [cob] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>     [coins] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>     [amt_paid] [varchar] (9)
>
> I get a fixed length text file with the fields.
>
> I am using all varchar in my table just to run the following validation
> for the above-mentioned fields:
>
> --ERROR_CODE_C413
> -- check for valid amount allowed
> update ases_MI60_source.dbo.services_source_carrier
> set error_code =error_code +', C413'
> where tos_code <> 'Z' and trans_code <> 'E' and amt_allowed NOT LIKE
> '%[^0-9]%' AND DATALENGTH(amt_allowed) <> 9
>
> --ERROR_CODE_C414
> -- check for valid deductible
> update ases_MI60_source.dbo.services_source_carrier
> set error_code =error_code +', C414'
> where deduct NOT LIKE '%[^0-9]%' AND DATALENGTH(deduct) <> 9 and
> trans_code = 'I'
>
> --ERROR_CODE_C415
> -- check for valid copay
> update ases_MI60_source.dbo.services_source_carrier
> set error_code =error_code +', C415'
> where copay NOT LIKE '%[^0-9]%' AND DATALENGTH(copay) <> 9 and
> trans_code = 'I'
>
> --ERROR_CODE_C416
> -- check for valid cob amount
> update ases_MI60_source.dbo.services_source_carrier
> set error_code =error_code +', C416'
> where cob NOT LIKE '%[^0-9]%' AND DATALENGTH(cob) <> 9 and trans_code =
> 'I'
>
> --ERROR_CODE_C417
> -- check for valid coinsurance amount
> update ases_MI60_source.dbo.services_source_carrier
> set error_code =error_code +', C417'
> where coins NOT LIKE '%[^0-9]%' AND DATALENGTH(coins) <> 9 and
> trans_code = 'I'
>
>
>
>
> I need to validate amt_paid like
>
> amt_paid = amt_allowed - deduct - copay - cob - coins
>
>
>
> I tried this:
>
> update ases_MI60_source.dbo.services_source_carrier
> set error_code =error_code +', C418.3'
> where trans_code = 'I' and tos_code <> 'Z'
> and (cast(amt_allowed as numeric) - cast(deduct as numeric) -
> cast(copay as numeric) - cast(cob as numeric) - cast(coins as numeric))
> <> cast(amt_paid as numeric)
>
> but I get ther following error message:
>
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to numeric.
>
>
>
> how can I make sure that all those values equal to amt_paid correctly?
>
>
>
> Thank you in advanced for your help!
>
>
> T.

24 Aug 2006 4:19 PM

thank you, but I do not see how this will insert the apropriate error
code if the formula does not meet the requirement.

SQL Menace wrote:
Show quote

> try WHERE Value NOT LIKE '%[a-z]%'
> AND ISNUMERIC(Value) = 1
>
> check out this example
>
> CREATE TABLE #foo (Value VARCHAR(20))
> INSERT INTO #foo
> SELECT '1' UNION ALL
> SELECT '3' UNION ALL
> SELECT 'B' UNION ALL
> SELECT '2' UNION ALL
> SELECT '33.331' UNION ALL
> SELECT 'adad1' UNION ALL
> SELECT '1d2' UNION ALL
> SELECT '^' UNION ALL
> SELECT '17777.999'
>
> --returns ^
> SELECT * FROM #foo
> WHERE Value NOT LIKE '%[a-z]%'
>
> --returns 1d2
> SELECT * FROM #foo
> WHERE ISNUMERIC(Value) = 1
>
> --returns correct result
> SELECT * FROM #foo
> WHERE Value NOT LIKE '%[a-z]%'
> AND ISNUMERIC(Value) = 1
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
>
> TG wrote:
> > hi!
> >
> > my table definition is as follows:
> >
> >                 [amt_allowed] [varchar] (9) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NULL ,
> >     [deduct] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >     [copay] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >     [cob] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >     [coins] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >     [amt_paid] [varchar] (9)
> >
> > I get a fixed length text file with the fields.
> >
> > I am using all varchar in my table just to run the following validation
> > for the above-mentioned fields:
> >
> > --ERROR_CODE_C413
> > -- check for valid amount allowed
> > update ases_MI60_source.dbo.services_source_carrier
> > set error_code =error_code +', C413'
> > where tos_code <> 'Z' and trans_code <> 'E' and amt_allowed NOT LIKE
> > '%[^0-9]%' AND DATALENGTH(amt_allowed) <> 9
> >
> > --ERROR_CODE_C414
> > -- check for valid deductible
> > update ases_MI60_source.dbo.services_source_carrier
> > set error_code =error_code +', C414'
> > where deduct NOT LIKE '%[^0-9]%' AND DATALENGTH(deduct) <> 9 and
> > trans_code = 'I'
> >
> > --ERROR_CODE_C415
> > -- check for valid copay
> > update ases_MI60_source.dbo.services_source_carrier
> > set error_code =error_code +', C415'
> > where copay NOT LIKE '%[^0-9]%' AND DATALENGTH(copay) <> 9 and
> > trans_code = 'I'
> >
> > --ERROR_CODE_C416
> > -- check for valid cob amount
> > update ases_MI60_source.dbo.services_source_carrier
> > set error_code =error_code +', C416'
> > where cob NOT LIKE '%[^0-9]%' AND DATALENGTH(cob) <> 9 and trans_code =
> > 'I'
> >
> > --ERROR_CODE_C417
> > -- check for valid coinsurance amount
> > update ases_MI60_source.dbo.services_source_carrier
> > set error_code =error_code +', C417'
> > where coins NOT LIKE '%[^0-9]%' AND DATALENGTH(coins) <> 9 and
> > trans_code = 'I'
> >
> >
> >
> >
> > I need to validate amt_paid like
> >
> > amt_paid = amt_allowed - deduct - copay - cob - coins
> >
> >
> >
> > I tried this:
> >
> > update ases_MI60_source.dbo.services_source_carrier
> > set error_code =error_code +', C418.3'
> > where trans_code = 'I' and tos_code <> 'Z'
> > and (cast(amt_allowed as numeric) - cast(deduct as numeric) -
> > cast(copay as numeric) - cast(cob as numeric) - cast(coins as numeric))
> > <> cast(amt_paid as numeric)
> >
> > but I get ther following error message:
> >
> > Server: Msg 8114, Level 16, State 5, Line 1
> > Error converting data type varchar to numeric.
> >
> >
> >
> > how can I make sure that all those values equal to amt_paid correctly?
> >
> >
> >
> > Thank you in advanced for your help!
> >
> >
> > T.

24 Aug 2006 4:30 PM

SQL Menace

oops I actually gave you a check for numeric not for not numeric
here try this

--ERROR_CODE_C666
-- check for valid numeric amount
update ases_MI60_source.dbo.services_source_carrier
set error_code =error_code +', C666'
where cob LIKE '%[a-z]%'
or ISNUMERIC(cob) = 0

and then this should have been my previous example

CREATE TABLE #foo (Value VARCHAR(20))
INSERT INTO #foo
SELECT '1' UNION ALL
SELECT '3' UNION ALL
SELECT 'B' UNION ALL
SELECT '2' UNION ALL
SELECT '33.331' UNION ALL
SELECT 'adad1' UNION ALL
SELECT '1d2' UNION ALL
SELECT '^' UNION ALL
SELECT '17777.999'

--returns non numeric results
SELECT * FROM #foo
WHERE Value LIKE '%[a-z]%'
or ISNUMERIC(Value) = 0

Denis the SQL Menace
http://sqlservercode.blogspot.com/

TG wrote:
Show quote

> thank you, but I do not see how this will insert the apropriate error
> code if the formula does not meet the requirement.
>
>
> SQL Menace wrote:
> > try WHERE Value NOT LIKE '%[a-z]%'
> > AND ISNUMERIC(Value) = 1
> >
> > check out this example
> >
> > CREATE TABLE #foo (Value VARCHAR(20))
> > INSERT INTO #foo
> > SELECT '1' UNION ALL
> > SELECT '3' UNION ALL
> > SELECT 'B' UNION ALL
> > SELECT '2' UNION ALL
> > SELECT '33.331' UNION ALL
> > SELECT 'adad1' UNION ALL
> > SELECT '1d2' UNION ALL
> > SELECT '^' UNION ALL
> > SELECT '17777.999'
> >
> > --returns ^
> > SELECT * FROM #foo
> > WHERE Value NOT LIKE '%[a-z]%'
> >
> > --returns 1d2
> > SELECT * FROM #foo
> > WHERE ISNUMERIC(Value) = 1
> >
> > --returns correct result
> > SELECT * FROM #foo
> > WHERE Value NOT LIKE '%[a-z]%'
> > AND ISNUMERIC(Value) = 1
> >
> > Denis the SQL Menace
> > http://sqlservercode.blogspot.com/
> >
> >
> >
> > TG wrote:
> > > hi!
> > >
> > > my table definition is as follows:
> > >
> > >                 [amt_allowed] [varchar] (9) COLLATE
> > > SQL_Latin1_General_CP1_CI_AS NULL ,
> > >     [deduct] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > >     [copay] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > >     [cob] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > >     [coins] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > >     [amt_paid] [varchar] (9)
> > >
> > > I get a fixed length text file with the fields.
> > >
> > > I am using all varchar in my table just to run the following validation
> > > for the above-mentioned fields:
> > >
> > > --ERROR_CODE_C413
> > > -- check for valid amount allowed
> > > update ases_MI60_source.dbo.services_source_carrier
> > > set error_code =error_code +', C413'
> > > where tos_code <> 'Z' and trans_code <> 'E' and amt_allowed NOT LIKE
> > > '%[^0-9]%' AND DATALENGTH(amt_allowed) <> 9
> > >
> > > --ERROR_CODE_C414
> > > -- check for valid deductible
> > > update ases_MI60_source.dbo.services_source_carrier
> > > set error_code =error_code +', C414'
> > > where deduct NOT LIKE '%[^0-9]%' AND DATALENGTH(deduct) <> 9 and
> > > trans_code = 'I'
> > >
> > > --ERROR_CODE_C415
> > > -- check for valid copay
> > > update ases_MI60_source.dbo.services_source_carrier
> > > set error_code =error_code +', C415'
> > > where copay NOT LIKE '%[^0-9]%' AND DATALENGTH(copay) <> 9 and
> > > trans_code = 'I'
> > >
> > > --ERROR_CODE_C416
> > > -- check for valid cob amount
> > > update ases_MI60_source.dbo.services_source_carrier
> > > set error_code =error_code +', C416'
> > > where cob NOT LIKE '%[^0-9]%' AND DATALENGTH(cob) <> 9 and trans_code =
> > > 'I'
> > >
> > > --ERROR_CODE_C417
> > > -- check for valid coinsurance amount
> > > update ases_MI60_source.dbo.services_source_carrier
> > > set error_code =error_code +', C417'
> > > where coins NOT LIKE '%[^0-9]%' AND DATALENGTH(coins) <> 9 and
> > > trans_code = 'I'
> > >
> > >
> > >
> > >
> > > I need to validate amt_paid like
> > >
> > > amt_paid = amt_allowed - deduct - copay - cob - coins
> > >
> > >
> > >
> > > I tried this:
> > >
> > > update ases_MI60_source.dbo.services_source_carrier
> > > set error_code =error_code +', C418.3'
> > > where trans_code = 'I' and tos_code <> 'Z'
> > > and (cast(amt_allowed as numeric) - cast(deduct as numeric) -
> > > cast(copay as numeric) - cast(cob as numeric) - cast(coins as numeric))
> > > <> cast(amt_paid as numeric)
> > >
> > > but I get ther following error message:
> > >
> > > Server: Msg 8114, Level 16, State 5, Line 1
> > > Error converting data type varchar to numeric.
> > >
> > >
> > >
> > > how can I make sure that all those values equal to amt_paid correctly?
> > >
> > >
> > >
> > > Thank you in advanced for your help!
> > >
> > >
> > > T.

24 Aug 2006 4:26 PM

Roy Harvey

In all the earlier UPDATEs you filtered to only deal with valid data,
but when you are adding them up I do not see that filtering. Perhaps
it is as simple as adding some tests:

   and amt_allowed NOT LIKE '%[^0-9]%'
   and deduct NOT LIKE '%[^0-9]%'
   and copay NOT LIKE '%[^0-9]%'
   and cob NOT LIKE '%[^0-9]%'
   and coins NOT LIKE '%[^0-9]%'
   and amt_paid NOT LIKE '%[^0-9]%'
   and DATALENGTH(amt_allowed) <> 9
   and DATALENGTH(deduct) <> 9
   and DATALENGTH(copay) <> 9
   and DATALENGTH(cob) <> 9
   and DATALENGTH(coins) <> 9
   and DATALENGTH(amt_paid) <> 9

You might at least see if this avoids the error.

Now, what you are supposed to do when just ONE of the columns has a
problem is still for you to decide. Substitute a zero? Mark it as an
error?

Roy Harvey
Beacon Falls, CT

Show quote

On 24 Aug 2006 09:04:03 -0700, "TG" <jtam***@yahoo.com> wrote:

>hi!
>
>my table definition is as follows:
>
>                [amt_allowed] [varchar] (9) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [deduct] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>    [copay] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>    [cob] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>    [coins] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>    [amt_paid] [varchar] (9)
>
>I get a fixed length text file with the fields.
>
>I am using all varchar in my table just to run the following validation
>for the above-mentioned fields:
>
> --ERROR_CODE_C413
> -- check for valid amount allowed
>update ases_MI60_source.dbo.services_source_carrier
>set error_code =error_code +', C413'
>where tos_code <> 'Z' and trans_code <> 'E' and amt_allowed NOT LIKE
>'%[^0-9]%' AND DATALENGTH(amt_allowed) <> 9
>
> --ERROR_CODE_C414
> -- check for valid deductible
>update ases_MI60_source.dbo.services_source_carrier
>set error_code =error_code +', C414'
>where deduct NOT LIKE '%[^0-9]%' AND DATALENGTH(deduct) <> 9 and
>trans_code = 'I'
>
> --ERROR_CODE_C415
> -- check for valid copay
>update ases_MI60_source.dbo.services_source_carrier
>set error_code =error_code +', C415'
>where copay NOT LIKE '%[^0-9]%' AND DATALENGTH(copay) <> 9 and
>trans_code = 'I'
>
> --ERROR_CODE_C416
> -- check for valid cob amount
>update ases_MI60_source.dbo.services_source_carrier
>set error_code =error_code +', C416'
>where cob NOT LIKE '%[^0-9]%' AND DATALENGTH(cob) <> 9 and trans_code =
>'I'
>
> --ERROR_CODE_C417
> -- check for valid coinsurance amount
>update ases_MI60_source.dbo.services_source_carrier
>set error_code =error_code +', C417'
>where coins NOT LIKE '%[^0-9]%' AND DATALENGTH(coins) <> 9 and
>trans_code = 'I'
>
>
>
>
>I need to validate amt_paid like
>
>amt_paid = amt_allowed - deduct - copay - cob - coins
>
>
>
>I tried this:
>
>update ases_MI60_source.dbo.services_source_carrier
>set error_code =error_code +', C418.3'
>where trans_code = 'I' and tos_code <> 'Z'
>and (cast(amt_allowed as numeric) - cast(deduct as numeric) -
>cast(copay as numeric) - cast(cob as numeric) - cast(coins as numeric))
><> cast(amt_paid as numeric)
>
>but I get ther following error message:
>
>Server: Msg 8114, Level 16, State 5, Line 1
>Error converting data type varchar to numeric.
>
>
>
>how can I make sure that all those values equal to amt_paid correctly?
>
>
>
>Thank you in advanced for your help!
>
>
>T.