• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

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
0
Jamil Muammar
Asked:
Jamil Muammar
  • 2
  • 2
1 Solution
 
Russ SuterCommented:
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?
0
 
Russ SuterCommented:
To get a row count looking for duplicates try this:
SELECT
  YEAR_MONTH,
  Customer,
  Product,
  COUNT(*)
FROM
  myTable
GROUP BY
  YEAR_MONTH,
  Customer,
  Product
HAVING
  COUNT(*) > 1;

Open in new window

0
 
johnsoneSenior Oracle DBACommented:
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.
0
 
awking00Commented:
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;
0
 
awking00Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now