Re: Groupped count(*) results from a view

From: Nic <nicpayre[junk]_at_sympatico.ca>
Date: Fri, 8 Aug 2003 21:10:22 -0700
Message-ID: <aSXYa.4993$ a4.1015184_at_news20.bellglobal.com>

It because you use the UNIQUE keyword in the SELECT part of the view definition. Unique ensure that each rows return are unique...so there can't be any HAVING COUNT(*) > 1 for any grouping on the view.

You could figure it easy by yourself, you just have to query the base table, look at the result then query the view and try to find duplicate item num...

  "Michael Krzepkowski" <NOmichaelkSPAM_at_sqlcanada.com> wrote in message news:3F343F38.7080301_at_sqlcanada.com...   Nic wrote:

"Michael Krzepkowski" <NOmichaelkSPAM_at_sqlcanada.com> wrote in message news:3F33BF12.8080809_at_sqlcanada.com...   All,

I have a view that returns the following values:

Item                     Vendor
70807                   1234
70807                    5678

If I am looking for items that have more than one vendor: select item num,count(*)
from myview
group by item num
having count(*) > 1;

returns: no rows found.

If I use a table instead of the view everything works as expected. Is there a way around this issue?
Oracle version 8.1.7 on Sun.     

What's the definition of the view "myview"?

  TIA Michael     

  Here it is:

  select unique item vend item.item num,

                vend item.vendor num 

  from item vend item, vend item
  where item vend item.vend item id =

        vend item.vend item id

  Michael

--

Received on Sat Aug 09 2003 - 06:10:22 CEST