NULL value

PQL recognizes IS NULL and IS NOT NULL in exactly the same way as SQL does.

Examples

You can try the following examples against your own data in the GoToAssist search field, or against fictitious data in the Query Sandbox:

• Example #1: Query includes null values
• Example #2: Query excludes null values
• Example #3: Query excludes null systems

Example #1: Query includes null values

Here is a broad query that reports the status of free memory from all devices:

  SELECT status/memory_free                 
  FROM /network/device
               

When this query is run against a database that contains devices without all of the requested information, the result includes several IS NULL values:

  row: 
    status/memory_free: null                
  row: 
    status/memory_free: null
  row: 
    status/memory_free: null
  row: 
    status/memory_free: 
      memory_free: 355516416

Example #2: Query excludes null values

You can focus the query and avoid cluttering the result with the IS NULL values by running a query like this:

  SELECT status/memory_free 
  FROM /network/device
  WHERE status/memory_free IS NOT NULL
 

When this query is run against the same data as above, the result is much more specific:

  row:
    status/memory_free: 
      memory_free: 355516416
                

Example #3: Query excludes null systems

You can focus the query and avoid cluttering the result with the IS NULL values by running a query like this:

SELECT nvl(
  system/name, 
  system/dns_name
  ) as "Name",
  nvl(
    system/model, 
    system/class, 
    wmi/win32_computersystem/domainrole
  ) as "Type",
    interface/inet/ip_address as "IP Address",
    last_seen 
FROM /network/device 
WHERE system IS NOT NULL 

Depending on your database, this query finds something like the following. Note that although there are some null values in the results, the query has filtered any systems that are null:

row
   Name dallas
   Type null
   IP Address 10.10.10.10
   last_seen 2008-04-05T17:36:19.0000000Z
row
   Name null
   Type null
  IP Address
     ip_address 10.20.20.20
     ip_address 10.30.30.30
   last_seen 2008-04-05T17:39:48.0000000Z
row
   Name cuernavaca
   Type null
   IP Address 10.40.40.40
   last_seen 2008-04-05T17:35:42.0000000Z 
   ...   

Related topics

• Functions
• Operators
• Expressions
• Read about PQL

| Views: 1168 | Last Updated: Thu, May 09 2013 2:28 PM

Is this article helpful?