Knowledge Base

PRB: Field Does Not Accept Null Values When Used with a Union

Article ID: 256157

Article Last Modified on 4/12/2000

APPLIES TO

Microsoft Visual FoxPro 5.0 Standard Edition
Microsoft Visual FoxPro 5.0a
Microsoft Visual FoxPro 6.0 Professional Edition

This article was previously published under Q256157

SYMPTOMS

If you use a SELECT statement with a UNION on a table without null values, the result set might contain null values. If the first table or cursor used in the UNION does not allow null values, you might receive the following error message:

Field "Fieldname" does not accept null values.

CAUSE

This error occurs if the first table in the SELECT statement does not allow null values.

RESOLUTION

This error can be prevented by creating an empty cursor that allows null values and using it first in the UNION.

Create a program that contains the following code and run it:

* Start of Code
CREATE CURSOR T_Emps ( ;
   Emp_ID   I NOT NULL, ;
   cName    C ( 7)  NOT NULL, ;
   Mgr_ID   I  NOT NULL)

* Populate the table with data.  Leave manager ID empty for some records.
INSERT INTO T_Emps ( Emp_ID, cName, Mgr_ID) VALUES ( 1, "Anne", 3)
INSERT INTO T_Emps ( Emp_ID, cName, Mgr_ID) VALUES ( 2, "Bob", 3)
INSERT INTO T_Emps ( Emp_ID, cName, Mgr_ID) VALUES ( 3, "Chuck", 9)
INSERT INTO T_Emps ( Emp_ID, cName, Mgr_ID) VALUES ( 4, "Diane", 9)
INSERT INTO T_Emps ( Emp_ID, cName, Mgr_ID) VALUES ( 5, "Earnest", 9)
INSERT INTO T_Emps ( Emp_ID, cName, Mgr_ID) VALUES ( 6, "Frank", 3)
INSERT INTO T_Emps ( Emp_ID, cName, Mgr_ID) VALUES ( 7, "Gillian", 0)
INSERT INTO T_Emps ( Emp_ID, cName, Mgr_ID) VALUES ( 8, "Harry", 3)
INSERT INTO T_Emps ( Emp_ID, cName, Mgr_ID) VALUES ( 9, "Ida", 0)

* Collect the employees who have managers.
SELECT T_Emps.Emp_ID, ;
       T_Emps.cName, ;
       Mgrs.Emp_ID AS Mgr_ID, ;
       Mgrs.cName AS MgrName ;
  FROM T_Emps ;
       INNER JOIN T_Emps Mgrs ;
       ON Mgrs.Emp_ID = T_Emps.Mgr_ID ;
  INTO CURSOR C_Emps

* Collect all employees, whether they have a manager or not.
SELECT T_Emps.Emp_ID, ;
       T_Emps.cName, ;
       Mgrs.Emp_ID AS Mgr_ID, ;  && may be NULL
       NVL( Mgrs.cName, SPACE( 7)) AS MgrName ;
  FROM T_Emps ;
       LEFT OUTER JOIN T_Emps Mgrs ;
       ON Mgrs.Emp_ID = T_Emps.Mgr_ID ;
  INTO CURSOR C_All

* Create a cursor to use in the union with nothing in it so it won't affect the results.
CREATE CURSOR C_Empty ( ;
  Emp_ID   I NULL, ;
   cName    C ( 7) NULL , ;
   Mgr_ID   I NULL, ;
   mrgname c(7) NULL)

* The error does not occur if C_Empty is used first
SELECT * ;
 FROM C_Empty;
  UNION ALL;
SELECT * ;
  FROM C_Emps ;
 UNION ALL ;
SELECT * ;
  FROM C_All ;
  INTO CURSOR C_Final
* End of Code

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior