All Forums Database
moh_mf2 2 posts Joined 09/14
12 Jan 2015
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Hi Expert
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
 Y clause.
 
so it fails
 
is there's a work arround for that?

dnoeth 4628 posts Joined 11/04
12 Jan 2015

RANGE syntax is not implemented in Teradata, you need to user ROWS instead:

FIRST_VALUE (Col_name) 
OVER (PARTITION BY ACCOUNT_ID 
ORDER BY ACCOUNT_ID , VALID_FROM 
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Col_name

 

Dieter

moh_mf2 2 posts Joined 09/14
13 Jan 2015

Thanks for your quick response..
Actully Rows does not replace range in most of cases. is there's a workarround for this.
 
 
Moh Attia

dnoeth 4628 posts Joined 11/04
13 Jan 2015

Hi Moh,
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.

Dieter

vrushang 4 posts Joined 11/10
13 Jan 2015

Dieter,
The other Topic was based on the Time so extracting hour component from the eventtime helped to resolve it.
Moh,
If you can provide some sample data and what you are expecting, it would help to understand the problem.
 

You must sign in to leave a comment.