Help - 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)

Help

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-06-02 : 07:33:34

Kevin writes "Whats wrong with this query

SELECT contacts.LeaveEntitled+contacts.BroughtFwd-(SELECT Sum(leave.NoOfDays) From leave WHERE ID=44)
FROM contacts
WHERE contacts.ID=44;"

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-02 : 08:40:56

You don't mention if the query returns a syntax error or an invalid result.

The query looks ok to me, assuming there's one row in contacts for ID=44 and that both LeaveEntitled and BroughtFwd are NOT NULL. I don't see any allowances for NULL in any cases.

Here is one thing I'd change:

SELECT contacts.LeaveEntitled+contacts.BroughtFwd-(SELECT Sum(leave.NoOfDays) From leave WHERE ID=44) AS TotalLeave
FROM contacts
WHERE contacts.ID=44

Then, to allow for NULLs so the result returned won't be NULL...

SELECT IsNull(contacts.LeaveEntitled, 0) + IsNull(contacts.BroughtFwd, 0) - IsNull((SELECT Sum(leave.NoOfDays) From leave WHERE ID=44), 0) AS TotalLeave
FROM contacts
WHERE contacts.ID=44

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources