From: Tomasz Myrta <jasiek@klaster.net>
To: cio198 <cio198@plasa.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: create view with numeric data
Date: Mon, 31 Mar 2003 19:58:31 +0200
Message-ID: <3E8881C7.8030608@klaster.net> (raw)
In-Reply-To: <8d6dd4ad.0303310247.2dded41c@posting.google.com>
References: <8d6dd4ad.0303310247.2dded41c@posting.google.com>
Uz.ytkownik cio198 napisa?:
> 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;
Sure, change all of your sums to:
cast(sum(...) as numeric(9,2)) as ...
Regards,
Tomasz Myrta
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: jasiek@klaster.net, cio198@plasa.com
Subject: Re: create view with numeric data
In-Reply-To: <3E8881C7.8030608@klaster.net>
* 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