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

Transact-SQL (2000)

Null value

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2008-12-05 : 09:28:36

Is it possible for a money field to show 0.0000 in the results grid, but actually be a null? The field does not allow nulls, and I dont see any nulls in my table data, but when I try to run an update query it gives me this error:

Cannot insert the value NULL into column 'LastPayAmount', table 'ctcReceivables.dbo.EntityBalanceTotal'; column does not allow nulls. UPDATE fails.

I dont know if this matters, but this is my update query:
Update ebt
Set LastPayAmount =
(Select LastPayAmount
FROM EntityBalanceTotal ebt2
Where ebt2.EntityID = ebt.EntityID
AND ebt2.ARPeriodID = 37
and ebt2.BalanceTypeID = ebt.BalanceTypeID)
FROM EntityBalanceTotal ebt
JOIN EntityBalanceTotal ebt2
ON (ebt.EntityID = ebt.EntityID
and ebt.BalanceTypeID = ebt2.BalanceTypeID)
Where ebt.ARPeriodID = 49

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-05 : 09:43:00

ISNULL fucntion might be of use. But then, Storing Null as 0.00 might make a huge difference in cases such as if you are averaging etc

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-05 : 09:45:01

I think making 'LastPayAmount' nullable might be the best way to handle this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-05 : 09:50:26

That UPDATE statement is a mess.
Try this

UPDATE		ebt
SET		ebt.LastPayAmount = x.LastPayAmount
FROM		EntityBalanceTotal AS ebt
LEFT JOIN	EntityBalanceTotal AS x ON x.EntityID = ebt.EntityID
			AND x.BalanceTypeID = ebt.BalanceTypeID
			AND x.ARPeriodID = 37
WHERE		ebt.ARPeriodID = 49

E 12°55'05.63"
N 56°04'39.26"

JAdauto
Posting Yak Master

160 Posts

Posted - 2008-12-05 : 09:50:54

quote:
Originally posted by sakets_2000

I think making 'LastPayAmount' nullable might be the best way to handle this.

Well we already have months worth of data in the table. I want to move the lastpayment amount from one record to another. I dont show NULL in my table data anywhere, so I dont understand where it could be reading this null.

Is there a way to turn this nullable thing off temporarily and then back on just to run the update? Or is that an unsafe practice?

Thx

JAdauto
Posting Yak Master

160 Posts

Posted - 2008-12-05 : 09:54:32

quote:

Originally posted by Peso

That UPDATE statement is a mess.
Try this

UPDATE		ebt
SET		ebt.LastPayAmount = x.LastPayAmount
FROM		EntityBalanceTotal AS ebt
LEFT JOIN	EntityBalanceTotal AS x ON x.EntityID = ebt.EntityID
			AND x.BalanceTypeID = ebt.BalanceTypeID
			AND x.ARPeriodID = 37
WHERE		ebt.ARPeriodID = 49

E 12°55'05.63"
N 56°04'39.26"

That does look a lot better :). I researched SQLTeam forum to figure out how to do an update on a table using the same table as the data to update from and what I got evidently did not translate. Anyway, while it appears better, I still get the same error.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-05 : 10:15:57

Yes, same error because there are no values found for ARPeriod 37.

E 12°55'05.63"
N 56°04'39.26"

JAdauto
Posting Yak Master

160 Posts

Posted - 2008-12-05 : 10:20:18

quote:
Originally posted by Peso

Yes, same error because there are no values found for ARPeriod 37.

E 12°55'05.63"
N 56°04'39.26"

There are 1100 records in EBT, and 36 of them are records that have an ARPeriod 37, that also have a matching record for ARPeriod 49 with teh same EntityID and BalanceTypeID. Those 36 I need to update the last payment amount field. All 36 records have a value in LastPayAmount. 2 of the 36 have 0.0000, but it is a value and not Null.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-05 : 10:23:08

For clarification. There are combinations of BalanceTypeID and EntityID found in ARPeriod 49 that is not found in ARPeriod 37.
While they exist in ARPeriod 49, and not in ARPeriod 37, you will get a NULL value in return for the ARPeriod 49 combination.

Run this to verify

SELECT		ebt.LastPayAmount,
		ebt.EntityID,
		ebt.BalanceTypeID,
		ebt.ARPeriodID,
		x.LastPayAmount,
		x.EntityID,
		x.BalanceTypeID,
		x.ARPeriodID
FROM		EntityBalanceTotal AS ebt
FULL JOIN	EntityBalanceTotal AS x ON x.EntityID = ebt.EntityID
			AND x.BalanceTypeID = ebt.BalanceTypeID
			AND x.ARPeriodID = 37
WHERE		ebt.ARPeriodID = 49

If you want only the BalanceTypeID and EntityID combinations in ARPeriod 49 updated, that also exists in ARPeriod 37, change LEFT JOIN to INNER JOIN.

E 12°55'05.63"
N 56°04'39.26"

JAdauto
Posting Yak Master

160 Posts

Posted - 2008-12-05 : 10:36:02

quote:
Originally posted by Peso

For clarification. There are combinations of BalanceTypeID and EntityID found in ARPeriod 49 that is not found in ARPeriod 37.
While they exist in ARPeriod 49, and not in ARPeriod 37, you will get a NULL value in return for the ARPeriod 49 combination.

BalanceTypeID and EntityID combinations in ARPeriod 49 updated, that also exists in ARPeriod 37, change LEFT JOIN to INNER JOIN.

Yes that was exactly the problem. Thank you very much. I understand what you are saying about the left join and inner join and why it was trying to update all of my records, but I am still trying to figure out the first one. How did my original query get to be such a mess and include all of the records that were 49? Was it because of my bottom Where statement:
Where ebt.ARPeriodID = 49
I guess I am trying to pick your brain to how you saw that and said "man, this query needs help"... what was the reg flag so that I can avoid that again.
Thanks so much,
JAdauto

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-05 : 10:42:31

No, it was the correlated subquery that returned NULL because the EntityID and BalanceTypeID did not exist in ARPeriod 37.
And when returning to ARPeriod 49, the UPDATE failed.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-05 : 10:48:35

The regd flag was actually this below

Update	ebt
Set	LastPayAmount = (	Select	LastPayAmount
				FROM	EntityBalanceTotal ebt2
				Where	ebt2.EntityID = ebt.EntityID 
					AND ebt2.ARPeriodID = 37 
					and ebt2.BalanceTypeID = ebt.BalanceTypeID)
FROM	EntityBalanceTotal ebt
JOIN	EntityBalanceTotal ebt2 ON (ebt.EntityID = ebt.EntityID 
		and ebt.BalanceTypeID = ebt2.BalanceTypeID)
Where	ebt.ARPeriodID = 49

There is absolutely no need to self join twice.
So I removed that red part.
Then I looked at the green part and realized this is a self outer join, which in this case was evaluated for every record.
So I changed that to a LEFT JOIN, because of the nature of the correlated query (the self outer join).

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources