Use RESET WHEN change occurs in text field

Database

Use RESET WHEN change occurs in text field

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
FROM X_V.MY_VIEW

I keep getting a Syntax error looking for a character between MNT_MRGN_CALL_STS_RSN_TX and OVER.

Thanks!

1 REPLY
Teradata Employee

Re: Use RESET WHEN change occurs in text field

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 BY ENTP_PRTY_ID ORDER BY SRC_SYS_PROC_DT DESC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)...