Override 0.00 with NULL value - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Override 0.00 with NULL value

Author

Topic

ankur_gurha
Starting Member

20 Posts

Posted - 2006-07-21 : 06:26:32

HI,

I am trying to override an int column with 0.00 value in it during the query with the NULL value but somehow dont know as how to really do it..

I've pasted the query which i am trying , please have a look and let me know as it can be done

SELECT actualCost , manualOverRide , originalEstimate , COALESCE( actualCost , manualOverRide , originalEstimate ) AS SubTotal
INTO #t

From JobCostElements As jce

INNER JOIN Job As job
ON job.ID = jce.job

Where job = '-2966946650436068841'
--
IF (SELECT actualCost FROM #t ) = null
BEGIN
UPDATE #t
SET actualCost = 0.00
END

ELSE
IF (SELECT manualOverRide FROM #t ) = 0.00
BEGIN
UPDATE #t

SET manualOverRide = null
END

SELECT *From #t

SELECT (SELECT SUM (SubTotal) As total FROM #t )

DROP Table #t

Infact this query while execution throws up this message.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Line 42
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(15 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.

Thanks in advance

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-21 : 07:35:54

It's not clear to me what you're trying to achieve.

>an int column with 0.00 value

int column can not have 0.00 value, since ints have no decimal places. So you really mean 0?

Maybe one of these will help...

update myTable set myColumn = 0 where myColumn is null
update myTable set myColumn = null where myColumn = 0
select *, nullif(myColumn, 0) from myTable
select *, isnull(myColumn, 0) from myTable

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-21 : 08:02:39

you could try a nasty,ugly nested case to simulate COALESCE. no idea if it would work.


SELECT actualCost , manualOverRide , originalEstimate 
,case actualcost when 0 then 
(case manualoverride when 0 then originalestimate else manualoverride end)
else actualcost end as Subtotal

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-21 : 08:55:36

Why are you making your solution to this simple problem so complicated with IFs. Just do this...

Update #t
Set ActualCost = 0.00
where actualcost is null

Update #t
Set manualOverRide = Null
where manualOverRide = 0.00

Harsh Athalye
India.
"Nothing is Impossible"

gugarc
Starting Member

17 Posts

Posted - 2006-07-21 : 08:56:45

HI.
First look the ANSI_NULL_DEFAULT setting of your Database.

The statement 'if @VAR = NULL' should be avoided - it only works when the set ansi_null_default is set to OFF!!!

You should allway look null values like @var IS NULL.

Take a look also at the ISNULL function:
ISNULL(Column, 0) (when NULL returns 0, otherwise, returns the column itself) ....

gugarc
Starting Member

17 Posts

Posted - 2006-07-25 : 13:23:41

sorry, not the ansi_null_dflt, but ansi_nulls setting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources