LAG and ORDER BY
Posted by: SARAH GEORGE
Date: November 15, 2023 02:29PM

Hi,

Can anyone tell me why this script cannot be represented in the grid or diagram panes?


SELECT *
FROM ( SELECT
v_rentregister_bi.[Rentregister Id] AS RentRegisterID,
v_rentregister_bi.[Tenant Ref] AS TenantRef,
v_rentregister_bi.Narrative,
v_rentregister_bi.[Start Date] AS StartDate,
v_rentregister_bi.[End Date] AS EndDate,
v_rentregister_bi.[Current],
v_rentregister_bi.Amount,
LAG(v_rentregister_bi.Amount) OVER (ORDER BY v_rentregister_bi.[Tenant Ref],v_rentregister_bi.[Start Date]) AS PreviousAmount,
LAG(v_rentregister_bi.[Tenant Ref]) OVER (ORDER BY v_rentregister_bi.[Tenant Ref],v_rentregister_bi.[Start Date]) AS PreviousTenantRef

FROM OPENQUERY(propcodr, 'select `Rentregister Id`, `Tenant Ref`, Narrative, `Start Date`, `End Date`, Amount, `Current` from v_rentregister_bi
WHERE `Tenant Ref` IN ("TestA1") OR `Tenant Ref` IN ("P236839A1") OR `Tenant Ref` IN ("TestB1") OR `Tenant Ref` IN ("TestC1")') AS v_rentregister_bi )
AS Subquery
WHERE Amount <> PreviousAmount AND StartDate > '2021-10-31' AND TenantRef = PreviousTenantRef

Options: ReplyQuote


Subject
Written By
Posted
LAG and ORDER BY
November 15, 2023 02:29PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.