Oracle Communities

Oracle Analytics and AI

Child Item

SQL IS NULL error

Content

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?

Find more posts tagged with

Reporting

SQL

Answers

OTBI

Analyses

Comments

Prudence K

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