I have a simple Query on Paradox table. One field must be a difference
between two fields from a table. I wrote:

select distinct (field1-field2) as Diff, field3, field4
from xy
where ....

Field2 can be 0, >9 or NULL. When Field2 is NULL, Diff is also NULL.

Any simple solutions? (I have solved with CalcField).

Thanks Tomi

Hi,

select distinct (field1-field2) as Diff, field3, field4
from xy
where field2 IS NOT NULL
UNION
select distinct (field1) as Diff, field3, field4
from xy
where field2 IS NULL

it should o what you expect, if NULL means the same as zero for field2
Eero

Tomi,

solution depends from database which you use. For example, in Oracle you can
use NVL function which allow to define some "default" value for null values

--
With best regards, Mike Shkolnik
E-Mail: mshkol...@scalabium.com
        mshkol...@yahoo.com
WEB: http://www.scalabium.com

Tomi <tomi.ze...@guest.arnes.si> D??? ???Y??:3c7c17d4_1@dnews...

You could use a UNION ALL query where the first SELECT retrieved the records
that are not null and the second handles the ones that are null.

--
Bill
(TeamB cannot answer questions received via email)

eero and Bill:
I have already tried with UNION but it is slow (it is faster to define a
calc filed and OnCalcField event).

Mike:
I am using Paradox

Thanks Tomi

In article <3c7d19f8$1_1@dnews>, tomi.ze...@guest.arnes.si says...

        Bill suggested UNION ALL which is different than UNION.  I can't
remember if Paradox supports this; check LocalSQL.hlp.

        HTH,

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
     Delphi/InterBase WebLog: http://delphi.weblogs.com
     InterBase PLANalyzer (Free IB optimization tool):
          http://delphi.weblogs.com/IBPLANalyzer

It does.

--
Bill
(TeamB cannot answer questions received via email)

Tomi,

In this case better to use a calculated field in application.

--
With best regards, Mike Shkolnik
E-Mail: mshkol...@scalabium.com
        mshkol...@yahoo.com
WEB: http://www.scalabium.com

Thank you all. It looks that using a calc field is the fastest way.

Tomi