Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 5F5E7475D00
	for <pgsql-sql@postgresql.org>; Sun, 20 Oct 2002 22:51:04 -0400 (EDT)
Received: from web80311.mail.yahoo.com (web80311.mail.yahoo.com
 [66.218.79.27])
	by postgresql.org (Postfix) with SMTP id E5F4B474E42
	for <pgsql-sql@postgresql.org>; Sun, 20 Oct 2002 22:51:02 -0400 (EDT)
Message-ID: <20021021025107.28093.qmail@web80311.mail.yahoo.com>
Received: from [203.87.150.116] by web80311.mail.yahoo.com via HTTP;
	Sun, 20 Oct 2002 19:51:07 PDT
Date: Sun, 20 Oct 2002 19:51:07 -0700 (PDT)
From: Ludwig Lim <lud_nowhere_man@yahoo.com>
Subject: Re: Restricting a VIEW.
To: Terry Yapt <yapt@technovell.com>
Cc: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
In-Reply-To: <3DB2032A.496345A1@technovell.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200210/254
X-Sequence-Number: 9971


--- 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