Solved

Filter out NULL values not working in OBIEE column formula

Posted on 2015-02-02
1
1,383 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
[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
1 Comment
 
LVL 4

Accepted Solution

by:
scsyme earned 500 total points
ID: 40584994
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to tune the query 17 81
oracle forms question 9 39
Can a Trigger trigger a Trigger? 4 46
Access 2003 query, remove dupes 17 18
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

739 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