[Zope] dtml and 'none' behaviour

[Chris Kratz]

You may also use the null= tag on dtml-var.  If the field is None, it will 
replace the value with whatever is in the null="" tag.  Look in the online 
doc that comes with zope.

ie <dtml-var somefield null="">

We use nulls quite extensively since it bears the connotation of not entered 
which in our case is different then entered but empty.  But, there are cases 
where nulls can throw you if you don't watch it.  One simple example is 
this...

create temp table test (id serial, val text);
insert into test(val) values('A');
insert into test(val) values(NULL);
insert into test(val) values('B');
select * from test;
 id | val
----+-----
  1 | A
  2 |
  3 | B
(3 rows)
select * from test where val<>'A';
 id | val
----+-----
  3 | B
(1 row)

Even though this is correct sql, it is hard to get users heads around the fact 
that if you say give me everything where val<>somevalue, it won't pull null 
records.  The correct statement if you do want nulls is:

select * from test where val<>'A' or val is null;

-Chris

On Friday 14 January 2005 03:52 pm, Garry Saddington wrote:
> Thanks you have confirmed what I thought to be the case - avoid nulls in
> the database. It is what my instinct told me but I just wanted to have a
> Zope type view on it.
> regards
> garry
> ----- Original Message -----
> From: "Tino Wildenhain" <tino at wildenhain.de>
> To: <garry at scholarpack.org>
> Cc: <zope at zope.org>
> Sent: Friday, February 11, 2005 7:34 PM
> Subject: Re: [Zope] dtml and 'none' behaviour
>
> > Am Freitag, den 11.02.2005, 19:21 +0000 schrieb garry saddington:
> >> I have a zsql method which returns some null values. These are then
> >> displayed
> >> in a table but the null values become 'None'. Is there any way to get
> >> round
> >> this behaviour and have empty cells in the displayed results? I can do
> >> it with several dtml-if and dtml-unless calls but wondered if there was
> >> a more
> >> subtle approach.
> >
> > Well, null is to SQL as None to python. So either you solve this
> > at database level or in python. For example in python you could
> > use:
> >
> > return [{'foo':row.foo or '',
> >         'bar':row.bar or ''} for row in context.yourzsqlmethod()]
> >
> > with this little "or" you solve this quite elegant. Every
> > value which evaluates to false (which is: 0, "", ... and None)
> > let the expression return its right side, which is '' in this
> > case.
> >
> >
> > On database level you could use coerce() which returns the
> > first non null argument:
> >
> > SELECT coerce(foo,'') as foo,coerce(bar,'') as bar FROM ...
> >
> > or even better try to avoid nulls where possible.
> >
> > Regards
> > Tino
>
> _______________________________________________
> Zope maillist  -  Zope at zope.org
> http://mail.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists -
>  http://mail.zope.org/mailman/listinfo/zope-announce
>  http://mail.zope.org/mailman/listinfo/zope-dev )