Database release : Teradata 14.10
When i try to use "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" in
FIRST_VALUE (Col_name) OVER (PARTITION BY ACCOUNT_ID ORDER BY ACCOUNT_
ID , VALID_FROM RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWI
NG) Col_name ,
LAST_VALUE (Col_name2) OVER (PARTITION BY ACCOUNT_ID
ORDER BY ACCOUNT_ID , VALID_FROM RANGE BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) Col_name2 ,
*** Failure 3706 Syntax error: Expected the word RESET or ')' after ORDER B
so it fails
is there's a work arround for that?
RANGE syntax is not implemented in Teradata, you need to user ROWS instead:
OVER (PARTITION BY ACCOUNT_ID
ORDER BY ACCOUNT_ID , VALID_FROM
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Col_name
Thanks for your quick response..
Actully Rows does not replace range in most of cases. is there's a workarround for this.
for UNBOUNDED there's no difference between RANGE and ROWS :)
Otherwise a possible workaround depends on what you actually need as a result...
E.g. there is another topic Help coding on count historical event within a fix time period, greatly thanks!, which unfortunately needs RANGE and there's no workaround.
The other Topic was based on the Time so extracting hour component from the eventtime helped to resolve it.
If you can provide some sample data and what you are expecting, it would help to understand the problem.