When reporting on a field that is not blank zero records are returned within Web Console Inventory Custom Report

  • 7001368
  • 16-Sep-2008
  • 27-Apr-2012

Environment

Novell ZENworks 7.5 Asset Management - ZAM7.5
Oracle Database

Situation

Custom Workstation report that contains a blank value when using the not = operator returns zero records using an Oracle database.

Resolution

Fixed in ZENWorks Asset Management Interim Release 16 or newer.

Interim Releases can be scheduled to run automatically or can be downloaded manually at https://download.novell.com .   The Interim releases can be set up within the ZAM Manager for the Task server to check the site on a scheduled basis, and download and apply them automatically. Please refer to the Help Section for details of how to set up automatic downloads if desired.

Each interim release is cumulative. If Interim Release 16 is not available due to a newer interim release being placed on the website, be assured that the code needed is in the later release.

Additional Information

The Oracle truth table at
http://www.oracle.com/technology/oramag/oracle/05-jul/o45sql.html
indicates that this where clause:

where not ( MyString = '' or Mystring is null )

will never return any rows.

Here is an example.

This returns rows, proving that ( middlename = '' or middlename is null ) ==
TRUE for some rows:
select * from nc_user where ( middlename = '' or middlename is null );
That is because, as the truth table shows, (UNKNOWN or TRUE) == TRUE.
(middlename = 'anything' evaluates to UNKNOWN when middlename is null).

And this returns rows, proving that ( middlename is null ) is TRUE for some
rows:
select * from nc_user where ( middlename is null );

Finally, this never returns any rows, because (UNKNOWN or FALSE) == UNKNOWN,
and (NOT UNKNOWN) == UNKNOWN:
select * from nc_user where not ( middlename = '' or middlename is null );