[RESOLVED] Creating tables with 'CASE' statement?

[Greyskull]

Is it possible to use the CASE statement in a CREATE TABLE statement. I just thought that I could make the database more accurate this way. Say if i refer to products sold in a supermarket, some are measured by quantity and some are measured by weight. So if possible to have this statement:

 
CREATE TABLE tblProduct
(
	ProdCode	CHAR(8)		PRIMARY KEY,
	ProdType	VARCHAR(8),
	Prod_Desc	VARCHAR(40),
	Unit		VARCHAR(12),
	CentreName	CHAR(12)	REFERENCES tblDistCentre(CentreName),
	/*DCStockLvl	(type to be decided by case statement) */
);

If the prodtype is FRESH (goods measured by weight) then I the DCStockLvl type will be FLOAT. However if the ProdType is 'PACKAGED' (goods measured by unit) then the type will be just a SMALLINT.
The thought of using a CASE statement just occured to me, atm Ive just set-it up so that the type will just be a FLOAT.

Thanks in advance,

[brucevde]

A Field can only have one datatype.

[Greyskull]

I initially thought that but somehow i just wanted to be creative. lol.
Thanks

[szlamany]

Keep in mind that FLOAT datatypes yield inaccurate results in math calculations.

It's always best to use a DECIMAL() or MONEY datatype.