Solved

Filter out NULL values not working in OBIEE column formula

Posted on 2015-02-02
1
977 Views
Last Modified: 2015-02-03
I have a column formula that I've almost, but not quite, gotten to work. The problem is that "Program Start Date" field is a VARCHAR in the format 201209 and is sometimes NULL, so the formula doesn't work when this field is NULL. I am able to filter out NULL values by creating an analysis, and when I do this the formula does work. But when I try to do the same thing by adding a condition to the column formula, it doesn't work.

SUM(CASE WHEN

"Enrolment - College"."F College Enrolment"."Program Start Date" is not NULL and

(TimeStampDiff(SQL_TSI_month, TO_DATETIME("Enrolment - College"."F College Enrolment"."Program Start Date" || '01', 'yyyy/mm/dd') , TO_DATETIME(LEFT("Enrolment - College"."D Time"."Fiscal Year", 4)||'/09/01', 'yyyy/mm/dd'))) / 12 >= 5

THEN 1 ELSE 0 END)
0
Comment
Question by:mariita
1 Comment
 
LVL 4

Accepted Solution

by:
scsyme earned 500 total points
Comment Utility
You can't short circuit a null in SQL like you would in Java, C#, etc. You could try one of two alternative approaches:
1) make the first case statement handle the instances where the date is null
2) wrap all references to the date field in a NVL function (Oracle's version if ISNULL)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
How to increase the row limit in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now