Jamil Muammar
asked on
Pl SQL Oracle
Dear Experts,
I have tables contain 3 columns as a key, cannot have 2 rows with the same keys value.
The coulm are: YEAR_MONTH, Customer, product.
How do I by creating a view display all the rows that having more than row in the same keys
For Example, for the following table
YEAR_MONTH - Customer - Product
201803 cust1 prod1
201911 cust2 prod2
201803 Cust1 Prod1
the out view will be
201803 Cust1 Prod1 2(times)
Thanks
I have tables contain 3 columns as a key, cannot have 2 rows with the same keys value.
The coulm are: YEAR_MONTH, Customer, product.
How do I by creating a view display all the rows that having more than row in the same keys
For Example, for the following table
YEAR_MONTH - Customer - Product
201803 cust1 prod1
201911 cust2 prod2
201803 Cust1 Prod1
the out view will be
201803 Cust1 Prod1 2(times)
Thanks
If your table already has the key in place then it would be impossible for the data to be in the state you're testing for. Are you trying to add the key to a table with existing data and getting a key violation?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
PL/SQL is Oracle's procedural language (the PL in PL/SQL). It is used to create procedures, functions, triggers and things like that. This question does not involve PL/SQL, it is a strictly SQL question. By putting PL/SQL in the title some experts will skip the question as they are not as knowledgable in that subject but could have answered your question.
Also, try to add the Oracle Database topic. Most Oracle experts monitor that topic. For some reason, this question didn't make it in there.
While using the count query that was already posted certainly works, here is a different method.
Another method is to actually try to create the key and let it fail. The first thing you need for that is an exceptions table. You can easily create that by running @?/rdbms/admin/utlexcpt Typically you would run that from the server, connected as the user that owns the table. To speed things up, create an index on the combination of the 3 columns for example:
create index mytab_indx1 on mytab(year_month, customer, product);
Then create the constraint. The key is the exceptions clause
alter table mytab add unique (year_month, customer, product) exceptions into exceptions;
That will put all the rows that violate the unique constraint into the EXCEPTIONS table that was created. The nice thing about the EXCEPTIONS table is that you can easily reference the list over and over without having to scan the table and doing counts again and again.
I wouldn't drop the index. Keep it around and when you actually do create the constraint, it won't have to build a new index, it can use the existing one.
Also, try to add the Oracle Database topic. Most Oracle experts monitor that topic. For some reason, this question didn't make it in there.
While using the count query that was already posted certainly works, here is a different method.
Another method is to actually try to create the key and let it fail. The first thing you need for that is an exceptions table. You can easily create that by running @?/rdbms/admin/utlexcpt Typically you would run that from the server, connected as the user that owns the table. To speed things up, create an index on the combination of the 3 columns for example:
create index mytab_indx1 on mytab(year_month, customer, product);
Then create the constraint. The key is the exceptions clause
alter table mytab add unique (year_month, customer, product) exceptions into exceptions;
That will put all the rows that violate the unique constraint into the EXCEPTIONS table that was created. The nice thing about the EXCEPTIONS table is that you can easily reference the list over and over without having to scan the table and doing counts again and again.
I wouldn't drop the index. Keep it around and when you actually do create the constraint, it won't have to build a new index, it can use the existing one.
While the three columns are intended to be unique, the difference in case allows such data as 201803, cust1, prod1 and 201803, Cust1, Prod1. If those are considered to not be unique, you can find them using the following:
select year_month, upper(customer) as customer, upper(product), count(*) as number_of_times
from yourtable
group by year_month, upper(customer), upper(product)
having count(*) > 1;
select year_month, upper(customer) as customer, upper(product), count(*) as number_of_times
from yourtable
group by year_month, upper(customer), upper(product)
having count(*) > 1;
For Example, for the following table
YEAR_MONTH - Customer - Product
201803 cust1 prod1
201911 cust2 prod2
201803 Cust1 Prod1
the out view will be
201803 Cust1 Prod1 2(times)
You do know that the accepted answer would not produce the out view for that example data since lowercase "cust1" is not equal to capitalized "Cust1" without providing for case insensitivity.
YEAR_MONTH - Customer - Product
201803 cust1 prod1
201911 cust2 prod2
201803 Cust1 Prod1
the out view will be
201803 Cust1 Prod1 2(times)
You do know that the accepted answer would not produce the out view for that example data since lowercase "cust1" is not equal to capitalized "Cust1" without providing for case insensitivity.