X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 02FD9475D0F
	for <pgsql-sql@postgresql.org>; Tue,  1 Apr 2003 04:53:46 -0500 (EST)
Received: from news.hub.org (news.hub.org [64.49.215.80])
	by postgresql.org (Postfix) with ESMTP id 511E8475AF8
	for <pgsql-sql@postgresql.org>; Tue,  1 Apr 2003 04:53:45 -0500 (EST)
Received: by news.hub.org (Postfix, from userid 8)
	id 19D7843A89E; Tue,  1 Apr 2003 04:53:45 -0500 (EST)
From: Thomas Kellerer <spam_eater@gmx.net>
X-Newsgroups: comp.databases.postgresql.sql
Subject: Re: create view with numeric data
Date: Tue, 01 Apr 2003 11:53:45 +0200
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 66
Message-ID: <b6bnj8$fcr$1@news.hub.org>
References: <8d6dd4ad.0303310247.2dded41c@posting.google.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: usenet@news.hub.org
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; de-AT;
	rv:1.3) Gecko/20030312
X-Accept-Language: de, de-at, en-us, en
In-Reply-To: <8d6dd4ad.0303310247.2dded41c@posting.google.com>
To: pgsql-sql@postgresql.org
X-Spam-Status: No, hits=-30.9 required=5.0
	tests=BAYES_10,IN_REP_TO,QUOTED_EMAIL_TEXT,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: 200304/1
X-Sequence-Number: 12702

Haven't tried it, but if you casted the result of the sum to 
numeric(9,2)? Btw: does the ELSE '0' really work? It seems a bit strange 
to put a string into a SUM...

cast ( sum(CASE WHEN month='01' THEN budgetvalue ELSE 0 END) as 
numeric(9,2)) AS january

Thomas


cio198 schrieb:
> Hello, 
> 
> 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='01' THEN budgetvalue ELSE '0' END) AS
> january,
>         sum(CASE WHEN month='02' THEN budgetvalue ELSE '0' END) AS
> february,
>         sum(CASE WHEN month='03' THEN budgetvalue ELSE '0' END) AS
> march,
>         sum(CASE WHEN month='04' THEN budgetvalue ELSE '0' END) AS
> april,
>         sum(CASE WHEN month='05' THEN budgetvalue ELSE '0' END) AS
> may,
>         sum(CASE WHEN month='06' THEN budgetvalue ELSE '0' END) AS
> june,
>         sum(CASE WHEN month='07' THEN budgetvalue ELSE '0' END) AS
> july,
>         sum(CASE WHEN month='08' THEN budgetvalue ELSE '0' END) AS
> august,
>         sum(CASE WHEN month='09' THEN budgetvalue ELSE '0' END) AS
> september,
>         sum(CASE WHEN month='10' THEN budgetvalue ELSE '0' END) AS
> october,
>         sum(CASE WHEN month='11' THEN budgetvalue ELSE '0' END) AS
> november,
>         sum(CASE WHEN month='12' THEN budgetvalue ELSE '0' END) AS
> december,
>         sum(budgetvalue) as totalvalue
>         FROM budget b inner join account a on b.accountno=a.accountno
>         GROUP BY year, b.accountno, a.name
>         ORDER BY b.accountno;