pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcreate view with numeric data
4+ messages / 4 participants
[nested] [flat]
* create view with numeric data
@ 2003-03-31 10:47 cio198 <cio198@plasa.com>
0 siblings, 3 replies; 4+ messages in thread
From: cio198@plasa.com @ 2003-03-31 10:47 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: create view with numeric data
@ 2003-03-31 17:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: cio198 <cio198@plasa.com>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-03-31 17:52 UTC (permalink / raw)
To: cio198@plasa.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: create view with numeric data
@ 2003-03-31 17:58 Tomasz Myrta <jasiek@klaster.net>
parent: cio198 <cio198@plasa.com>
2 siblings, 0 replies; 4+ messages in thread
From: Tomasz Myrta @ 2003-03-31 17:58 UTC (permalink / raw)
To: cio198 <cio198@plasa.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: create view with numeric data
@ 2003-04-01 09:53 Thomas Kellerer <spam_eater@gmx.net>
parent: cio198 <cio198@plasa.com>
2 siblings, 0 replies; 4+ messages in thread
From: Thomas Kellerer @ 2003-04-01 09:53 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-04-01 09:53 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-31 10:47 create view with numeric data cio198 <cio198@plasa.com>
2003-03-31 17:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-31 17:58 ` Tomasz Myrta <jasiek@klaster.net>
2003-04-01 09:53 ` Thomas Kellerer <spam_eater@gmx.net>
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