Solved

Filter out NULL values not working in OBIEE column formula

Posted on 2015-02-02
1
1,205 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
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Row_number in SQL 6 34
SQL trigger 5 23
UTL_FILE invalid file operation 5 29
Loading flat file data in tables 2 41
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

831 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