O$: Null Fields

[Doug Easterbrook]

hi Christine:

no way around it.   Nulls are fact of life for empty date fields in  
postgres (most databases for that matter).

there have been many discussion on 'is this good or bad' in past omnis  
posts.   Its neither good, nor bad, just a fact of life and you have  
to deal with them.    You can set everything to an initial value of  
Jan 1 1904 (or some arbitrary value, but thats worse



my favourite way to deal with them is:


if isclear(myDate)
or
if not(isclear(myDate))

and put your other code inside those if statements.




you can encounter the same with booleans.   Nulls can be useful  
there.   If you don't like nulls in booleans, then you can do two  
things:

set a default value for the boolean in the database


or when inserting, run a simple trick like

calc boolean as not(not(boolean))

always works to make it true if it was true and false if it was false  
or null.






Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 536-1205    Fax (403) 536-1210


On Apr-21-09, at 10:00 AM, omnisdev-en-request at lists.omnis-dev.com  
wrote:

> Hi Everyone,
>
> We are in the process of converting our software to SQL. In the old
> DML code we never had to deal with nulls much. Now I need some advice
> how to deal with them in some specific problems that have come up. We
> decided in most cases we don't want the overhead of always clearing
> nulls after finding data.
>
> When we have one field we are looking at there is no problem. When we
> have more than one it can cause problems. Here is an example. Any
> suggestions would be appreciated.
>
> If
> (pOldRow.FF_HIRED<>pNewRow.FF_HIRED)| 
> (pOldRow.FF_PREVIOUS_MONTHS<>pNewRow.FF_PREVIOUS_MONTHS)
>
> In this case we compare old and new values to see if anything has
> changed. Typically in this situation everything will be null except
> pNewRow.FF_HIRED. It still fails though because of the nulls. We do
> this type of thing all over the program. It would be good to not have
> to write a pile of code each time checking for nulls.