Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread Problem with 2 integer filter : EmptyValue <> 0 !!
Wed, Jun 18 2008 5:44 AMPermanent Link

"Enrico Ghezzi"
Hi

I have this table

INT-FIELD1    INT-FIELD2
       5                     
       5                        0

my filter in TDbIsamTable

Filter := 'FIELD2  < FIELD 1'

Result only 1 record :

INT-FIELD1    INT-FIELD2
       5                        0


Why EmptyValue  <> 0   ?

How i can do it : ?  FILTER :=  INTEGER(FIELD2)  < INTEGER(FIELD1) '   ?
Wed, Jun 18 2008 5:58 AMPermanent Link

"Enrico Ghezzi"
ofcourse by FilterRecord , work well

but i should use a Filter property.




procedure TfrmAssegnato.tblAssegnatoFilterRecord(DataSet: TDataSet;
 var Accept: Boolean);
begin

if DataSet.FieldByName('FIELD2').AsFloat <
DataSet.FieldByName('FIELD1').AsFloat then
  begin
  Accept := False;
  exit;
  end;

Accept := True;
end;
Wed, Jun 18 2008 6:20 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Enrico


Welcome to the NULL confusion club.

You have three options:

1. Set a default in the table for the fields to have a value of 0 then your filter will be fine

2. add in an explicit test for NULL eg Filter := '(FIELD2  < FIELD1) OR (FIELD2 IS NOT NULL AND FIELD1 IS NULL)'

3. Use COALESCE eg

Filter := 'COALESCE(FIELD2,0)  < COALESCE(FIELD1,0)'

Roy Lambert [Team Elevate]
Wed, Jun 18 2008 7:00 AMPermanent Link

"Robert"

"Enrico Ghezzi" <Enrico.Ghezzi-70_XXXXXXXXX@libero.it> wrote in message
news:288DF9B8-370E-45D5-823B-C17C44FC6E63@news.elevatesoft.com...
> Hi
>
> I have this table
>
> INT-FIELD1    INT-FIELD2
>        5                     5                        0
>
> my filter in TDbIsamTable
>
> Filter := 'FIELD2  < FIELD 1'
>
> Result only 1 record :
> INT-FIELD1    INT-FIELD2
>        5                        0
>
>
> Why EmptyValue  <> 0   ?

because null is neither greater or less than a number. it is a different
thing

>
> How i can do it : ?  FILTER :=  INTEGER(FIELD2)  < INTEGER(FIELD1) '   ?
>

same as you would in SQL

cast (field2 as integer) < field1

robert


Image