Re: how to make select for multiunit

Hi,

I have understood he does not want to round...

I would make custom type myUofM
KRT int, PAK int, PCS int

Then function to_myUofM which has 1 input parametar as int, and returns
myUofM, (imutable func)

so SELECT to_myUofM(25) returns
0,2,5

25 PCS is 0 KRT, 2 PAK and 5 PCS

Then query:

SELECT name, to_myUofM(qty).KRT, myUofM(qty).PAK, myUofM(qty).PCS FROM
stocks

Kind Regards,

Misa

Sent from my Windows Phone
------------------------------
From: Rene Romero Benavides
Sent: 15/01/2012 05:26
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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/