Query to compare the contents of a field using PL/SQL
I need to compare the contents of a field to make sure that the fields have the same value. For example here is the table stucture:
PONUM ITEMNUM POLINENUM CCNUMBER
1234 3214 1 012345678
1234 4325 2 987654321
I need to compare the contents of CCNUMBER to make sure they are the same. How would I best handle this task using PL/SQL?
PONUM ITEMNUM POLINENUM CCNUMBER
1234 3214 1 012345678
1234 4325 2 987654321
I need to compare the contents of CCNUMBER to make sure they are the same. How would I best handle this task using PL/SQL?
ASKER
That is correct.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
what about this code to identify them:
select PONUM , max(CCNUMBER), min(CCNUMBER)
from yourtable
GROUP BY PONUM
HAVING max(CCNUMBER) <> min(CCNUMBER)
I wouldn't use pl/sql at all. I'd do it with sql.
SELECT *
FROM (SELECT ponum, ccnumber, COUNT(DISTINCT ccnumber) OVER(PARTITION BY ponum) cnt
FROM my_table)
WHERE cnt > 1
SELECT *
FROM (SELECT ponum, ccnumber, COUNT(DISTINCT ccnumber) OVER(PARTITION BY ponum) cnt
FROM my_table)
WHERE cnt > 1
select PONUM , max(CCNUMBER), min(CCNUMBER)
from yourtable
GROUP BY PONUM
HAVING max(CCNUMBER) <> min(CCNUMBER)
This doesn't work if ccnumber has any nulls in it
from yourtable
GROUP BY PONUM
HAVING max(CCNUMBER) <> min(CCNUMBER)
This doesn't work if ccnumber has any nulls in it
ASKER
What if CCNUMBER does have nulls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
note, my version of angeliii's updated query doesn't return the same results as his updated version.
If all ccnumbers for a ponum are null, I count that as the same ccnumber.
angeliii does not
both are reasonable answers to your question, only you can decide which is more appropriate for your application though.
We both count a mix of null and not null as different ccnumber's.
If all ccnumbers for a ponum are null, I count that as the same ccnumber.
angeliii does not
both are reasonable answers to your question, only you can decide which is more appropriate for your application though.
We both count a mix of null and not null as different ccnumber's.
can you explain a bit more what you mean with "they need to be the same"?
I guess, for the same PONUM you must have the same CCNumber?