Thread: how to make select for multiunit

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

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.

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@postgresql.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/

---

From: Rene Romero Benavides
Sent: 15/01/2012 05:26
To: pgsql-general@postgresql.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.