Using TDA premium 3.1 and when doing an export of query data to an Excel file or instance, the values are exporting as something other than (blank). So what’s happening when I’m pivoting the data after the export and trying to do a count on values, the null values are still being counted and are not registering as no value or (blank). This is different from when I use Toad for Oracle. Null values are exported as blank when using Toad for Oracle. I’m trying to transition over to TDA, but this difference in exporting it corrupting my pivot table reports.
Am I doing something wrong or is there something I need to change in the TDA setup to not export nulls as an actual value of some kind?
Thanks.
TDA currently does not have an option to specify text for null values the way Toad for Oracle does.
If you’re using Oracle, you can utilize the NVL function in your select statement. For example:
“SELECT CONTACT.REGION, NVL(CONTACT.NAME, ‘NULL’) NAME FROM …”
This will replace any null values with the word “NULL” in the output. Does this solve your problem?
-Mike
Thanks so much Mike for the reply!
Some iteration of that might work as a work around if I replace null values with “”.
I’m not actually trying to export a value though, the opposite actually. Here’s an example…
In Toad for Oracle, if I export to Excel say 10 rows and in one of the columns 6 of them are null, and then I pivot and do a count on that column in the pivot table, then the count is 4 because 6 of the rows are null and have no value in them. All is as it should be in this case.
In TDA however, using the exact same script, when I export to Excel the row count is still 10, and in that same column six of the rows are still null (same as from Toad for Oracle), but when I pivot and do a count, the count is no longer 4, but 10, even though there is nothing in the cell that can be seen or changed. No hidden space or anything that can be deleted out of cell, but for some reason it’s being counted as existing.
Said a different way, data looks exactly the same when exported from either TDA or Toad for Oracle, but for some reason, when exported from TDA, the null values are being counted in Excel as existing values even though nothing can be seen visually and when exported from Toad for Oracle they are not counted as existing values.
Thanks again for your thought! It’s much appreciated.
An update…
Discovered I can delete the invisible value being place in excel when exporting a null. However still confusing since it looks the same as a blank, but isn’t and don’t want to waste time deleting nulls.
Going to handle this diff between TDA and Toad for Oracle by changing export to 1 or 0 and summing in my pivot table instead of counting values.