Prevent a null and negative value in SQL update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

Prevent a null and negative value in SQL update

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-14 : 06:54:56

Waddah writes "Hello all
I have the following statement
UPDATE INVOICE
SET
ADJ_AMT = NVL(ADJ_AMT,0) + :input_amt
WHERE
ID = :sql_id

ADJ_AMT should = 10
:input_amt should = -10

In some cases ADJ_AMT is zero and I end up with -10
This is unacceptable is there a way I can prevent a null and negative value in ADJ_AMT.

Thank you "

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-09-14 : 06:57:01

SQL Team is a Microsoft SQL Server site, Oracle questions can be posted here:

http://dbforums.com

Although the following might solve your problem:

UPDATE INVOICE SET ADJ_AMT = CASE WHEN NVL(ADJ_AMT,0) + :input_amt < 0 THEN 0 ELSE NVL(ADJ_AMT,0) + :input_amt END
WHERE ID = :sql_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources