pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRestricting a VIEW.
4+ messages / 3 participants
[nested] [flat]
* Restricting a VIEW.
@ 2002-10-20 01:13 Terry Yapt <yapt@technovell.com>
0 siblings, 1 reply; 4+ messages in thread
From: Terry Yapt @ 2002-10-20 01:13 UTC (permalink / raw)
To: pgsql-sql
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.
SELECT * FROM vw_ty_test WHERE datein BETWEEN '2002/10/01' AND '2002/10/9';
--==============================
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Restricting a VIEW.
@ 2002-10-21 02:51 Ludwig Lim <lud_nowhere_man@yahoo.com>
parent: Terry Yapt <yapt@technovell.com>
0 siblings, 2 replies; 4+ messages in thread
From: Ludwig Lim @ 2002-10-21 02:51 UTC (permalink / raw)
To: Terry Yapt <yapt@technovell.com>; +Cc: pgsql-sql
--- 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Restricting a VIEW.
@ 2002-10-21 08:25 Terry Yapt <yapt@NOtechSPnovellAM.com>
parent: Ludwig Lim <lud_nowhere_man@yahoo.com>
1 sibling, 0 replies; 4+ messages in thread
From: Terry Yapt @ 2002-10-21 08:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Restricting a VIEW.
@ 2002-10-21 08:26 Terry Yapt <yapt@technovell.com>
parent: Ludwig Lim <lud_nowhere_man@yahoo.com>
1 sibling, 0 replies; 4+ messages in thread
From: Terry Yapt @ 2002-10-21 08:26 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-10-21 08:26 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-20 01:13 Restricting a VIEW. Terry Yapt <yapt@technovell.com>
2002-10-21 02:51 ` Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-10-21 08:25 ` Terry Yapt <yapt@NOtechSPnovellAM.com>
2002-10-21 08:26 ` Terry Yapt <yapt@technovell.com>
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