Oracle Analytics and AI

User_GCQ4K

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

Gianni Ceresa

Where did you add that line?

Because it comes with a ' , ' at the end, but you should add one at the beginning if you are adding it right before the FROM.

You just have a syntax error in your query, look careful at your list of columns and fix the wrong ' , '.