예전에 제가 답변했던 사항이었는데
파라독스에서는 nvl을 지원하지 않습니다...
그때 국외 뉴스그룹에서 답변글을 달았던적이 있었는데
여기다가 옮겨드리졈..
국내 델파이사이트에서 답변을 찾기힘드시면
외국 뉴스구룹에서의 답변을 찾아보시는것도 현명한 방법이라 생각이 듭니다.
영어는 제가 좀 딸려서 번역을 해드리기보단 직접 읽어보시는게^^


뉴스그룹 조회사이트 : http://www.mers.com/searchsite.html


Subject: Re: NULL values with local SQL
Author: Steve Koterski (Borland)
Date: Wed, 13 Oct 1999 00:32:13 GMT
Newsgroup: borland.public.delphi.database.desktop


--------------------------------------------------------------------------------
On Tue, 12 Oct 1999 23:46:37 +0200, "Fran?is GAILLARD"
<f_gaillard@compuserve.com> wrote:


No, there is no direct way to force the BDE to treat NULL values as zeros
when performing aggregate operations or arithmetic calculations. I am not
aware of an nvl function (is it some nonstandard language extension
specific to one database system?). SQL-92 provides a NULLIF function, but
that is not in local SQL's subset.

Using local SQL as it is, you would need to use a UNION join of three
(possibly four) SELECT queries to get what you describe. Each would check
for a combination of NULL or non-NULL values in the Salary and Bonus
columns.

/* Neither are NULL */
SELECT Name, (Salary + Bonus) AS Total
FROM Employee
WHERE (Salary IS NOT NULL) AND (Bonus IS NOT NULL)
UNION ALL
/* Only Bonus is NULL */
SELECT Name, (Salary)
FROM Employee
WHERE (Salary IS NOT NULL) AND (Bonus IS NULL)
UNION ALL
/* Only Salary is NULL */
SELECT Name, (Bonus)
FROM Employee
WHERE (Salary IS NULL) AND (Bonus IS NOT NULL)
UNION ALL
/* Both are NULL */
SELECT Name, (0)
FROM Employee
WHERE (Salary IS NULL) AND (Bonus IS NULL)

I get the feeling that of your two columns, only the Bonus column is ever
going to be NULL. In that case, you can drop the second two SELECT queries
from this statement.

/* Neither are NULL */
SELECT Name, (Salary + Bonus) AS Total
FROM Employee
WHERE (Salary IS NOT NULL) AND (Bonus IS NOT NULL)
UNION ALL
/* Only Bonus is NULL */
SELECT Name, (Salary)
FROM Employee
WHERE (Salary IS NOT NULL) AND (Bonus IS NULL)

==========================================================================
Steve Koterski "Computers are useless. They can only give
Technical Publications you answers."
Borland -- Pablo Picasso (1881-1973)
http://www.borland.com/techpubs/delphi