unsubscribe
--- Tomasz Myrta <jasiek@klaster.net> wrote:
> Date: Mon, 31 Mar 2003 19:58:31 +0200
> From: Tomasz Myrta <jasiek@klaster.net>
> To: cio198 <cio198@plasa.com>
> CC: pgsql-sql@postgresql.org
> Subject: Re: [SQL] create view with numeric data
>
> 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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
=====
