Print
All Forums Database
sean.m.bradford 1 post Joined 11/15
30 Nov 2015
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!

Fred 1096 posts Joined 08/04
30 Nov 2015

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)...

You must sign in to leave a comment.