X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 7ABFB4764F9
	for <pgsql-sql@postgresql.org>; Mon, 31 Mar 2003 10:40:57 -0500 (EST)
Received: from news.hub.org (news.hub.org [64.49.215.80])
	by postgresql.org (Postfix) with ESMTP id 7B5654765F9
	for <pgsql-sql@postgresql.org>; Mon, 31 Mar 2003 07:05:34 -0500 (EST)
Received: by news.hub.org (Postfix, from userid 8)
	id 551BE43A89E; Mon, 31 Mar 2003 07:05:34 -0500 (EST)
From: cio198@plasa.com (cio198)
X-Newsgroups: comp.databases.postgresql.sql
Subject: create view with numeric data
Date: 31 Mar 2003 02:47:04 -0800
Organization: http://groups.google.com/
Lines: 54
Message-ID: <8d6dd4ad.0303310247.2dded41c@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1
X-Complaints-To: groups-abuse@google.com
To: pgsql-sql@postgresql.org
Content-Transfer-Encoding: quoted-printable
X-Spam-Status: No, hits=-4.5 required=5.0
	tests=BAYES_10,FROM_ENDS_IN_NUMS,UPPERCASE_25_50 version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200303/660
X-Sequence-Number: 12698

Hello,=20

i've created this table

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")
);

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)

TIA


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;