Microsoft KB Archive/256157

= 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)

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)
 * Populate the table with data. Leave manager ID empty for some records.

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 the employees who have managers.

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
 * Collect all employees, whether they have a manager or not.

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

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



STATUS
This behavior is by design.



Steps to Reproduce Behavior
  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)

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)
 * Populate the table with data. Leave manager ID empty for some records.

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 the employees who have managers.

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
 * Collect all employees, whether they have a manager or not.

SELECT * ; FROM C_Emps ; UNION ALL ; SELECT * ; FROM C_All ; INTO CURSOR C_Final  Note that you receive the error shown in the "Symptoms" section above.
 * UNION the two cursors together. Use cursor without any nulls first.
 * This will generate the 1581 error, "Field MGR_ID does not accept null values."
 * End of Code

Keywords: kbcodesnippet kbdatabase kbprb kbclient KB256157

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.