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.
| Author |
Topic |
|
cblatchford
Starting Member
40 Posts |
 Posted - 2006-03-16 : 06:22:47
|
| Hi ladies and gents,I want to be able to set a default value for a set of fields if no results are returned; this is for an accountancy credits/debits application.I have two queries, one which picks up credits dependant on a set of posting codes, and the other which picks up debits (again depending on posting codes). In one case the credit query picks up a value for a client, but the debit query does not pick up any debits. When the calculation tries to run (credit - debit) it does not return a value, I'm assuming because there is no entity to debit.So, what I'd like to do is if the debit query does not return a result, I want to default certain fields to a value of 0.ThanksBlatcho |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-16 : 06:42:57
|
| Make use of IsNull function(Credit-Isnull(debit,0))and(Debit-Isnull(Credit,0))MadhivananFailing to plan is Planning to fail |
 |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2006-03-16 : 06:45:31
|
This is why you need outer joins - Like LEFT OUTER JOIN and FULL OUTER JOIN. Using OUTER JOINS you can return records that exist in one table and not in another (The ones that don't exist in one of the tables will have a NULL Value) you can then Handle the NULL value using the COALESCE function or the ISNULL function ie replace the NULL VALUE with 0.LOOK UP LEFT OUTER JOIN, FULL OUTER JOIN COALESCE and ISNULL in Books OnLineDuane.  |
|
|
|
cblatchford
Starting Member
40 Posts |
Posted - 2006-03-16 : 07:04:45
|
| cheers guys, credit to both of you for this one!blatcho |
|
|
|
|
|
|
Default Values