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.

--
http://sharingtechknowledge.blogspot.com/