PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US

  • Overview
  • How to manage the NULL value in an item?
  • Managing the NULL value
  • Limits
  • How to use the NULL value in my applications?
  • How to save a NULL value in an item?
  • Customizing the display of the items whose value is NULL
  • Query: Selection condition
  • Example
  • Limitations
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Managing NULL in HFSQL
ODBCNot available with this kind of connection
Overview
WINDEV, WEBDEV and WINDEV Mobile allow you to manage the NULL value in the items of HFSQL data files and for all the other types of accesses (Native, OLE DB, ...).
Therefore, when calculations are performed on the records found in your data files, the records containing a Null item will be ignored. For example, if a query calculates the average grade of students for the quarter, only the grades of the attending students will be taken into account. If a student is not attending, his grade will correspond to the Null value.
Versions 17 and later
iPhone/iPad This feature is now available for the iPhone/iPad applications.
New in version 17
iPhone/iPad This feature is now available for the iPhone/iPad applications.
iPhone/iPad This feature is now available for the iPhone/iPad applications.
Note: From version 19, HFSQL is the new name of HyperFileSQL.
How to manage the NULL value in an item?

Managing the NULL value

To manage the null value in your data files, you can use:
  • In the data model editor:
    • a "NULL supported" option available in the description of the data file ("Info" tab). This option is used to specify whether the management of NULL is supported by this data file. In this case, you will have the ability to manage the NULL value for the different items of the data file.
    • a "Default to NULL" option available for each item of your files ("General" tab of the item description). This option allows you to define the null value as default value for the item.
    • Versions 16 and later
      a "NULL allowed" option for each item of your files ("General" tab of the item description). This option is used to allow (or not) the use of the NULL value for this item. By default, if NULL is supported by the data file, the "NULL Allowed" option is checked for all the items of the data file.
      New in version 16
      a "NULL allowed" option for each item of your files ("General" tab of the item description). This option is used to allow (or not) the use of the NULL value for this item. By default, if NULL is supported by the data file, the "NULL Allowed" option is checked for all the items of the data file.
      a "NULL allowed" option for each item of your files ("General" tab of the item description). This option is used to allow (or not) the use of the NULL value for this item. By default, if NULL is supported by the data file, the "NULL Allowed" option is checked for all the items of the data file.
  • In programming, several properties:
    Versions 09 and later
    Null
    New in version 09
    Null
    Null
    ..Null is used to:
    • Assign NULL to the item for the current record.
    • Find out whether the item is associated with the NULL value for the current record.
    • Define the default value of an item when the item is described by programming.
    Versions 16 and later
    NullAllowed
    New in version 16
    NullAllowed
    NullAllowed
    ..NullAllowed is used to manage the NULL value in the items of HFSQL data files.
    Versions 09 and later
    NullSupported
    New in version 09
    NullSupported
    NullSupported
    ..NullSupported is used to manage the NULL value in HFSQL data files.

Limits

The NULL value cannot be used:
  • on the array items.
  • on the automatic identifiers.
  • on the composite keys.
How to use the NULL value in my applications?

How to save a NULL value in an item?

  1. Check the "NULL supported" box when the data file is described in the data model editor ("Description of data file" from the popup menu, "Info" tab).
    Versions 16 and later
    By default, "NULL Allowed" is checked for all the items of the data file.
    New in version 16
    By default, "NULL Allowed" is checked for all the items of the data file.
    By default, "NULL Allowed" is checked for all the items of the data file.
  2. Generate the analysis.
  3. Check "NULL if empty" for the edit control linked to the item. If no value was entered by the user in this control, the NULL value will be automatically assigned to the item when using:
Notes:
  • The "NULL if empty" option for the edit controls can also be used to manage the optional parameters of a query with parameters. See Using a query with parameters for more details.
  • If the default value of the item is NULL (box checked in the editor), HReset resets the ..Null property to True.

Customizing the display of the items whose value is NULL

By default, if an edit control is linked to an item whose value is NULL, the value displayed in the control is "". To customize this display mode, use ControlError associated with the ceNull constant.

Query: Selection condition

In the query editor, you have the ability to take into account or to ignore the records that have a null item. To do so, create a selection condition and choose "Is null" or "Is not null".
In a selection query that performs a calculation, all the records that have a null value (for the calculation item) will be ignored.

Example

The following example is a query used to calculate the average grade of students for the 2003 French class. If one of the grades corresponds to the NULL value, this grade is ignored.
SELECT Marks.Course AS Course,
Marks.MarkDate AS MarkDate,
LEFT(Marks.MarkDate,4) AS Year,
AVG(Marks.Mark) AS the_average_Mark
FROM Marks
WHERE Marks.Course = 'French'
AND Marks.Mark IS NOT NULL
AND LEFT(Marks.MarkDate,4) = '2003'
GROUP BY Marks.Course, Marks.MarkDate, LEFT(Marks.MarkDate,4)
Limitations
  • When managing the integrity, the NULL value is ignored
  • A linked record on 0 or empty string ("") is equivalent to a linked record on NULL.
  • For backward compatibility, the transfer of ..Null by assignment is not allowed.
    • Don't:
      File1.Item1 = File2.Item2
    • Do:
      File1.Item1..Null = File2.Item2..Null
Minimum required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment