Oracle Analytics Publisher

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Remove duplicate Row

Accepted answer
307
Views
7
Comments
Rank 1 - Community Starter

I received below result from below Query

SELECT DISTINCT LB.CHANGEBY,

LB.CHANGEDATE,

LB.REVISIONNUM,

LB.MEMO,

LB.STATUS,

LB.CONTRACTNUM,

R1.DESCRIPTION,

R1.REVCOMMENTS,

R1.CNRCMSNUM,

R1.RENEWALDATE,

CASE

WHEN ((REVTYPE IS NULL OR REVTYPE<>' ') AND R1.REVISIONNUM=0) THEN 'NEW CONTRACT'

WHEN REVCOMMENTS LIKE '%CLOSE%' OR REVCOMMENTS LIKE '%CLOSE%' OR REVCOMMENTS LIKE '%CLOSE%' THEN 'CLOSED'

WHEN REVCOMMENTS LIKE '%HOLD%' THEN 'HOLD'

WHEN REVCOMMENTS LIKE '%UPDATE LABOUR%' OR REVCOMMENTS LIKE '%UPDATE LABOUR%' THEN 'UPDATE LABOUR'

WHEN REVCOMMENTS LIKE '%UPDATE EQUIPR%' OR REVCOMMENTS LIKE '%UPDATE EQUIP%' THEN 'UPDATE EQUIP'

WHEN REVCOMMENTS LIKE '%RATE EXTENSION%' OR REVCOMMENTS LIKE '%RATE EXTENSION%' THEN 'RATE EXTENSION'

WHEN REVCOMMENTS LIKE '%REVISE CONTRACT HEADER%' OR REVCOMMENTS LIKE '%REVISE CONTRACT HEADER%' THEN 'REVISE CONTRACT HEADER'

WHEN REVCOMMENTS LIKE '%NEW LABOUR RATE%' OR REVCOMMENTS LIKE '%NEW LABOUR RATE%' OR REVCOMMENTS LIKE '%NEW LABOURS RATE%' THEN 'NEW LABOUR RATES'

WHEN REVCOMMENTS LIKE '%NEW EQUIPMENT RATE%' OR REVCOMMENTS LIKE '%NEW EQUIPMENT%' OR REVCOMMENTS LIKE '%NEW EQUIPMENTS RATE%'THEN 'NEW EQUIPMENT RATES'

ELSE REVTYPE

END AS REVTYPE

FROM CONTRACTSTATUS_V LB

LEFT JOIN

(SELECT DISTINCT *

FROM SM_CONTRACT_V) R1 ON(R1.CONTRACTNUM=LB.CONTRACTNUM

AND R1.REVISIONNUM=LB.REVISIONNUM)

LEFT JOIN

(SELECT DISTINCT CONTRACTID,REVTYPE

FROM CNRLABORVIEWEXT_V) R2 ON(R2.CONTRACTID=R1.CONTRACTID)

WHERE LB.STATUS IN('WAPPR') AND

LB.CHANGEDATE >=TO_DATE('2021-01-01','YYYY-MM-DD') AND LB.CONTRACTNUM='470364' AND

LB.CHANGEBY IN('DYLANWH','JENNIEX','KIMP', 'ROSEMARW','ROWENAW','RUPALP','SHAYNEBR','YVONNEF')  ORDER BY CONTRACTNUM, CNRCMSNUM, REVISIONNUM DESC


My desire result should be below -keep only one record when revisionnum and changedate is same (ignore changeby and different time and keep older record)

I did try to add

ROW_NUMBER() OVER(PARTITION BY TRUNC(LB.CHANGEDATE) ORDER BY LB.CHANGEDATE ASC) AS Rownum,

but it giving me error

" Message=ORA-00923: FROM keyword not found where expected"

Thanks lot

Welcome!

It looks like you're new here. Sign in or register to get started.

Best Answers

Answers

Welcome!

It looks like you're new here. Sign in or register to get started.