Re: Groupped count(*) results from a view

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

    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...
  Look at my example: I can have two different vendors for the same item. The query is supposed to find
  all items (and the count of vendors) only where there is more than one occurence i.e.
  if an item has only one vendor, I don't want to see it.

  I have exactly the same dataset in Informix and results are correct.   Is my syntax incorrect for Oracle?

  Yes your syntax is correct!!

  But I'm a little confused about of what and how your doing... I mean you said your query work great with the base table and not with the view, so it's obvious the syntax is ok. Also if you want to check a query, for syntax and logic, you simply need to log in SQL*Plus and give it a try, Oracle will give you pretty good feed back about the syntax and you can check the logic with the results...

  I work a lot with Oracle 8i on solaris and I never heard a of kind a bug related to your problem, sorry but I truly belive that there ain't no duplicate "item num" in your view...

  Good luck.

  Michael

      "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

--