unsubscribe

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


=====