Differing Decimals
I'm working on a database for the scales at my work place. We have a quite a few - and quite a few different types. They're categorized by capacity and resolution. One might be 320g x 0.001g (320 gram max and shows 3 decimal places). Another might be 25 lb x 0.01 lb (25 pound max and shows 2 decimal places).
The scales have to be checked on a daily basis, so one of the tables I've built is a "standards" table. It has the test point, the tolerance, the units, and the number of decimals that the particular scale shows:
CREATE TABLE standards(
standID INT UNSIGNED NOT NULL AUTO_INCREMENT,
tp DOUBLE,
tol DOUBLE,
unit VARCHAR(3),
ndec INT UNSIGNED,
PRIMARY KEY(standID),
UNIQUE (tp,tol,unit,ndec)
);
I went with DOUBLE for the tolerance because the decimals are not fixed for all scales. I included the "number of decimals" (ndec) to compensate for that.
When I select the data, is there a way to use the value of one column for the precision of another?
I just tried a quick experiment:
SELECT standID, tp, CAST(tol AS DECIMAL(6,3)), unit, ndec FROM standards;
to see if the 'tol' column would display with 3 decimal places, but that didn't work either.