Informix-Creating View bring server down
hi;
I'm trying to create a view on a very large table.In view u can't create
temp table, so add a subquery.
SQL:
CREATE VIEW mbrcount (group_id,plan_id,sub,dep) AS
Select distinct a.group_id,a.plan_id,count(a.member_id) sub,
(Select count(distinct c.member_id)
From b,outer c
Where
a.group_id = b.group_id
and a.plan_id = b.plan_id
and b.mb_gr_pl_id = c.mb_gr_pl_id
and b.member_id <> c.member_id
and c.eff_date <= today
and (c.exp_date is null or c.exp_date > today)
group by b.group_id,b.plan_id )
From a
Where a.eff_gr_pl <= today
and (a.exp_gr_pl > today or a.exp_gr_pl is null)
Group by a.group_id,a.plan_id
when I try to run this query, it bring server down, but when I run this
without create view statement run perfect.
What I'm doing wrong??????????
thanks in advance
Rahim
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
In article <7889qr$d2h$1@nnrp1.dejanews.com>, rayr@usa.net writes
>hi;
>
>I'm trying to create a view on a very large table.In view u can't create
>temp table, so add a subquery.
>
>SQL:
>CREATE VIEW mbrcount (group_id,plan_id,sub,dep) AS
>Select distinct a.group_id,a.plan_id,count(a.member_id) sub,
>(Select count(distinct c.member_id)
> From b,outer c
> Where
> a.group_id = b.group_id
> and a.plan_id = b.plan_id
> and b.mb_gr_pl_id = c.mb_gr_pl_id
> and b.member_id <> c.member_id
> and c.eff_date <= today
> and (c.exp_date is null or c.exp_date > today)
>group by b.group_id,b.plan_id )
>From a
>Where a.eff_gr_pl <= today
> and (a.exp_gr_pl > today or a.exp_gr_pl is null)
>Group by a.group_id,a.plan_id>
>when I try to run this query, it bring server down, but when I run this
>without create view statement run perfect.
>
>What I'm doing wrong??????????
>
Sounds like a bug, talk to Informix Technical Support.
>thanks in advance
>
>Rahim
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
--
David Williams
Which version??????
>Subject: Informix-Creating View bring server down
>From: rayr@usa.net
>Date: 1/21/99 4:30 PM Central Standard Time
>Message-id: <7889qr$d2h$1@nnrp1.dejanews.com>
>
>hi;
>
>I'm trying to create a view on a very large table.In view u can't create
>temp table, so add a subquery.
>
>SQL:
>CREATE VIEW mbrcount (group_id,plan_id,sub,dep) AS
>Select distinct a.group_id,a.plan_id,count(a.member_id) sub,
>(Select count(distinct c.member_id)
> From b,outer c
> Where
> a.group_id = b.group_id
> and a.plan_id = b.plan_id
> and b.mb_gr_pl_id = c.mb_gr_pl_id
> and b.member_id <> c.member_id
> and c.eff_date <= today
> and (c.exp_date is null or c.exp_date > today)
>group by b.group_id,b.plan_id )
>From a
>Where a.eff_gr_pl <= today
> and (a.exp_gr_pl > today or a.exp_gr_pl is null)
>Group by a.group_id,a.plan_id>
>when I try to run this query, it bring server down, but when I run this
>without create view statement run perfect.
>
>What I'm doing wrong??????????
>
>thanks in advance
>
>Rahim
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
>
>
>
>
>
Madison Pruet