Re: inconsistant sql behavior

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 Fri Aug 20 1999 - 14:09:11 CDT