Re: inconsistant sql behavior

This is not what Ed wants.
The GROUP BY clause is wrong.
It should be as following:

select vdl.EnteredDate as ListDate, count(*) as ShowingCount   from myView vdl, dev.ListingShowing ls   where ls.ListingID=vdl.ListingID
  group by vdl.ListingID, vdl.EnteredDate

Notice that the ListingID must appear in the group by clause, otherwise it will make an erroneous grouping.

Karen Abgarian <karen.abgarian_at_fmr.com> wrote in message news:37BDA7D6.853C6571_at_fmr.com...
> Hi Ed
>
> this is not the way it's going to work. This type of queries is
> resolved with joins in Oracle,
> like this:
>
> select vdl.EnteredDate, count(*)
> from myView vdl, dev.ListingShowing ls
> where ls.ListingID = vdl.ListingID
> group by vdl.EnteredDate;
>
> This is going to work if you dont have rows with equal entered dates.
> Add outer join
> operation if needed.
>
> Regards,
> Karen Abgarian.
>
> edmundo70_at_my-deja.com wrote:
>
> > Hello folks,
> >
> > I have a query where I want to do a subselect for a count.
> >
> > select
> > vdl.EnteredDate as ListDate,
> > (select count(*) as ShowingCount from
> > dev.ListingShowing ls
> > where
> > ls.ListingID = vdl.ListingID) as ShowingCount
> >
> > from
> > myView vdl
> >
> > __
> >
> > This works in Pl/sql but when I try to make it as a cursor, I get
> > something like "not expecting Select"...
> >
> > Here is another way that I thought should work but doesn't
> > select
> >
> > cnt.ShowingCount
> >
> > from
> > myView vdl,
> > (select count(*) as ShowingCount from
> > dev.ListingShowing ls
> > where
> > ls.ListingID = vdl.ListingID) cnt
> >
> > ___
> >
> > I guess vdl is not defined in the correlated subquery.
> >
> > Thoughts?
> >
> > Thx,
> > -Ed
> >
> > Sent via Deja.com http://www.deja.com/
> > Share what you know. Learn what you don't.
>
>
>
Received on Sun Aug 22 1999 - 05:00:45 CDT