adding NULL values doesn't work

rbuser

When I join 2 tables where the join can be empty (no matching records in the
joined table),

a calculation like
sum(audittoken.intuse)+max(audit.transuse)
doesn't work!

I mean, it does
1+NULL = NULL
where I want
1+NULL = 1

How can I do that?

SELECT
audit.Terminalname,
sum(audittoken.intuse)+max(audit.transuse)
FROM "Audit.DB" Audit
LEFT OUTER JOIN "audittoken.DB" audittoken ON
(audittoken.Audittime = Audit.Audittime)
AND (audittoken.TerminalID = Audit.TerminalID)

CU
WS

rbuser

if you are using MSSQL then there is a Buildin-Function to get a special
Functionresult, if a field is NULL.

1+IsNull(max(audit.transuse),0)

if the Field is NULL then the Function uses the value in the second
parameter. This works also for strings.
If you not using MSSQL, then you could build a PassTrough-Function to do
this.

chris

rbuser

There are some strict rules about null values that need be followed. There
was a great post by Ray Lischner about this, but seeing it was Borland's
variant beta ng, it seems the message was not archived. AFAICR, null + 1
does equal null. Also, null < 1 is false and null > 1 is false as well.
There were a number of others.

--
Ed Dressel
Team DM