Oracle database mismatch

sachin483
2StarLounger
Posts: 101
Joined: 03 Feb 2018, 04:20

Oracle database mismatch

Post by 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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Oracle database mismatch

Post by HansV »

How exactly do you define a mismatch?

What if you had

customer location
AR1111 TN183
AR1111 MP183
AR1111 MP183
AR1111 TN183
Best wishes,
Hans

sachin483
2StarLounger
Posts: 101
Joined: 03 Feb 2018, 04:20

Re: Oracle database mismatch

Post by sachin483 »

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

customer location
AR1111 TN183
AR1111 MP183

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Oracle database mismatch

Post by 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.
Best wishes,
Hans

sachin483
2StarLounger
Posts: 101
Joined: 03 Feb 2018, 04:20

Re: Oracle database mismatch

Post by 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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Oracle database mismatch

Post by 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!
Best wishes,
Hans

sachin483
2StarLounger
Posts: 101
Joined: 03 Feb 2018, 04:20

Re: Oracle database mismatch

Post by 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)

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Oracle database mismatch

Post by HansV »

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