Обсуждение: Restricting a VIEW.

Поиск
Список
Период
Сортировка

Restricting a VIEW.

От
Terry Yapt
Дата:
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
theexample)
 

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.

SELECT * FROM vw_ty_test WHERE datein BETWEEN '2002/10/01' AND '2002/10/9';
--==============================


Re: Restricting a VIEW.

От
Ludwig Lim
Дата:
--- 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


Re: Restricting a VIEW.

От
Terry Yapt
Дата:
Hi Ludwig,

I need that like a VIEW because if I convert that view to a select, I
must to do that SELECT in a lot of client programs.

I would to know if I can to do any kind of VIEW with parameters...
Rewriting the view in the example:

DROP VIEW vw_ty_test;
CREATE VIEW vw_ty_test AS   SELECT       TO_CHAR(datein,'MM/YYYY') AS datein2,       SUM(money)     FROM       ty_test
 WHERE            datein BETWEEN $Parameter1 AND $Parameter2     GROUP BY       datein2;
 

is it possible ??  How ?

Thanks a lot...

On Mon, 21 Oct 2002 02:51:10 +0000 (UTC), lud_nowhere_man@yahoo.com
(Ludwig Lim) wrote:

>
>--- 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
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly



Re: Restricting a VIEW.

От
Terry Yapt
Дата:
Hi Ludwig,

I need that like a VIEW because if I convert that view to a select, I
must to do that SELECT in a lot of client programs.

I would to know if I can to do any kind of VIEW with parameters...
Rewriting the view in the example:

DROP VIEW vw_ty_test;
CREATE VIEW vw_ty_test AS   SELECT       TO_CHAR(datein,'MM/YYYY') AS datein2,       SUM(money)     FROM       ty_test
 WHERE            datein BETWEEN $Parameter1 AND $Parameter2     GROUP BY       datein2;
 

is it possible ??  How ?

Thanks a lot...


On Mon, 21 Oct 2002 02:51:10 +0000 (UTC), lud_nowhere_man@yahoo.com
(Ludwig Lim) wrote:

>
>--- 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
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly