Return a value if a field is null

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 2008 Forums

Transact-SQL (2008)

Return a value if a field is null

Author

Topic

jdibble
Starting Member

4 Posts

Posted - 2011-08-01 : 04:18:11

We are trying to create a small function that returns -999 if one of the fields used in the calculation are null which is very likely as most of our historic data will not have this populated.

So looking at the code, we need the value to return -999 if uf_savedquotecost is null. Atm it will return ~95% if it isn't populated. Here's what we have currently:-

CASE WHEN
(NULLIF(ISNULL((SELECT MAX(i.uf_savedquotecost) FROM coitem i WHERE i.co_num=CoNum AND i.co_line=CoLine AND i.co_release=CoRelease),1),0))
= NULL THEN
-999
ELSE
((((Price / NULLIF(ISNULL((SELECT MAX(h.exch_rate) FROM co h WHERE h.co_num = CoNum),1),0)) -
(NULLIF(ISNULL((SELECT MAX(i.uf_savedquotecost) FROM coitem i WHERE i.co_num=CoNum AND i.co_line=CoLine AND i.co_release=CoRelease),1),0))) /
(Price /
NULLIF(ISNULL((SELECT MAX(h.exch_rate) FROM co h WHERE h.co_num = CoNum),1),0)
)
) * 100).

Can anyone give us a few pointers? Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-01 : 04:37:37

why are you using so many subqueries. its making query really complex and messy.
Why not use variables to hold values instead or use joins if it varies for each row in main query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jdibble
Starting Member

4 Posts

Posted - 2011-08-01 : 05:55:23

Essentially it's becuase I'm very new to this and it works. Could you give some examples on how to make it better?

Also do you have any ideas on the main probelm I described?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-08-01 : 06:08:45

the
= null
should be
is null

Don't know if that is the issue but it's a start.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jdibble
Starting Member

4 Posts

Posted - 2011-08-02 : 04:31:59

Popped in the IS NULL to no avail. I've been playing around with using IF ELSE statements but I'm getting syntax errors.

IF ISNULL(SELECT i.uf_savedquotecost FROM coitem i WHERE i.co_num=CoNum AND i.co_line=CoLine AND i.co_release=CoRelease) THEN -999 ELSE ((((Price / NULLIF(ISNULL((SELECT MAX(h.exch_rate) FROM co h WHERE h.co_num = CoNum),1),0)) - (NULLIF(ISNULL((SELECT MAX(i.uf_savedquotecost) FROM coitem i WHERE i.co_num=CoNum AND i.co_line=CoLine AND i.co_release=CoRelease),1),0))) /(Price / NULLIF(ISNULL((SELECT MAX(h.exch_rate) FROM co h WHERE h.co_num = CoNum),1),0)
)) * 100)

I think I might start from scratch and build this query up.

jdibble
Starting Member

4 Posts

Posted - 2011-08-02 : 05:30:35

After rewritting the code from scratch it now works as expected. For anyone whos interested here's what I ended up with:-

CASE WHEN ((SELECT i.uf_savedquotecost FROM coitem i WHERE i.co_num=CoNum AND i.co_line=CoLine AND i.co_release=CoRelease) IS NULL) THEN '-999' ELSE ((((Price / NULLIF(ISNULL((SELECT MAX(h.exch_rate) FROM co h WHERE h.co_num = CoNum),1),0)) -
(NULLIF(ISNULL((SELECT MAX(i.uf_savedquotecost) FROM coitem i WHERE i.co_num=CoNum AND i.co_line=CoLine AND i.co_release=CoRelease),1),0))) /
(Price /
NULLIF(ISNULL((SELECT MAX(h.exch_rate) FROM co h WHERE h.co_num = CoNum),1),0)
)
) * 100) END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources