asked on

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)

Haris Dulic

Hi,

There is currentdate variabke which you can use instead of sysdate.

mariita

ASKER

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)

mariita

ASKER

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)

mariita

ASKER

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.

ASKER CERTIFIED SOLUTION

Haris Dulic

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mariita

ASKER

It did answer the question, thanks! I also needed to use the TimeStampDiff function.