Returning Numeric values exactly as inserted

Question

Returning Numeric values exactly as inserted

banningj

SSC Rookie

Points: 34
More actions
December 1, 2003 at 4:46 pm

#101780

I am inserting numeric values from 0.01 to 9.99 into a SQL 2000 database using the Numeric(3,2) datatype. Occasionally the values will only have 1 or even 0 decimal places, 1.2 or 3. For data validation purposes, if the value inserted was 1.2, I need to have 1.2 returned from a query. When I look at this in Query Analyzer, I always get the trailing zeros. But in Enterprise Manager, it shows the value as entered.
How can I get this to work?
Thanks,

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

EM has many limitations in the GUI outputs, this is just one of those. What is your goal and where is it ultimately going to be viewed?

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

For data validation purposes, where is the difference between 1.2 and 1.20?

You can always use the char or varchar data type, but I guess that doesn't make your life easier at all.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

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

banningj SSC Rookie Points: 34 More actions · Answer 3

I am dealing with an application that stores data records in text files. When data records are written to the text files, decimal values are not written with a consistent precision. I am writing another VB application that will put those records in a database.

Each record in the text file has an associated hash value to ensure data integrity. The hash value is based on all of the characters in the record. After I write the record into the database, I need to read it back from the table, recreate the hash and compare it to what is in the original text file. At that point, there is a difference between 1.2 and 1.20.

The decimal values are temperature and humidity readings with their associated voltage outputs. I would really like to store these readings as decimal values. But it looks like that might not be feasible given my hashing requirements.

Looking at the data in Enterprise Manager gave me some hope that SQL 2000 somehow "remembers" exactly how a decimal value is entered. If I can't make that work, varchar it is.

Thanks for your input.

Jeff

cmore SSCrazy Points: 2452 More actions · Answer 4

Would using char/varchar but having a check on the field to ensure numeric(3,2) validity help?

banningj SSC Rookie Points: 34 More actions · Answer 5

I forgot one small item. The main reason for putting the data in a database is for easy reporting. Using the SQL aggregate functions, AVG, STDEV, MIN, MAX, etc. over a time period is the information we are looking for. It is alot simpler to do that on decimal fields. If I am forced to use varchar, then my reporting gets a little messier.

Thanks again.

linto SSC Eights! Points: 851 More actions · Answer 6

Why don't you write the code in the VB application to convert to numeric after reading from the text file and compare for the validity of data stored ??

Linto

David Burrows SSC Guru Points: 65143 More actions · Answer 7

Store value as both varchar and decimal, use the varchar to show original input and use decimal value for calculations.

Far away is close at hand in the images of elsewhere.
Anon.

banningj SSC Rookie Points: 34 More actions · Answer 8

Thanks for all of your input. Since I haven't seen any replies about returning a decimal value that has not been modified by the leading or trailing zeros, I will have to trudge forward with alternative plans.

Writing the numbers to a varchar and a decimal field is probably my best bet. But, this data could be audited by FDA regulators and they might not appreciate verifying data in one field and reporting from another.

Jeff

mia Right there with Babe Points: 749 More actions · Answer 9

Probably doesn't add anything to the discussion, but would using a computed column expression help with the FDA issue? If you have a varchar column NUM_AS_STRING you could create the decimal column in your table creation statement as follows:



NUM_AS_STRING varchar(15) null,

NUM_AS_NUM as cast(NUM_AS_STRING as numeric(3, 2))

Then you only need to insert into the varchar column. The numeric column will not be a different value, just a different representation. Mind you, knowing what regulators are like, this might not make a difference to them

Cheers,

mia

Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.

Cheers,
mia

Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.

tymberwyld SSCertifiable Points: 7810 More actions · Answer 10

I got around this issue by using a "SmallMoney" data type instead of Real, Decimal, or Float. When I tried using the other data types, a Value of "4.5" would be entered into SQL as "4.44449" or something similar. It was very annoying!

SQLBill SSC Guru Points: 51440 More actions · Answer 11

How about STORING the data as CHAR or VARCHAR and when you need to do calculations, CAST the data to DECIMAL.

Not a 'pretty' solution, but you only need to store the data once.

-SQLBill