TRANSFORM(NULL) is not NULL.

[Mike Lewis]

Here's something that just caught me out.

x = NULL
y = TRANSFORM(x)

What do you suppose y contains?

I assumed that y would be NULL. After all, any expression that includes a NULL will evaluate to NULL.

In fact, y will be a string containing six characters: .NULL.

In retrospect, I suppose it's obvious, but I just spent nearly an hour trying to figure out a bug before it dawned on me. So I thought I'd post it here in case anyone else gets caught by it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[bborissov]

Mike,
may be this is by design. TRANSFORM() is supposed to convert any data type to sting, so it converts it .

Borislav Borissov

 

[bborissov]

Oops, I forgot.
Thank you for that post. As you I also will suspect to have a .NULL. instead of [.NULL.].

Borislav Borissov

 

[MarciaAkins]

Hi Mike.

This is what I had to say on this subject in the white papaer from my seesion "It Seemed Like a Good idea at the Time"

However if a value was defined as some other data type and has subsequently acquired a NULL value the TYPE() function returns the original data type - with potentially catastrophic results!

lnTotal = 100
luVal = 10
? TYPE( ‘luVal’ )				&& Type = “N”
luVal = IIF( luVal > 20, luVal, NULL )	
? TYPE( ‘luVal’ )				&& Value = NULL but Type = “N”
lnTotal = lnTotal + luVal 			&& lnTotal = NULL - not even 100!!!

Why is this so bad? Consider what would happen if you were to be using code which accumulated a values inside a loop (e.g. a SCAN) and one record contained a NULL Not only would your total so far be lost, but you would not even get the value back because as soon as you reached the null record the value in the accumulator would be set to NULL and all subsequent additions would simply end up as NULL You would not get an error, but you certainly would not get the result you desired.

Fortunately you CAN use functions like SUM() and the CALCULATE functions, or SQL SUM() even when NULL values are present. Visual FoxPro is intelligent enough to simply ignore such values - and indeed this is one of positive benefits of NULL support when calculating averages and counting records - NULLS are ignored and do not affect the results.

One other specific gotcha! with NULL values arises when you are concatenating character strings, one of which contains a NULL.

luVal = NULL
? “Fred “ + luVal	&& Gives a “Data Type Mismatch” Error
? “Fred “ + ALLTRIM( luVal )		&& No error, but the result is now NULL!

Personally, I think that this one is a bug! If a data type mismatch arises when a character string containing a NULL is concatenated with another, then simply applying an ALLTRIM() function should not allow the operation to proceed without error. I fell over this one in an application and it took a lot of debugging to find out what was happening.


Marcia G. Akins

http://www.TightlineComputers.com

 

[Dan Freeman]

It gets even better if you play with OldVal() on a newly appended record.

Create Cursor test (test c(10) Not Null)
CursorSetProp("BUFFERING",5)
Append Blank
?Transform(OldVal(test))

You get the string ".NULL." even though the field doesn't support null. Set Null Off has no effect.

(This showed up in some generic data handling code I inherited. I kept wondering how users were getting that value in fields I never let them edit! LOL)

 

[Mike Lewis]

Borislav, Marcia, Dan.

Thanks to all of you for your insights and comments -- as valuable as ever. I never knew there was so much I didn't know about TRANSFORM() and NULL.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk