X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id D892C475AA9
	for <pgsql-sql@postgresql.org>; Mon, 31 Mar 2003 17:24:49 -0500 (EST)
Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66])
	by postgresql.org (Postfix) with ESMTP id 86FE347580B
	for <pgsql-sql@postgresql.org>; Mon, 31 Mar 2003 17:24:48 -0500 (EST)
Received: from klaster.net (unknown [213.76.36.234])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id 60ED26A27E; Tue,  1 Apr 2003 00:02:35 +0200 (CEST)
Message-ID: <3E8881C7.8030608@klaster.net>
Date: Mon, 31 Mar 2003 19:58:31 +0200
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL;
 rv:1.3) Gecko/20030312
X-Accept-Language: pl, en-us, en
MIME-Version: 1.0
To: cio198 <cio198@plasa.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: create view with numeric data
References: <8d6dd4ad.0303310247.2dded41c@posting.google.com>
In-Reply-To: <8d6dd4ad.0303310247.2dded41c@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
X-Spam-Status: No, hits=-29.4 required=5.0
	tests=BAYES_10,DATE_IN_PAST_03_06,IN_REP_TO,QUOTED_EMAIL_TEXT,
	RCVD_IN_RFCI,REFERENCES,REPLY_WITH_QUOTES,UPPERCASE_25_50,
	USER_AGENT_MOZILLA_UA autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200303/663
X-Sequence-Number: 12701

Uz.ytkownik cio198 napisa?:
> Hello,=20
>=20
> i've created this table
>=20
> CREATE TABLE "budget" (
>         "year" character varying NOT NULL,
>         "month" character varying NOT NULL,
>         "accountno" character varying NOT NULL,
>         "costid" character varying NOT NULL,
>         "valutacode" character varying,
>         "budgetvalue" numeric(9,2) DEFAULT '0',
>         "deptname" character varying,
>         Constraint "budget_pkey" Primary Key ("year", "month",
> "accountno", "costid")
> );
>=20
> And I want to create the a view using query bellow.
> The problem is the numeric data in the view isn't limited to
> numeric(9,2) instead it become numeric(65535, 65531).
> Is there any way i can restrict it to numeric (9,2)
>=20
> TIA
>=20
>=20
> CREATE VIEW view_budget
> AS      SELECT b.year, b.accountno, a.name,
>         sum(CASE WHEN month=3D'01' THEN budgetvalue ELSE '0' END) AS
> january,
>         sum(CASE WHEN month=3D'02' THEN budgetvalue ELSE '0' END) AS
> february,
>         sum(CASE WHEN month=3D'03' THEN budgetvalue ELSE '0' END) AS
> march,
>         sum(CASE WHEN month=3D'04' THEN budgetvalue ELSE '0' END) AS
> april,
>         sum(CASE WHEN month=3D'05' THEN budgetvalue ELSE '0' END) AS
> may,
>         sum(CASE WHEN month=3D'06' THEN budgetvalue ELSE '0' END) AS
> june,
>         sum(CASE WHEN month=3D'07' THEN budgetvalue ELSE '0' END) AS
> july,
>         sum(CASE WHEN month=3D'08' THEN budgetvalue ELSE '0' END) AS
> august,
>         sum(CASE WHEN month=3D'09' THEN budgetvalue ELSE '0' END) AS
> september,
>         sum(CASE WHEN month=3D'10' THEN budgetvalue ELSE '0' END) AS
> october,
>         sum(CASE WHEN month=3D'11' THEN budgetvalue ELSE '0' END) AS
> november,
>         sum(CASE WHEN month=3D'12' THEN budgetvalue ELSE '0' END) AS
> december,
>         sum(budgetvalue) as totalvalue
>         FROM budget b inner join account a on b.accountno=3Da.accountno
>         GROUP BY year, b.accountno, a.name
>         ORDER BY b.accountno;
Sure, change all of your sums to:
   cast(sum(...) as numeric(9,2)) as ...

Regards,
Tomasz Myrta