Link to home
Start Free TrialLog in
Avatar of kalees
kaleesFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Problem with duplicate records in Oracle query

I have created the script below to determine if a vehicle is maintained or non-maintained for non-maintained vehicles the script seems to be working fine however for maintained vehicles it is always returning 2 rows one for maintained and one for non-maintained

Any help will be greatly appreciated.

Regards,

Keith


This is an extract of the data in the database

CONTRACT_NUMBER      SERVICE_ID
11819                              21
11819                              21
11819                              61
11819                              62
11819                              63
11819                              64
11819                              65



Script

SELECT
CS.CONTRACT_NUMBER,
FMS_ID,
REGISTRATION_NUMBER,
FIRST_REGISTERED_DATE,
CASE WHEN CS.SERVICE_ID IN (23,65) THEN 'Maintained' ELSE 'Non-Maintained' END "Maintained / Non-Maintained"
  FROM STD_BI.RL2_CONTRACTS_SERVICES_VW CS
       INNER JOIN STD_BI.RL2_CONTRACTS_VW
          ON (CS.CONTRACT_NUMBER =
                 RL2_CONTRACTS_VW.CONTRACT_NUMBER)
 WHERE (CONTRACT_STATUS = 'ACTIVE') AND (LATEST_REVISION = 'Y') AND (PRODUCT_TYPE = 'OL') AND REGISTRATION_NUMBER = 'ABC123'
 GROUP BY
CS.CONTRACT_NUMBER,
FMS_ID,
REGISTRATION_NUMBER,
FIRST_REGISTERED_DATE,
CASE WHEN CS.SERVICE_ID IN (23,65) THEN 'Maintained' ELSE 'Non-Maintained' END

This is the result that I am getting back, I am only expecting the first line.

CONTRACT_NUMBER      FMS_ID      REGISTRATION_NUMBER      FIRST_REGISTERED_DATE      Maintained / Non-Maintained
11819                             1750      ABC123                              12-Feb-13                              Maintained
11819                             1750      ABC123                              12-Feb-13                              Non-Maintained
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi Keith,
Please try this -

Which table has these columns - CONTRACT_NUMBER      SERVICE_ID  ?  RL2_CONTRACTS_VW ?

--
SELECT
CS.CONTRACT_NUMBER,
FMS_ID, 
REGISTRATION_NUMBER, 
FIRST_REGISTERED_DATE,
CASE WHEN CS.SERVICE_ID IN (23,65) THEN 'Maintained' ELSE 'Non-Maintained' END "Maintained / Non-Maintained"
  FROM STD_BI.RL2_CONTRACTS_SERVICES_VW CS
       INNER JOIN ( SELECT DISTINCT CONTRACT_NUMBER   ,   SERVICE_ID FROM STD_BI.RL2_CONTRACTS_VW ) RL2_CONTRACTS_VW
          ON (CS.CONTRACT_NUMBER = RL2_CONTRACTS_VW.CONTRACT_NUMBER)
 WHERE (CONTRACT_STATUS = 'ACTIVE') AND (LATEST_REVISION = 'Y') AND (PRODUCT_TYPE = 'OL') AND REGISTRATION_NUMBER = 'ABC123'
 GROUP BY
CS.CONTRACT_NUMBER,
FMS_ID, 
REGISTRATION_NUMBER, 
FIRST_REGISTERED_DATE,
CASE WHEN CS.SERVICE_ID IN (23,65) THEN 'Maintained' ELSE 'Non-Maintained' END
--

Open in new window


Hope it helps!
Avatar of kalees

ASKER

Hi Many thanks for the reply,

Sorry for the ambiguity RL2_CONTRACTS_SERVICES_VW has the service_ID
last comment worked?
Avatar of kalees

ASKER

No is gives the following error

Error at line 8
ORA-00904: "SERVICE_ID": invalid identifier
Can you please provide columns for these tables -  

STD_BI.RL2_CONTRACTS_SERVICES_VW

STD_BI.RL2_CONTRACTS_VW

Also which table has duplicate values ?
Please try this also-

SELECT
CS.CONTRACT_NUMBER,
FMS_ID, 
REGISTRATION_NUMBER, 
FIRST_REGISTERED_DATE,
CASE WHEN CS.SERVICE_ID IN (23,65) THEN 'Maintained' ELSE 'Non-Maintained' END "Maintained / Non-Maintained"
  FROM ( SELECT DISTINCT CONTRACT_NUMBER , SERVICE_ID FROM STD_BI.RL2_CONTRACTS_SERVICES_VW ) CS
       INNER JOIN STD_BI.RL2_CONTRACTS_VW
          ON (CS.CONTRACT_NUMBER =
                 RL2_CONTRACTS_VW.CONTRACT_NUMBER)
 WHERE (CONTRACT_STATUS = 'ACTIVE') AND (LATEST_REVISION = 'Y') AND (PRODUCT_TYPE = 'OL') AND REGISTRATION_NUMBER = 'ABC123'
 GROUP BY
CS.CONTRACT_NUMBER,
FMS_ID, 
REGISTRATION_NUMBER, 
FIRST_REGISTERED_DATE,
CASE WHEN CS.SERVICE_ID IN (23,65) THEN 'Maintained' ELSE 'Non-Maintained' END

Open in new window


Hope it helps!
Avatar of kalees

ASKER

I have just changed the View name in the Select Distinct and it has got further but now gives the following error

Error at line 15
ORA-00904: "FIRST_REGISTERED_DATE": invalid identifier
Can you please provide columns from both table and views ?
Avatar of kalees

ASKER

The last script is still returning 2 rows
Hi,
Please try this -

SELECT 
CONTRACT_NUMBER,
FMS_ID,
REGISTRATION_NUMBER,
FIRST_REGISTERED_DATE,
"Maintained / Non-Maintained"
FROM 
(
	SELECT 
	CONTRACT_NUMBER,
	FMS_ID,
	REGISTRATION_NUMBER,
	FIRST_REGISTERED_DATE,
	"Maintained / Non-Maintained",
	ROW_NUMBER() OVER(PARTITION BY CONTRACT_NUMBER,FMS_ID,REGISTRATION_NUMBER,FIRST_REGISTERED_DATE ORDER BY "Maintained / Non-Maintained" DESC) rnk
	FROM 
	(
		SELECT
		CS.CONTRACT_NUMBER,
		FMS_ID, 
		REGISTRATION_NUMBER, 
		FIRST_REGISTERED_DATE,
		CASE WHEN CS.SERVICE_ID IN (23,65) THEN 'Maintained' ELSE 'Non-Maintained' END "Maintained / Non-Maintained"

		  FROM STD_BI.RL2_CONTRACTS_SERVICES_VW CS
			   INNER JOIN STD_BI.RL2_CONTRACTS_VW
				  ON (CS.CONTRACT_NUMBER =
						 RL2_CONTRACTS_VW.CONTRACT_NUMBER)
		 WHERE (CONTRACT_STATUS = 'ACTIVE') AND (LATEST_REVISION = 'Y') AND (PRODUCT_TYPE = 'OL') AND REGISTRATION_NUMBER = 'ABC123'
		 GROUP BY
		CS.CONTRACT_NUMBER,
		FMS_ID, 
		REGISTRATION_NUMBER, 
		FIRST_REGISTERED_DATE,
		CASE WHEN CS.SERVICE_ID IN (23,65) THEN 'Maintained' ELSE 'Non-Maintained' END
	)k 
)p WHERE rnk = 1

Open in new window


Hope it helps!
Avatar of kalees

ASKER

RL2_CONTRACTS_SERVICES_VW has

CONTRACT_NUMBER
SERVICE_ID

RL2_CONTRACTS_VW has
CONTRACT_NUMBER
FMS_ID
REGISTRATION_NUMBER
FIRST_REGISTERED_DATE
CONTRACT_STATUS
LATEST_REVISION
PRODUCT_TYPE
Avatar of kalees

ASKER

Hi,

I have just run it with and without the registration number in the where clause and It has reduced the record count to one as it did when I used the MAX function for the CASE in my original script, however, it is returning all as non-maintained
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kalees

ASKER

Again Pawan, you have saved the day, option 2 is the answer to my prayers.

Many many thanks again,

I will have to spend some time studying your script to learn from it,

Again, many thanks,

Keith.
Avatar of kalees

ASKER

Again Pawan, you have saved the day, option 2 is the answer to my prayers.

Many many thanks again,

I will have to spend some time studying your script to learn from it,

Again, many thanks,

Keith.
Glad to help!