kalees
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_SERVI CES_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
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_SERVI
INNER JOIN STD_BI.RL2_CONTRACTS_VW
ON (CS.CONTRACT_NUMBER =
RL2_CONTRACTS_VW.CONTRACT_
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
ASKER
Hi Many thanks for the reply,
Sorry for the ambiguity RL2_CONTRACTS_SERVICES_VW has the service_ID
Sorry for the ambiguity RL2_CONTRACTS_SERVICES_VW has the service_ID
last comment worked?
ASKER
No is gives the following error
Error at line 8
ORA-00904: "SERVICE_ID": invalid identifier
Error at line 8
ORA-00904: "SERVICE_ID": invalid identifier
Can you please provide columns for these tables -
STD_BI.RL2_CONTRACTS_SERVI CES_VW
STD_BI.RL2_CONTRACTS_VW
Also which table has duplicate values ?
STD_BI.RL2_CONTRACTS_SERVI
STD_BI.RL2_CONTRACTS_VW
Also which table has duplicate values ?
Please try this also-
Hope it helps!
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
Hope it helps!
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
Error at line 15
ORA-00904: "FIRST_REGISTERED_DATE": invalid identifier
Can you please provide columns from both table and views ?
ASKER
The last script is still returning 2 rows
Hi,
Please try this -
Hope it helps!
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
Hope it helps!
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
CONTRACT_NUMBER
SERVICE_ID
RL2_CONTRACTS_VW has
CONTRACT_NUMBER
FMS_ID
REGISTRATION_NUMBER
FIRST_REGISTERED_DATE
CONTRACT_STATUS
LATEST_REVISION
PRODUCT_TYPE
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Many many thanks again,
I will have to spend some time studying your script to learn from it,
Again, many thanks,
Keith.
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.
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!
Please try this -
Which table has these columns - CONTRACT_NUMBER SERVICE_ID ? RL2_CONTRACTS_VW ?
Open in new window
Hope it helps!