Select CASE - 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

Select CASE

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-12-18 : 14:58:26

Getting Incorrect Syntax near the keyword 'and'

This table returns multiple records for an Invoice.
Based on the transactiontype_desc the Amount_Paid_DC is a different value. Trying to add up the amounts based on the transactiontype.


select DebtorNumber, InvoiceNumber, Sum(Amount_Invoiced_DC) AS InvAmt,
case transactiontype_desc when 'Sales Invoice' then sum(Amount_Paid_DC) else 0 end as AmtPaid,
case transactiontype_desc when 'Discount/Surcharge' and Amount_Paid_DC < 0 then sum(Amount_Paid_DC) else 0 end as DiscountAmt
FROM            BI50_BankTransactions_AR_InvcDt_H
group by debtornumber, Invoicenumber

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-18 : 15:03:36

You can't combine the short-form of the case that you are using with additional conditions. You can do this:


cast when transactiontype_desc = 'Sales Invoice' then sum(Amount_Paid_DC) ...
...etc.

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-12-18 : 15:06:35

I'm not sure I follow:

This works just fine.

select DebtorNumber, InvoiceNumber, Sum(Amount_Invoiced_DC) AS InvAmt,
case transactiontype_desc when 'Sales Invoice' then sum(Amount_Paid_DC) else 0 end as AmtPaid,
case transactiontype_desc when 'Discount/Surcharge' then sum(Amount_Paid_DC) else 0 end as DiscountAmt
FROM BI50_BankTransactions_AR_InvcDt_H
group by debtornumber, Invoicenumber,transactiontype_desc

I just need the second one to also only look at amount if < 0

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-18 : 15:35:22

Yes, but you cannot add "and Amount_Paid_DC" to "case transactiontype_desc when 'Discount/Surcharge'". You must write it:

case when transactiontype_desc = Discount/Surcharge' and Amount_Paid_DC ...

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-12-18 : 16:03:01

I thought that is what I had in the first script.

case transactiontype_desc when 'Discount/Surcharge' and Amount_Paid_DC < 0 then sum(Amount_Paid_DC) else 0 end as DiscountAmt

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-18 : 16:06:22

[code]
case transactiontype_desc when 'Discount/Surcharge' and Amount_Paid_DC < 0 then sum(Amount_Paid_DC) else 0 end as DiscountAmt
[/code]

<>

[code]
case when transactiontype_desc = 'Discount/Surcharge' and Amount_Paid_DC then sum(Amount_Paid_DC) else 0 end as DiscountAmt
[/code]

Yours begins:

case transactiontype_desc when 'Discount/Surcharge'

Mine begins:

case when transactiontype_desc = 'Discount/Surcharge'

subtle! but different.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-12-22 : 01:47:36

select DebtorNumber, InvoiceNumber, Sum(Amount_Invoiced_DC) AS InvAmt,
sum(case when transactiontype_desc ='Sales Invoice' and Amount_Paid_DC<0 then Amount_Paid_DC else 0 end) as AmtPaid,
sum(case when transactiontype_desc ='Discount/Surcharge' and Amount_Paid_DC<0 then Amount_Paid_DC else 0 end) as DiscountAmt
FROM BI50_BankTransactions_AR_InvcDt_H
group by debtornumber, Invoicenumber

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources