From: Ludwig Lim <lud_nowhere_man@yahoo.com>
To: Terry Yapt <yapt@technovell.com>
Cc: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: Restricting a VIEW.
Date: Sun, 20 Oct 2002 19:51:07 -0700 (PDT)
Message-ID: <20021021025107.28093.qmail@web80311.mail.yahoo.com> (raw)
In-Reply-To: <3DB2032A.496345A1@technovell.com>
--- Terry Yapt <yapt@technovell.com> wrote:
> Hello all,
>
> I have a doubt. In the next example, I have a table
> with two columns:
> - DATE
> - MONEY
>
> And a VIEW which SUM's the money GROUPing by
> 'month/year' (I cut off the day)...
>
> Ok.. I would like to be able to SELECT * FROM VIEW..
> but restricting by complete dates (dd/mm/yyyy)...
> (Last select in the example)
>
> I think it isn't possible, but I would like to know
> your opinion... Or if there is any workaround...
>
> Best regards..
>
> --==============================
> DROP TABLE ty_test;
> CREATE TABLE ty_test
> (datein date NOT NULL,
> money numeric(6,2) NOT NULL,
> PRIMARY KEY (datein)
> ) WITHOUT OIDS;
>
> INSERT INTO ty_test VALUES ('2002/10/01',10);
> INSERT INTO ty_test VALUES ('2002/10/15',20);
> INSERT INTO ty_test VALUES ('2002/11/15',30);
>
> DROP VIEW vw_ty_test;
> CREATE VIEW vw_ty_test AS
> SELECT
> TO_CHAR(datein,'MM/YYYY') AS datein2,
> SUM(money)
> FROM
> ty_test
> GROUP BY
> datein2;
>
> SELECT * FROM ty_test; -- All rows from table.
> SELECT * FROM vw_ty_test; -- All rows from view.
>
I don't the work around using a view but you can do
it without using a view:
SELECT
to_number(to_char(datein,'mm'),'99') as month,
to_number(to_char(datein,'yyyy'),'9999') as year,
SUM(money)
FROM ty_test
WHERE datein BETWEEN
to_date('01/10/2002','mm/dd/yyyy') AND
to_date('09/10/2002','mm/ddy/yyyy')
ORDER BY
to_number(to_char(datein,'mm'),'99')
to_number(to_char(datein,'yyyy'),'9999');
ludwig.
__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lud_nowhere_man@yahoo.com, yapt@technovell.com
Subject: Re: Restricting a VIEW.
In-Reply-To: <20021021025107.28093.qmail@web80311.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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