Column specific rules? - 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

General SQL Server Forums

New to SQL Server Programming

Column specific rules?

Author

Topic

hardwood
Starting Member

19 Posts

Posted - 2010-06-08 : 11:44:50

Okay so for this query I'm writing, the same table column is used twice. Once as 'amount', once as 'payment'. The problem is that in the resulting query, I need to add a clause that only positive numbers will be displayed under 'amount' while the rest left blank, and only negative numbers be displayed under 'payment' and the rest left blank.

Is this at all possible? If not I'm okay throwing in the towel, but it would make my day infinitely easier. Here's my current syntax:

Select
h.InvNo as Reference, h.Invoice as Doc, d.PostingDate as Date,
d.Memo as Description, d.Amount as Amount, d.Tax as Tax,
d.Amount as Payment, i.Amount as Balance, d.Deposit

From
ARHeader h
join ARDetail d on h.Voucher = d.Voucher and h.CompCode = d.CompCode
join ARItem i on d.[LineNo] = i.[LineNo] and d.Voucher = i.Voucher

Where
i.CompCode='Will' and h.AID=475568982 and d.TransNo=0

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-08 : 11:50:04

Yes. You can use a CASE statement

Select
h.InvNo as Reference, h.Invoice as Doc, d.PostingDate as Date,
d.Memo as Description, case when d.Amount > 0 then d.Amount else null end as Amount, d.Tax as Tax, 
case when d.amount < 0 then d.Amount else null end as Payment, i.Amount as Balance, d.Deposit
From
ARHeader h
join ARDetail d on h.Voucher = d.Voucher and h.CompCode = d.CompCode
join ARItem i on d.[LineNo] = i.[LineNo] and d.Voucher = i.Voucher
Where
i.CompCode='Will' and h.AID=475568982 and d.TransNo=0

hardwood
Starting Member

19 Posts

Posted - 2010-06-08 : 11:53:50

perfect, thanks so much!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-08 : 12:12:58

Np. You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources