From: cio198@plasa.com
To: pgsql-sql@postgresql.org
Subject: create view with numeric data
Date: 31 Mar 2003 02:47:04 -0800
Message-ID: <8d6dd4ad.0303310247.2dded41c@posting.google.com> (raw)
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cio198@plasa.com
Subject: Re: create view with numeric data
In-Reply-To: <8d6dd4ad.0303310247.2dded41c@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox