[RESOLVED] Creating tables with 'CASE' statement?

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:

Code:

---


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,

A Field can only have one datatype.

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

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

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