• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2236
  • Last Modified:

Filter out NULL values not working in OBIEE column formula

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
mariita
Asked:
mariita
1 Solution
 
scsymeCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now