Re: Pls Hlp: SQL Problem - Mailing list pgsql-sql

Dear Richard,<br /><br /> Sorry for that :) and thanks for your tips, but it didn't solve my problem. Maybe because my
postgresqlknowledge limitation. I have tried your suggestion, my view like this :<br /><br /> -- View: "vwlapjual"<br
/><br/> -- DROP VIEW vwlapjual;<br /><br /> CREATE OR REPLACE VIEW vwlapjual AS <br />  SELECT tblpenjualan.tanggal,
tblpenjualan."operator",tblpenjualan.noinvoice, tblpenjualan.bayar, tblpenjualan.bayarvocher, tblpenjualan.jam,
tblpenjualan.id,tblpenjualan.jeniscard, tbltransaksi.kodeproduk, tbltransaksi.keluar, tbltransaksi.harga,
tbltransaksi.disc1,tblproduk.namabarang, tblproduk.subkat, tblsubkategori.subkategori, tblkategoriproduk.kode,
tblkategoriproduk.kategori,tbltransaksi.hargapokok * tbltransaksi.keluar::numeric(2,0) AS modal, <font
color="#ff0000"><b>tbltransaksi.keluar* tbltransaksi.harga - (tbltransaksi.disc1 / 100::numeric(3,0) *
(tbltransaksi.keluar* tbltransaksi.harga))::numeric(10,0) AS jumlah</b></font>, <font
color="#ff0000"><b>tbltransaksi.keluar* tbltransaksi.harga - tbltransaksi.disc1 / 100::numeric(3,0) *
(tbltransaksi.keluar* tbltransaksi.harga) - (tbltransaksi.hargapokok * tbltransaksi.keluar)::numeric(10,0) AS
profit</b></font><br/>    FROM tblpenjualan<br />    JOIN tbltransaksi ON tblpenjualan.id = tbltransaksi.jualid<br />
  LEFT JOIN tblproduk ON tbltransaksi.kodeproduk::text = tblproduk.kode::text<br />    LEFT JOIN tblsubkategori ON
tblproduk.subkat::text= tblsubkategori.id::text<br />    LEFT JOIN tblkategoriproduk ON tblsubkategori.kategoriid::text
=tblkategoriproduk.kode::text;<br /><br /> ALTER TABLE vwlapjual OWNER TO hpl;<br /><br /> and the view result like
this:<br /><br /> "2008-07-20";"ULY";"01-200708";"Tunai";0.00;"1899-12-30
21:14:47+06:55:25";1;"";"8993417200021";1.00;4200.00;0.00;"ELIPSHAIR VITAMIN ";"121";"VITAMIN RAMBUT";"NF";"Non
Food";3960.00;<fontcolor="#ff0000"><b>4200.0000</b></font>;<font
color="#ff0000"><b>240.000000000000000000000000</b></font><br/><br /> Thanks for any suggestion.<br /><br />
Regards,<br/> Hengky<br /><br /><br /> Richard Huxton wrote: <blockquote cite="mid:48CA432A.1050501@archonet.com"
type="cite"><prewrap="">Hengky Lie wrote: </pre><blockquote type="cite"><pre wrap="">Dear friends,
</pre></blockquote><prewrap="">
 
Hello. Quick tip - don't reply to another message when starting a new
thread, people will assume it's part of the old thread.

[snip most of query] </pre><blockquote type="cite"><pre wrap="">/ 100::numeric *    </pre></blockquote><pre wrap="">
</pre><blockquotetype="cite"><pre
wrap="">Food";3960.0000;*4200.000000000000000000000000*;*240.000000000000000000000000*  </pre></blockquote><pre
wrap=""></pre><blockquote type="cite"><pre wrap="">Why  the calculation result has so many decimals ?
</pre></blockquote><prewrap="">
 
Because you've cast 100 to numeric, rather than numeric(10,2) or
whatever. This means you get the full accuracy on the calculation - it
won't ever trim a numeric (that's the whole reason to have the type).
 </pre></blockquote><br />