Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

SQL IS NULL error

74
Views
3
Comments
Aaron Leggett
Aaron Leggett Rank 5 - Community Champion
in Oracle Transactional Business Intelligence

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? 

Tagged:

Comments

  • Prudence K
    Prudence K Rank 5 - Community Champion

    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
    Chris Dabel Rank 2 - Community Beginner

    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
    Neil Peacock Rank 2 - Community Beginner

    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