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;
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? |