Calculated Field Conversion

Epicor ERP 10
1

Is there a way to convert varchar to numeric in the calculated field window?
I’m trying to make some fields in this query so that whenever the TAX MiscCode is shown, it will display the MiscAmt.

This is my calculated field: “(case when ShipMisc.MiscCode = ‘TAX’ then ShipMisc.MiscAmt else ’ ’ end)”

Obviously it gave me this error - "Severity: Error, Table: , Field: , RowID: , Text: Error converting data type varchar to numeric.

Would this even be possible?

2

try
case when ShipMisc.MiscCode = ‘TAX’ then ShipMisc.MiscAmt else 0 end

You want to make sure you then and else are the same datatype.

3

You’re trying to set ShipMisc.MiscAmt to ‘’. Use 0.00 instead:

“(case when ShipMisc.MiscCode = ‘TAX’ then ShipMisc.MiscAmt else '0.00 end)”

4

Caleb,

  1. What is the data type of the calculated field?
  2. is there a reason why you can’t just have 0 instead of ’ '?
  3. if 0 is not ok then use NULL:
    (case when ShipMisc.MiscCode = 'TAX' then ShipMisc.MiscAmt else NULL end)

While it is possible to convert varchar to numeric, it’s extremely complicated… think about it this way:
If I have ‘A’ and I want to display that as a number, what should it be?
Well it would depend on how you would want A to be interpreted numerically… and there are different ways to do that.

5

The reason why I had ’ ’ instead of a 0 was because originally I had - case when ShipMisc.MiscCode = ‘TAX’ then ‘TAX’ else ’ ’ end. I then made a couple different calculated fields to cover the rest of the MiscCodes.

‘0.00’ fixed it.

Thank you all for the help.