Skip site navigation (1) Skip section navigation (2)

Re: Pls Hlp: SQL Problem

From: Hengky Lie <hengkyliwandouw(at)gmail(dot)com>
To: pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Pls Hlp: SQL Problem
Date: 2008-09-12 18:50:46
Message-ID: 48CABA06.3070002@gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Dear Richard,<br>
<br>
Sorry for that :) and thanks for your tips, but it didn't solve my
problem. Maybe because my postgresql knowledge 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>
&nbsp;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>
&nbsp;&nbsp; FROM tblpenjualan<br>
&nbsp;&nbsp; JOIN tbltransaksi ON tblpenjualan.id = tbltransaksi.jualid<br>
&nbsp;&nbsp; LEFT JOIN tblproduk ON tbltransaksi.kodeproduk::text =
tblproduk.kode::text<br>
&nbsp;&nbsp; LEFT JOIN tblsubkategori ON tblproduk.subkat::text =
tblsubkategori.id::text<br>
&nbsp;&nbsp; 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;"ELIPS HAIR
VITAMIN ";"121";"VITAMIN RAMBUT";"NF";"Non Food";3960.00;<font
 color="#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(dot)1050501(at)archonet(dot)com" type="cite">
  <pre wrap="">Hengky Lie wrote:
  </pre>
  <blockquote type="cite">
    <pre wrap="">Dear friends,
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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>
  <blockquote type="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>
  <pre wrap=""><!---->
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>
</body>
</html>


Attachment: unknown_filename
Description: text/html (3.3 KB)

In response to

Responses

pgsql-sql by date

Next:From: Scott MarloweDate: 2008-09-12 20:04:58
Subject: Re: Pls Hlp: SQL Problem
Previous:From: Rafael DomicianoDate: 2008-09-12 18:14:08
Subject: Doubts about FK

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group