SQL query IFNULL (V4MD)

[Gregoire William]

here we go :
  set fldRef = addField(gDB, ArtistRef, "name", #kTypeString, 20 )
  set fldRef = addField(gDB, ArtistRef, "lname", #kTypeString, 20 )
  set fldRef = addField(gDB, ArtistRef, "method1", #kTypeString, 20, 0,
"IFNULL(name, 'rien')" )
  set fldRef = addField(gDB, ArtistRef, "method2", #kTypeString, 20, 0,
"IF(name is null, 'rien', name)" )

  SetBlank(PersonCursor)
  SetField(PersonCursor, "name", "jon" )
  SetField(PersonCursor, "lname", "roberts" )
  AddRecord(PersonCursor)

  SetBlank(PersonCursor) -- no name
  SetField(PersonCursor, "lname", "schima" )
  AddRecord(PersonCursor)

  result :
-- "jon    roberts    jon    jon    "
-- "    schima            "

   and not
-- "jon    roberts    jon    jon    "
-- "    schima   rien    rien    "

i first though it was the strings but i tried with a byte type field and i
have the same result...

SO, it seems SetBlank add a defaut value ("" for string, 0 for byte) and not
NULL
Am I wrong ?
I can bypass it using IF(name='', 'rien', name) ?

How does it work with ImportAscii ?
If I import an ascii file with ImportAscii(myTextFile, "$", RETURN) who
contains
---
jon$roberts
$schima
---
will it add a NULL for "schima" name or a "" ?

thanks

WilG


-----Message d'origine-----
De : valentina-bounces at lists.macserve.net
[mailto:valentina-bounces at lists.macserve.net]De la part de Ruslan
Zasukhin
Envoye : jeudi 7 octobre 2004 16:13
A : valentina at lists.macserve.net
Objet : Re: SQL query IFNULL

What you expect and what you get ?

    IFnull(name,'rien') will return 'rien' only if field name is NULL.


You have remove Frank... Where and how ?

    you sure that you have set it to NULL ?
    may be it is empty string ?