create view with numeric data

From: cio198(at)plasa(dot)com (cio198)
To: pgsql-sql(at)postgresql(dot)org
Subject: create view with numeric data
Date: 2003-03-31 10:47:04
Message-ID: 8d6dd4ad.0303310247.2dded41c@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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;

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jan Wieck 2003-03-31 15:49:13 Re: How to notice column changes in trigger
Previous Message Tomasz Myrta 2003-03-31 06:30:28 Re: How to configure two netcard for PostgreSQL?