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.
Go to Top of Page

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
Go to Top of Page

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 ...
Go to Top of Page

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
Go to Top of Page

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.

Go to Top of Page

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
Go to Top of Page
   

- Advertisement -