Board index » delphi » Query - calculating with NULL value

Query - calculating with NULL value

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

Re:Query - calculating with NULL value

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

Quote
"Tomi" <tomi.ze...@guest.arnes.si> wrote in message news:3c7c17d4_1@dnews...
> 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

Re:Query - calculating with NULL value

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...

Quote
> 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

Re:Query - calculating with NULL value

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

Re:Query - calculating with NULL value

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:

Thanks Tomi

Re:Query - calculating with NULL value

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

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

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

Re:Query - calculating with NULL value

Tomi,

Quote

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

Re:Query - calculating with NULL value

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

Tomi