Syntax for OIBEE sysdate function

I am using the Oracle BI Administration Tool  to create a business (BMM) model.
I want to create a logical column that calculates age based on the SYSDATE function.
Is the SYSDATE function available in the Oracle BI Admin tool or do I have to create a variable?

I tried the synatx below but I got an error:
SUM(CASE WHEN floor(MONTHS_BETWEEN(sysdate, enrol.student_dob)/12) < 15 THEN 1 ELSE 0 END)
mariitaAsked:
Who is Participating?
 
Haris DjulicConnect With a Mentor Commented:
Hi,

so my post did not answer your original question ?

Is the SYSDATE function available in the Oracle BI Admin tool or do I have to create a variable?
0
 
Haris DjulicCommented:
Hi,

There is currentdate variabke which you can use instead of sysdate.
0
 
mariitaAuthor Commented:
When I use current_date, I get the following error message:
[nQSError: 27002] Near <(>: Syntax error [nQSError: 26012] .

Here is the SQL statement that I am using:
SUM(CASE WHEN floor(MONTHS_BETWEEN(current_date, "Enrolment - College"."F College Enrolment"."Student DOB"   )/12) < 15 THEN 1 ELSE 0 END)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
mariitaAuthor Commented:
I partly solved the problem. It turns out OBIEE doesn't support MONTHS_BETWEEN, so I had to use the TimeStampDiff function. I am no longer getting an error message, but I am also not getting the same output as the original SQL.

Original SQL:

SUM(CASE WHEN floor(MONTHS_BETWEEN(sysdate, enrol.student_dob)/12) < 15 THEN 1 ELSE 0 END)


Option #1:

SUM(CASE WHEN TimeStampDiff(SQL_TSI_year, "Enrolment - College"."F College Enrolment"."Student DOB", current_date) < 15 THEN 1 ELSE 0 END)

Option #2:

SUM(CASE WHEN (TimeStampDiff(SQL_TSI_month, "Enrolment - College"."F College Enrolment"."Student DOB", current_date)) / 12 < 15 THEN 1 ELSE 0 END)
0
 
mariitaAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for mariita's comment #a40477128

for the following reason:

This did work after all.
0
 
mariitaAuthor Commented:
It did answer the question, thanks! I also needed to use the TimeStampDiff function.
0
All Courses

From novice to tech pro — start learning today.