filter an expanded period

General
Highlighted
Enthusiast

filter an expanded period

I am having a table for account balance

It is slow change dimenssion.

The table has a period column representing the load start and load end for each row in the table 

 

I want to use the expand funcaiton on the period column starting from 1 August to 31 August.

 

SyntaxEditor Code Snippet

SELECT *
FROM (
              SELECT BEGIN(ex_Validy) Cal,ACCT_KEY,Bal
              FROM  Account_BAL
               EXPAND ON Validity AS ex_Validy            ) AS t
WHERE Cal BETWEEN DATE'2018-08-01' AND DATE'2018-08-31' 
 

 the query is very slow , because It berform the expand first then do the filteration , and the start date for for some record is 1 Jan 2000.

 

First solution come to my mind is to filter the dim_Calender and left join Account bal table , 

 

Is there any condition to limit the expand using the anchor key word

 

 


Accepted Solutions
Teradata Employee

Re: filter an expanded period

ANCHOR would be used to control the granularity of the expansion.

To limit the range, use FOR. Note that it's a period expression so it doesn't include the ending bound, while BETWEEN in the original query does include the ending bound.

 

SELECT BEGIN(ex_Validy) Cal,ACCT_KEY,Bal
FROM  Account_BAL
EXPAND ON Validity AS ex_Validy
FOR PERIOD(date'2018-08-01',date'2018-08-31'+1)
1 ACCEPTED SOLUTION
1 REPLY
Teradata Employee

Re: filter an expanded period

ANCHOR would be used to control the granularity of the expansion.

To limit the range, use FOR. Note that it's a period expression so it doesn't include the ending bound, while BETWEEN in the original query does include the ending bound.

 

SELECT BEGIN(ex_Validy) Cal,ACCT_KEY,Bal
FROM  Account_BAL
EXPAND ON Validity AS ex_Validy
FOR PERIOD(date'2018-08-01',date'2018-08-31'+1)