Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
DATABASES, TABLES AND QUERIES >>  BE AWARE OF THE NVL()

  Boudewijn Lutgerink
  Where is Boudewijn Lutgerink?
 Hoonaardstraat, Driel
 Netherlands
 Boudewijn Lutgerink



after much effort you finally have this SQL select statement where you also want to replace any NULL values in a field in a table with another value. using the NVL() function however might bring you results you don't want.
Consider this:
The table has (at least) a field named field1 and field1 might contain .null. values.

Using a statement like:
Select NVL(field1,"") from MyTableWithNullableFields


Will return only the first digit from the nullable field. (I found this both in vfp8 and 9)
The NVL function takes the length of the replacement character as leading for the rest of the records that do NOT have null values.
Thus returning only the value in the fields truncated to the length of the second parameter of the nvl() function

The solution is to use the actual fieldLength of the field as in:
SELECT NVL( field1, SPACE(FSIZE("field1","MyTableWithNullableFields") )) as field1 FROM MyTableWithNullableFields

FEEDBACK

Ravi Taxali @ 12/8/2006 7:36:13 PM
I did see the problem you mentioned once on my PC. However, when I try the following code, I don't see the problem. I tried on VFP 8 and VFP6. I don't know if it has to do with environment settings.

CREATE CURSOR curTest (NAME C(10) NULL)
INSERT INTO curTest VALUES ("Kumar")
INSERT INTO curTest VALUES (.NULL.)
INSERT INTO curTest VALUES ("Rajan")
SELECT NVL(NAME,"") from curtest

mike castillo @ 12/9/2006 12:44:55 AM
Other solution would be:

SELECT IIF(ISNULL(cname),SPACE(FSIZE("cname","table1")),cname) as cname FROM table1

Ken Murphy @ 2/7/2007 2:51:49 PM
Ravi,

If the first record that your SELECT finds has curTest.Name = NULL, you will see the problem.

Negoianu Cristi @ 3/16/2007 10:20:25 AM
The problem is not the NVL function. The problem is the Select command. The length of the resulting filed is decided from the first record.

Samir H. @ 8/30/2010 7:29:25 PM
> ...I found this both in vfp8 and 9
Why not use the CAST() function, instead of SPACE(FSIZE()) ?



Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: