I always select F_FIXEDPOINT(field,2) and sum(F_FIXEDPOINT(field,2)) - that
way I always get consistent results

Alan

-----Original Message-----
From: Paul Vinkenoog [mailto:paul@...]
Sent: Wednesday, 15 January 2003 11:21 AM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] Case Sensitivity on Indices


Hello Helen,

> If you have certain char or varchar columns that need a case-insensitive
> search frequently, create an additional column of the same length which is
> populated automatically by before insert and before update triggers and
> stores your value as upper case. Index this proxy search column instead

of

> the actual column.

[ snip ]

> create trigger bu_proxy_last_name for customer
> active before update position 0
> as
> begin
> if (new.last_name <> old.last_name
> and new.last_name is not null) then
> new.proxy_last_name = upper(new.last_name);
> end

I think you ought to extend the body to:

begin
if (new.last_name is null) then
new.proxy_last_name = null;
else if (old.last_name is null or new.last_name <> old.last_name) then
new.proxy_last_name = upper(new.last_name);
end

You have to test new.last_name against null because otherwise you
might leave old non-null values in the proxy column.

Second, if you test (new.last_name <> old.last_name) this will return
false if old.last_name is null. If you subsequently conclude that
new.last_name == old.last_name, you might leave the proxy column null
when you oughtn't. Hence the explicit test for old.last_name nullity.

The following expressions all return false:

null = null
null <> null
null = 'Pim'
null <> 'Pim'

Nasty buggers, nulls!


Greetings,
Paul Vinkenoog



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/