Microsoft KB Archive/238518

= The SELECT-SQL statement that uses an OUTER JOIN clause adds NULL values to the output in Visual FoxPro =

Article ID: 238518

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 7.0 Professional Edition
 * Microsoft Visual FoxPro 8.0 Professional Edition
 * Microsoft Visual FoxPro 9.0 Professional Edition

-



This article was previously published under Q238518



SYMPTOMS
Executing a SELECT-SQL statement using an OUTER JOIN clause results in null values being inserted into the fields of the output for which no matching record is found.



RESOLUTION
If the SELECT-SQL statement is being used to output data to a table, one workaround is to issue an ALTER TABLE command and set the columns of the table to NOT NULL. The following code illustrates this workaround: SELECT ALIAS FOR i=1 TO FCOUNT(ALIAS) ALTER TABLE (ALIAS) ALTER COLUMN (FIELDS(i)) NOT NULL ENDFOR This workaround is implemented in the code example in the MORE INFORMATION section following.



Steps to Reproduce Behavior
  Create a program file called OUTJOIN.PRG using the following code: SET SAFETY OFF

CREATE TABLE test1 (pid c(10), nvar1 N(10,2) NULL, dvar2 d, nvar2 N(10,2) NULL, mvar m) INSERT INTO test1 VALUES ('ABCDEFGHIK',3,DATE,3,REPLICATE('ABCDE ',5)) INSERT INTO test1 VALUES ('ABCDEFGHIJ',3,DATE,3,REPLICATE('ABCDE ',5)) INSERT INTO test1 VALUES ('ABCDEFGHIL',3,DATE,3,REPLICATE('ABCDE ',5))

CREATE TABLE test2 (pid c(10), nvar1 T, nvar2 Y, fvar1 F(10,2), fvar2 F(10,2)) INSERT INTO test2 VALUES ('ABCDEFGHIJ',DATETIME,45,3,4) INSERT INTO test2 VALUES ('ABCDEFGHIL',DATETIME,45,3,4) INSERT INTO test2 VALUES ('ABCDEFGHIZ',DATETIME,45,3,4)

SELECT test1.pid, test1.nvar1, test1.dvar2, test1.nvar2, test1.mvar, ; test2.pid AS testa, test2.nvar1 AS ntesta, test2.nvar2 AS ntestb, ; test2.fvar1 AS ftesta, test2.fvar2 AS ftestb ; FROM test1 ; LEFT OUTER JOIN test2 ; ON test2.pid=test1.pid ; INTO TABLE test3

SELECT test2.pid, test2.nvar1, test2.nvar2, test2.fvar1, test2.fvar2, ; test1.pid AS testa, test1.nvar1 AS ntesta, test1.dvar2 AS ntestb, ; test1.nvar2 AS ftesta, test1.mvar AS ftestb ; FROM test1 ; RIGHT OUTER JOIN test2 ; ON test1.pid=test2.pid ; INTO TABLE test4

SELECT test3 BROWSE

SELECT test4 FOR i=1 TO FCOUNT(ALIAS) ALTER TABLE (ALIAS) ALTER COLUMN (FIELDS(i)) NOT NULL ENDFOR BROWSE  Execute OUTJOIN.PRG. Note that when the Test3 Browse window appears, the last five columns of the first row are set to .NULL.

Note that when the Test4 Browse window appears, the last five columns of the third row are empty.

(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by John Desch, Microsoft Corporation.

Keywords: kbcodesnippet kbdatabase kbpending kbprb kbsqlprog KB238518

-

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

© Microsoft Corporation. All rights reserved.