O$: Null Fields

Christine Penner christine at ingenioussoftware.com
Tue Apr 21 13:14:33 EDT 2009


All of our sql code comes from an object and we don't use the $select 
method so we could do what you suggest.

We use Postgres. In Postgres the isnull equivalent is coalesce I 
believe.  Am I right that what you suggest is I replace all of my 
select columns with Select coalesce(Col1,''),coalesce(Col2,'')... Do 
you think there will be a time hit on putting that together for a 
select that has 400 or more columns? If its not a big time hit it 
sounds like that may be a good solution.

Christine

At 09:52 AM 21/04/2009, you wrote:

>----- Christine Penner <christine at ingenioussoftware.com> wrote:
> > I have looked through the past discussions on nulls. I asked a
> > similar question before but they are all more general then what I'm
> > looking for. We have decided that we will be dealing with nulls. What
> > I want is some suggestions on how to deal with specific things like
> > the example I gave. I could write a pile of code checking nulls but I
> > thought someone may have a more elegant solution then what I would
> > come up with.
>...
>
>If 
>(pOldRow.FF_HIRED<>pNewRow.FF_HIRED)|(pOldRow.FF_PREVIOUS_MONTHS<>pNewRow.FF_PREVIOUS_MONTHS)...
>
>Your options are limited.  One choice is to select an empty back 
>when the column is null.  This has the advantage of populating your 
>list columns for you on the select and if you have but one select 
>for that particular need, then all code that uses that select will 
>have the columns populated.  That is, you call something like 
>oSQL.sel_my_table and within that method you do something like 
>"select isnull(col1,'')..." and you ALWAYS call oSQL.sel_my_table 
>whenever you need data from that table wherever needed in your 
>application then this will work.  If you're doing listname.$select() 
>then obviously this won't work.  (I suggest you adopt a single 
>object to hold all of your sql code (if you have not already done 
>so) if only because it assures you have the same sql for all calls 
>made for that data in your application.  Much easier to maintain if 
>there is only ever one place to get a specific set of data...)
>
>The other is to do the "pick" trick:
>
>If 
>(pOldRow.FF_HIRED<>pNewRow.FF_HIRED)|(pick(isnull(pOldRow.FF_PREVIOUS_MONTHS),(pOldRow.FF_PREVIOUS_MONTHS<>pNewRow.FF_PREVIOUS_MONTHS),kfalse)
>
>presuming the FF_HIRED column is never null.  There is also a new 
>function, "isclear" I believe, in newer versions of Studio that is 
>supposed to do the same thing as the pick, in effect anyway.
>
>Note the user's tabbing though the column on the window will change 
>a null to an empty which can cause some "interesting" behavior 
>depending on what your desired outcome is.
>
>Regards,
>
>Joe Maus
>
>
>_____________________________________________________________
>Manage your list subscriptions at http://lists.omnis-dev.com




More information about the omnisdev-en mailing list