Solved

Syntax for OIBEE sysdate function

Posted on 2014-12-02
6
343 Views
Last Modified: 2014-12-04
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)
0
Comment
Question by:mariita
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40476755
Hi,

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

Author Comment

by:mariita
ID: 40476913
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
 

Author Comment

by:mariita
ID: 40477128
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mariita
ID: 40480787
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
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40480269
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
 

Author Closing Comment

by:mariita
ID: 40480788
It did answer the question, thanks! I also needed to use the TimeStampDiff function.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to increase the row limit in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question