I'm trying to reset a partition based upon a change of status in a text field. Is this possible?
SELECT SRC_SYS_PROC_DT, ENTP_PRTY_ID, SRC_MRGN_CALL_NB, LAST_MRGN_CALL_ISSU_DT, OLDST_UNRSLV_MRGN_CALL_DAY_CN, MNT_MRGN_CALL_STS_RSN_TX, MNT_MRGN_CALL_RSLV_DT, END_DT,
ROW_NUMBER () OVER (PARTITION BY ENTP_PRTY_ID ORDER BY SRC_SYS_PROC_DT DESC
RESET WHEN MNT_MRGN_CALL_STS_RSN_TX <> MNT_MRGN_CALL_STS_RSN_TX OVER (PARTITION BY ENTP_PRTY_ID ORDER BY SRC_SYS_PROC_DT ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)) as MRGN_RANK
I keep getting a Syntax error looking for a character between MNT_MRGN_CALL_STS_RSN_TX and OVER.
You must have an aggregate function before the window specification, e.g. MAX, even though you are limiting to a single row. Also, don't you want the same date ordering (DESC)?
...RESET WHEN MNT_MRGN_CALL_STS_RSN_TX <> MAX(MNT_MRGN_CALL_STS_RSN_TX) OVER (PARTITION