SQL Query converting fields

Question

SQL Query converting fields

agarwalshailesh

Mr or Mrs. 500

Points: 517
More actions
August 25, 2003 at 1:05 am

#83053

I have a table with structure as follows
Column Name data typedescription
ItemID char(10)
AddSub char(1)stores '+' or '-'
Qty numeric
I have a query where i can combine the two columns AddSub and Qty
Select ItemId, convert(numeric,addsub + convert(varchar,qty)) from
It gives the error as following,
Error converting data type varchar to numeric.
I found that the problem is occuring due to conversion from one datatype to another. What happens is, sometimes there are small decimal values in the qty field of the table
like 5.0000000000000003E-2, so when I convert it into varchar, and back to numeric, it gives an error because then 'E' is not a valid numeric value.
Does anyone knows how to solve this problem? Thanks in advance

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi agarwalshailesh,

quote:
I have a table with structure as follows
Column Name data typedescription
ItemID char(10)
AddSub char(1)stores '+' or '-'
Qty numeric
I have a query where i can combine the two columns AddSub and Qty
Select ItemId, convert(numeric,addsub + convert(varchar,qty)) from
It gives the error as following,
Error converting data type varchar to numeric.
I found that the problem is occuring due to conversion from one datatype to another. What happens is, sometimes there are small decimal values in the qty field of the table
like 5.0000000000000003E-2, so when I convert it into varchar, and back to numeric, it gives an error because then 'E' is not a valid numeric value.

try using

SELECT CAST(<your_field> as numeric(8,2))

SELECT CONVERT(numeric(8,2), <your_field>)

to set the scale

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

agarwalshailesh Mr or Mrs. 500 Points: 517 More actions · Answer 2

Thanks for the reply, i tried

Select ItemId, AddSub,Qty, Cast(Qty as numeric(8,4))

From InventoryAdjustmentDetail

It gave me this error

Arithmetic overflow error converting float to data type numeric.

I dont understand why?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

quote:
Thanks for the reply, i tried
Select ItemId, AddSub,Qty, Cast(Qty as numeric(8,4))
From InventoryAdjustmentDetail
It gave me this error
Arithmetic overflow error converting float to data type numeric.
I dont understand why?

can you post some sample data?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

agarwalshailesh Mr or Mrs. 500 Points: 517 More actions · Answer 4

Please find the sample data

I000000068-86814.0

I000000068-450000.0

I000000068-600000.0

I000000068-1050000.0

I000000068-200.0

I000000069-100000.0

I000000069-67066.0

I000000069+450000.0

I000000069+4028.0

I000000069-2100000.0

I000000070-1.3

I000000070-221.56999999999999

I000000070-2.5499999999999998

I000000074-0.23000000000000001

I000000076-0.40999999999999998

I000000078-0.23000000000000001

I000000078-0.65000000000000002

I000000078+5.0000000000000003E-2

I000000078+0.65000000000000002

I000000082-2.9999999999999999E-2

I000000082-4.0000000000000001E-2

I000000086-0.01

I000000086-5.0000000000000003E-2

I000000090+0.0

I000000163+0.5

I000000571-0.80000000000000004

I000000581-0.11

I000000583-0.67000000000000004

I000001165-0.35999999999999999

I000001364+0.0

I000001544+0.90000000000000002

I000001589+0.90000000000000002

I000001611-0.0

I000001691-0.28999999999999998

I000001737-0.82999999999999996

I000001745-0.53000000000000003

I000001768-0.33000000000000002

I000001769-0.66000000000000003

I000001807+0.0

I000001970+0.0

I000001990-0.5

I000002666+0.01

I000002737+0.67000000000000004

I000002739-0.5

I000002775-0.12

I000003284+0.01

I000003284+0.63

I000003333-0.98999999999999999

I000003333+0.02

I000003333-0.01

I000000069-2100000.0

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

quote:
Please find the sample data
I000000068-86814.0
I000000068-450000.0
I000000068-600000.0
I000000068-1050000.0
I000000068-200.0
I000000069-100000.0
I000000069-67066.0
I000000069+450000.0
I000000069+4028.0
I000000069-2100000.0
I000000069-2100000.0
I000000069-2100000.0
I000000070-1.3
I000000070-221.56999999999999
I000000070-2.5499999999999998
I000000074-0.23000000000000001
I000000076-0.40999999999999998
I000000078-0.23000000000000001
I000000078-0.65000000000000002
I000000078+5.0000000000000003E-2
I000000078+0.65000000000000002
I000000082-2.9999999999999999E-2
I000000082-4.0000000000000001E-2
I000000086-0.01
I000000086-5.0000000000000003E-2
I000000090+0.0
I000000163+0.5
I000000571-0.80000000000000004
I000000581-0.11
I000000583-0.67000000000000004
I000001165-0.35999999999999999
I000001364+0.0
I000001544+0.90000000000000002
I000001589+0.90000000000000002
I000001611-0.0
I000001691-0.28999999999999998
I000001737-0.82999999999999996
I000001745-0.53000000000000003
I000001768-0.33000000000000002
I000001769-0.66000000000000003
I000001807+0.0
I000001970+0.0
I000001990-0.5
I000002666+0.01
I000002737+0.67000000000000004
I000002739-0.5
I000002775-0.12
I000003284+0.01
I000003284+0.01
I000003284+0.01
I000003284+0.63
I000003333-0.98999999999999999
I000003333+0.02
I000003333-0.01
I000000069-2100000.0
I000000069-2100000.0
I000000069-2100000.0

from this sample data you've provided, numeric wasn't your first chosen data type, right?

Interestingly, I don't get this error, when setting the scale to less than 4 places.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

agarwalshailesh Mr or Mrs. 500 Points: 517 More actions · Answer 6

The data is only a subset of the complete data i have. I have around 2800 rows of data. Should I send it and if yes, in what format? Also do we have a option to include an attachment with the message which we post

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

quote:
The data is only a subset of the complete data i have. I have around 2800 rows of data. Should I send it and if yes, in what format? Also do we have a option to include an attachment with the message which we post

ok,

-what so you need this data for?

-how complex is the calculation?

-is a scale of 2 sufficient?

-could you send the CREATE TABLE statement?

No, you can't send attachment via the forum sofware. And I don't think this is neccessary

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

agarwalshailesh Mr or Mrs. 500 Points: 517 More actions · Answer 8

I need a scale of 5 decimals. Following is the Create Table Statement for your reference

CREATE TABLE [dbo].[ItemQty] (

[ItemId] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[AddSub] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Qty] [float] NULL

) ON [PRIMARY]

GO

agarwalshailesh Mr or Mrs. 500 Points: 517 More actions · Answer 9

I have deduce the problem. The mistake i was doing is using the statement as

Select ItemId, AddSub,Qty, Cast(Qty as numeric(8,4))

From InventoryAdjustmentDetail

instead of

Select ItemId, AddSub,Qty, Cast(Qty as numeric(18,4))

From InventoryAdjustmentDetail

Thanks Frank for your help

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

Hey, that's good. I was struggling a little bit on a solution

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]