This article use the schema SH : SH repository installation in the database and for the repository
To initialize the variables, two statements are used in order to test sysdate :
- the current date
select TIME_ID from TIMES WHERE TIME_ID = (select max(TIME_ID) from SALES)
- and sysdate
select SYSDATE from dual
Creation of the system and repository variable
OBIEE see the oracle date as a DATETIME then when you create the initialization blocks, you must use this format in the default value :
TIMESTAMP '2001-12-31 00:00:00'
Create then 4 initialization blocks :
- CurrentDateRepo : a repository variable with the current date statement
- CurrentDateSession : a session variable with the current date statement
- SysDateSession : a repository variable with the sysdate statement
- SysDateRepo : a repository variable with the sysdate statement
Verification of the type of the variable
- verification of the data type in the session manager. See : OBIEE - How to find the value, the data type of a Server variable ?
SELECT Calendar."Calendar Year" saw_0 FROM SH WHERE Calendar."Time Id" = VALUEOF(NQ_SESSION."CurrentDateSession") ORDER BY saw_0, saw_1
For the repository variable, the filter will be :
Calendar."Time Id" = VALUEOF("CurrentDateRepo")
You made an equality between a date (the column Time Id is defined as a date) and a datetime with the session variable CurrentDateSession. BI Server fired :
select distinct T3883.CALENDAR_YEAR as c1, cast(T3883.TIME_ID as CHARACTER ( 30 ) ) as c2 from SH.TIMES T3883 /* TIMES Fact */ where ( T3883.TIME_ID = TO_DATE('2001-12-31 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') ) order by c1, c2
With sysdate if you want to make a comparison with a date, you will get no rows because the variable don't have 00:00:00 in the hour:minute:second part of the timestamp.
You may have to cast it as a date
The repository variable doesn't support the date datatype from Oracle
Oracle has one date datatype and it's the timestamp datatype of OBIEE. It means that you have always the hour, minute and second.
If you use one repository variable in a report and that you don't want to see the hour, minute and second, you have to cast it (in the repository of in an anlytics) such as :
cast( myRepositoryVariable as date)