BAQ CAlc Field Error

Epicor ERP 10
,
1

I am trying to use this for a calc field and i keep getting an error.

Case when InvcHead.FiscalYear = Year(dateadd(year, -1, GetDate())) then sum((InvcHead.DocDepositCredit+InvcHead.DocInvoiceAmt)) else ‘’ end

Ice.Common.EpicorServerException: Error converting data type nvarchar to numeric. —> System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric.

2

What are you trying to achieve, it looks like invoices from last year?

try this

Case when InvcHead.FiscalYear = Constants.Year -1 then InvcHead.DocDepositCredit+InvcHead.DocInvoiceAmt else 0 end

1 Like
3

Have you tried . . . else 0 ?

DaveO

1 Like
4

I’m needing to sum all the invoice amounts for particular fiscal years, but it needs to be like a rolling five fiscal years. So can’t hard code it needed to be where it’s year -1, year - 2, etc.

5

@AndyGHA is right. You need a numeric field, and numeric fields can’t be an empty string. So you need a 0 in the else.

1 Like
6

Also, I think you need to move your aggregate function around your case statement.

So instead of this,

Case when InvcHead.FiscalYear = Year(dateadd(year, -1, GetDate())) then
sum((InvcHead.DocDepositCredit+InvcHead.DocInvoiceAmt)) 
else ‘’ end

You should do this.

sum(
when InvcHead.FiscalYear = Year(dateadd(year, -1, GetDate())) then 
(InvcHead.DocDepositCredit+InvcHead.DocInvoiceAmt)
else 0
end
)

I don’t think you can aggregate inside of the case statement like that.

1 Like
7 
CASE WHEN InvcHead.FiscalYear = YEAR(DATEADD(YEAR, -1, GETDATE()))
     THEN SUM(InvcHead.DocDepositCredit + InvcHead.DocInvoiceAmt)
     ELSE NULL
END
8

@aarong , you can’t shove nulls into number fields (int or decimal)

9

He didnt say it was a int field :face_with_raised_eyebrow:

10

Thank you all! I ended up using @AndyGHA and @DaveOlender to get it to work. However, I was very intrigued by @Banderson thoughts on it. Playing with that one for future work.