Thread: create view with numeric data
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;
cio198@plasa.com (cio198) writes:
> The problem is the numeric data in the view isn't limited to
> numeric(9,2) instead it become numeric(65535, 65531).
It should look like plain, unrestricted "numeric". Whatever is showing
you those bogus precision/scale fields is wrong. What client software
are you using anyway?
> Is there any way i can restrict it to numeric (9,2)
If you really want (9,2) and not just plain numeric, you can cast the
results of the sum operations to numeric(9,2).
regards, tom lane
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
Haven't tried it, but if you casted the result of the sum to
numeric(9,2)? Btw: does the ELSE '0' really work? It seems a bit strange
to put a string into a SUM...
cast ( sum(CASE WHEN month='01' THEN budgetvalue ELSE 0 END) as
numeric(9,2)) AS january
Thomas
cio198 schrieb:
> 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;