RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Database

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?

4 REPLIES
Senior Apprentice

Re: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

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

Re: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Thanks for your quick response..

Actully Rows does not replace range in most of cases. is there's a workarround for this.

Moh Attia

Senior Apprentice

Re: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

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.

Re: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

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.