Exclude where = 0? - 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)

Exclude where = 0?

Author

Topic

brendita
Starting Member

38 Posts

Posted - 2005-08-10 : 13:58:23

Is there anyone in this statment to exclude anything where total = 0?


SELECT  gl.Accountid, 
	gl.AccountName, 
	a.accttype, 
	Parent = a.AcctType, 
	a.chartorder, 
	a.debitincrease, 
	je.branchid, 
	total = CASE 
		WHEN a.debitincrease = 1 THEN je.debit - je.credit 
		WHEN a.debitincrease = 0 THEN je.credit - je.debit END, 
	Acctlevel1, 
	AcctLevel2, 
	AcctLevel3, 
	AcctLevel4, 
	AcctLevel5, 
	AcctLevel6, 
	je.journaldate
FROM         GLJournalEntry je LEFT OUTER JOIN
                      GLAccounts gl ON gl.accountid = je.accountid LEFT OUTER JOIN
                      View_AcctTypesLevels a ON a.AcctType = gl.accounttype
WHERE     a.Acctlevel3 <> 'Current Earnings'

Thanks for your time!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-10 : 14:00:58

[code]

SELECT * --<--use explicit column list
FROM
(
SELECT gl.Accountid,
gl.AccountName,
a.accttype,
Parent = a.AcctType,
a.chartorder,
a.debitincrease,
je.branchid,
total = CASE
WHEN a.debitincrease = 1 THEN je.debit - je.credit
WHEN a.debitincrease = 0 THEN je.credit - je.debit END,
Acctlevel1,
AcctLevel2,
AcctLevel3,
AcctLevel4,
AcctLevel5,
AcctLevel6,
je.journaldate
FROM GLJournalEntry je LEFT OUTER JOIN
GLAccounts gl ON gl.accountid = je.accountid LEFT OUTER JOIN
View_AcctTypesLevels a ON a.AcctType = gl.accounttype
WHERE a.Acctlevel3 <> 'Current Earnings'
) t
WHERE total <> 0

[/code]

Tara

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-10 : 14:02:27

is this a "how do I exclude anything where total=0" or something else???


-- to exlude total=0
...
Where a.Acctlevel3 <> 'Current Earnings'
and 
0 <> (CASE 
		WHEN a.debitincrease = 1 THEN je.debit - je.credit 
		WHEN a.debitincrease = 0 THEN je.credit - je.debit END, 
	Acctlevel1)

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-10 : 14:10:26

Looks like anytime je.debit = je.credit, the total will be 0. So if you're never getting 0s in your results and you want to find specific rows where the total should be 0, search for rows where credit = debit. if you want to exclude the 0s then either of the above ways will work as will: "where debit <> credit"

Be One with the Optimizer
TG

brendita
Starting Member

38 Posts

Posted - 2005-08-10 : 14:22:34

WOW, that is perfect! That was an easy fix.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-10 : 15:17:01

sheesh... i feel dumb

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-10 : 16:11:48

That's OK, go have a margarita

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources