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
Jamil MuammarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
awking00Information Technology SpecialistCommented:
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
awking00Information Technology SpecialistCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.