Re: [SQL] create view with numeric data

[Tomasz Myrta]

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