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/