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
5  5981 
"Michael Krzepkowski" <NO************ @sqlcanada.com> wrote in message
news:3F******** ******@sqlcanad a.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
Nic wrote: "Michael Krzepkowski" <NO************ @sqlcanada.com> wrote in message
news:3F******* *******@sqlcana da.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.vendo r_num
from item_vend_item, vend_item
where item_vend_item. vend_item_id =
vend_item.vend_ item_id
Michael
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******** ******@sqlcanad a.com...
Nic wrote:
"Michael Krzepkowski" <NO************ @sqlcanada.com> wrote in message
news:3F******** ******@sqlcanad a.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.vendo r_num
from item_vend_item, vend_item
where item_vend_item. vend_item_id =
vend_item.vend_ item_id
Michael
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***** *******@sqlcana da.com>> wrote in message
news:3F******** ******@sqlcanad a.com...
Nic wrote:
"Michael Krzepkowski" <NO************ @sqlcanada.com> wrote in message
news:3F****** ********@sqlcan ada.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.vendo r_num
from item_vend_item, vend_item
where item_vend_item. vend_item_id =
vend_item.vend_ item_id
Michael
"Michael Krzepkowski" <NO************ @sqlcanada.com> wrote in message news:3F******** ******@sqlcanad a.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******** ******@sqlcanad a.com...
Nic wrote:
"Michael Krzepkowski" <NO************ @sqlcanada.com> wrote in message
news:3F******** ******@sqlcanad a.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.vendo r_num
from item_vend_item, vend_item
where item_vend_item. vend_item_id =
vend_item.vend_ item_id
Michael
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Andy |
last post by:
I need to be able to count the number people who are registered in a
certain IT course. i need to display the results on a form or report.
This is for my A level courswork please help!
thanks
|
by: Zak McGregor |
last post by:
Hi all
is it possible to get a count(1) statemment, for example here:
select count(1) from results where fixture=4916 and winner=away group by winner;
to return a 0 value instead of absolutely nothing if no rows match fixture=4916
and winner=away? I get absolutely no results at all.
|
by: Michel de Becdeličvre |
last post by:
I need your help here :
I need to compute the product of a column, with conditions, over a
table, grouped by a key (it is a probability table).
In fact I need the exact equivalent of :
select key1, key2, sum( val )
from "sometable"
where "some parameterized condition"
|
by: Chris |
last post by:
Can't seem to figure out how to do this and have been reading for some
time now......
I want to select a row count from a table name in SYSTABLES.
This statement does not return what I needed, but can help explain what
I'm looking for. I want the results to be the Row Count from a Table
Name out of the Sub-Select.
Select Count(*) from
|
by: jonathan184 |
last post by:
Hi I am having a problme where the results of the sql count is not matching the results of the perl script sql count.
The script was working fine up till Wed last week and after that the results are different on sql and on perl and the date and times are the same, I even copy and pasted the sql query from the perl script into the sql window and got different results.
Here is part of the script where i am trying to get the count...
| | |
by: ret4rt |
last post by:
Hello.
I have a database with movies similar with imdb and i want to find out which directors have directed both thriller and drama movies.
The output i want is like this "DIR_NAME,amount of thriller movies,amout of drama movies"
First i did a check to see the expected number.Checkin only directors that have made thriller movies with
select DIR_NAME, count(GENRE)
from MGENRES, DIRECTORS, DIRECTED_BY
where GENRE = 'Thriller' and...
|
by: C10B |
last post by:
hi,
I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.
eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
|
by: Michael Krzepkowski |
last post by:
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(*)
|
by: M. Mehta |
last post by:
It seems that you can not create a materialized view if you are using
outer joins...can someone please verify this?
Thanks
M. Mehta
Please follow my example below:
created 2 tables:
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
