Groupped count(*) results from a view

[Michael Krzepkowski]

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.

TIA

Michael

[Nic]

"Michael Krzepkowski" <NO************@sqlcanada.com> wrote in message
news:3F**************@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

[Michael Krzepkowski]

Nic wrote:

"Michael Krzepkowski" <NO************@sqlcanada.com> wrote in message
news:3F**************@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

[Nic]

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" <NO************@sqlcanada.com> wrote in message news:3F**************@sqlcanada.com...
Nic wrote:

"Michael Krzepkowski" <NO************@sqlcanada.com> wrote in message
news:3F**************@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

[Michael Krzepkowski]

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?

Michael


"Michael Krzepkowski" <NO************@sqlcanada.com
<mailto:NO************@sqlcanada.com>> wrote in message
news:3F**************@sqlcanada.com...
Nic wrote:

"Michael Krzepkowski" <NO************@sqlcanada.com> wrote in message
news:3F**************@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

[Nic]

"Michael Krzepkowski" <NO************@sqlcanada.com> wrote in message news:3F**************@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" <NO************@sqlcanada.com> wrote in message news:3F**************@sqlcanada.com...
Nic wrote:

"Michael Krzepkowski" <NO************@sqlcanada.com> wrote in message
news:3F**************@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