getting an error around the isnull

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

General SQL Server Forums

New to SQL Server Programming

getting an error around the isnull

Author

Topic

gavakie
Posting Yak Master

221 Posts

Posted - 2007-09-21 : 12:00:53

I have a null in my column IV2.Inventory. So im trying the case statement at the bottom and keeping getting errors. Where am I going Wrong?

Select StoreGroupID, Sum(SnapShotQuantity * SnapShotPrice) as Inventory,
Convert(DateTime, Convert(Char, RecordDate, 101)) as [Date]
Into #ttIV
From Delsol.dbo.ItemSnapshotStore

Where DateDiff(mm,Convert(DateTime, Convert(Char, GetDate(), 101)),Convert(DateTime, Convert(Char, RecordDate, 101))) >= -24
and Day(RecordDate) = 1
and StoreGroupID = 1

Group By Convert(DateTime, Convert(Char, RecordDate, 101)), StoreGroupID
Order By Convert(DateTime, Convert(Char, RecordDate, 101)) desc

Select *, IV1.Inventory-IV2.Inventory/IV2.Inventory as Trend,
Case When IV2.Inventory (isnull(0,IV1.Inventory-IV2.Inventory/IV2.Inventory))
From #ttIV IV1
Left Join #ttIV IV2 on IV1.[Date] = DateAdd(mm,1,IV2.[Date])
--Drop Table #ttIV

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 12:03:49

Case When IV2.Inventory (isnull(0,IV1.Inventory-IV2.Inventory/IV2.Inventory))

There is no THEN, ELSE or END to the Case.

And IsNull is always going to give you 0 with that usage.

Might be best to read up on the parameters that IsNull takes in Books Online

Kristen

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-09-22 : 07:10:04

Do ISNULL(IV1.Inventory-IV2.Inventory/IV2.Inventory),0) which will simply return 0 if that equation is null.
If you want to check if the value is null , Use "IS NULL"

Jack Vamvas
--------------------
Need an IT job ? http://www.ITjobfeed.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-22 : 07:25:36

IV2.Inventory/IV2.Inventory

~~JackV~~ gavakie -- I would hope that you know that the above always returns either 1 or an error (division by zero) .... basic math is a very important skill to have when programming in SQL or any other language.

If you need to avoid divide by zero errors, you use case:

i.e., instead of

a/b

you might write:

case when b = 0 then 0 else a/b end

or

case when b = 0 then NULL else a/b end

EDIT: Thanks Kristen, sorry Jack!

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-09-22 : 07:28:41

"JackV -- I would hope that you know ..."

Although in fairness that was in the OP's code, and probably just got copy&paste

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-22 : 08:25:23

My bad, I meant to address the OP, not JackV -- my mistake. Sorry Jack!

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources