Oracle database mismatch

[sachin483]

I have database in which there multiple location for single customer, I want find the mismatch location and display the both the location for particular month in oracle database eg:- 2nd line is mismatch

customer location
AR1111 TN183
AR1111 MP183
AR1111 TN183
AR1111 TN183

[HansV]

How exactly do you define a mismatch?

What if you had

customer location
AR1111 TN183
AR1111 MP183
AR1111 MP183
AR1111 TN183

[sachin483]

In every case only 2 unique lines will get reflected ie:- particular customer having 2 location

customer location
AR1111 TN183
AR1111 MP183

[HansV]

You could use a query with subqueries:

SELECT DISTINCT customer, location
FROM tablename
WHERE customer IN (SELECT customer FROM (SELECT DISTINCT customer, location FROM tablename) AS Q GROUP BY customer HAVING Count(location)>1)

where tablename is the table with the customer and location fields.

[sachin483]

while executing the below sql

SELECT DISTINCT customer, location
FROM tablename
WHERE prd_code ='202203' and customer IN (SELECT customer FROM (SELECT DISTINCT customer, location FROM tablename) AS Q GROUP BY customer HAVING Count(location)>1);

error -ORA-00907: missing right parenthesis

[HansV]

I created and tested the query in Microsoft Access. It works there.
Unfortunately, I don't know anything about Oracle, so I have no idea why it doesn't work there. Sorry!

[sachin483]

After removing -- (AS Q) the query is working in oracle but both the lines not reflecting

customer location
AR1111 TN183 -- (not reflecting)
AR1111 MP183 -- (This line reflecting)

[HansV]

I'm afraid I cannot explain that.
You may have better luck asking your question on an Oracle forum.