General OBIEE

Get Involved. Join the Conversation.

Topic

    Aaron Leggett
    SQL IS NULL error
    Topic posted February 14, 2017 by Aaron LeggettGold Trophy: 10,000+ Points, tagged Analyses, Core HR, OBI Answers, OBIEE Answers, OTBI, Public Sector, Reports, SQL 
    115 Views, 3 Comments
    Title:
    SQL IS NULL error
    Content:

    Hi 

    We're trying to create multiple reports where we want to bring back a column with no data in (e.g. Missing Bank Details where the employee does not have a bank account number). When we run the report without a filter, we get back all the results as expected (records who have bank details and records who do not have bank details). When we apply the IS NOT NULL filter, we get back all the results as expected; records whom have bank details. However, when we apply the IS NULL filter, we get an error and don't get back any results when we should be getting back around 300. 

    Currently we're having to run the report without a filter and then apply filters in excel to find out which records are missing bank details. Is there any other formula we could try other than IS NULL? 

    Comment

     

    • Prudence Kruchten

      Hi Aaron - sometimes I have this problem, so I put a filter in to a few IDs (candidate or employee, whichever works for you) that I am expecting blank AND non-blank.  This way, I know the data is even pulling through (without the IS NULL or IS NOT NULL filter).  If the blank ones are showing up, then add the IS NULL ... did you get no results?  If so, remove that filter and then add a filter to where the column is equal to '' (that's tick tick,not double quotes). and see if that pulls back the results.  If you get no results again, I would submit a ticket to Oracle. If you do get results then you can use that as your filter option (vs the IS NULL).  Good luck!

    • Chris Dabel

      The only way to really understand what is going on with this is to look at the underlying, physical SQL that the report is using.

      I recommend logging a Service Request so we can help you get to the bottom of it.
       

      Chris Dabel
      Oracle Support

       

    • Neil Peacock

      Hi Aaron,

      Our analysis works with is null for this report, however it has been created through a data model. Can I ask what item you are performing the is null on? We have it on  "IBY_EXT_BANK_ACCOUNTS"."BANK_ACCOUNT_NUM_ELECTRONIC"

      However without the SQL it is hard to know exactly and the best avenue might be to make a ticket.

      Neil