초보자 wrote:

> 조금 급하거든요..

> 게시판을 다 뒤져봤는데..

> 몇개의 질문만 있을뿐 답글이 없습니다.

> 혹시 아는 분이 계시다면.. 좀 도와주세요.

>

> 파라독스에서 오라클에서 사용하는 nvl기능을 아시는 분..

> 제발..



Local SQL문에서는 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"

wrote:



>Is there a way with localSQL to treat NULL values as zero?

>like with the good old Session.BlankAsZero() in Paradox...

>or a SQL function like nvl(Field,defaultValue)

>

>Example:

>SELECT Name, Salary+Bonus AS Total FROM Employees

>Every employee has a salary, few have a bonus,

>but I want the total for everyone...



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