Re: how to make select for multiunit

On 01/14/2012 09:22 PM, plasmasoftware net wrote:

hello ...
i have stock table all stock in smallest unit ( PCS )

i have stock table like this

name qty  unit
PCA   20   PCS
MOUSE  25 PCS


i have table unit
1 KRT = 12 PAK
1 PAK = 10 PCS

how to make select to display report stok in dbGRID like this :

PCA 2 KRT 0 PAK 0 PCS
MOUSE 0 KRT 2 PAK 1 PCS

I don't understand,

Wouldn't have been:
PCA 0 KRT 2 PAK 20 PCS
MOUSE 0 KRT 2 PAK 25 PCS
?

anyways, if the "units" table was something like this :

unit convUnit ratio
PCS PCS          1
PCS KRT           1/120
PCS PAK           1/10
(foreseeing other types of conversions)

One simple solution  is:
SELECT s.name, (select s.qty * ratio FROM unit   WHERE unit='PCS' and conv='KRT'), ' KRT ',
                          (select s.qty * ratio FROM unit   WHERE unit='PCS' and conv='PAK'), ' PAK ',
                          s.qty, ' PCS'
 FROM stock s;

rounding or truncating is up to you.