0

I am combining 6 different fields into 1 large lookup field. 4 of them are varchar, 1 is a double and 1 is a decimal(10,2). I am having an issue with the decimal field. Sometimes the second digit of the decimal does not appear. Here is the code for the lookup field:

LookupField = a.Supplier + a.OrderNumber + a.OrderType + CAST(a.Line AS VARCHAR) + ReceiptDoc + CAST(a.AmountReceived AS VARCHAR)

Prior to this, I cast the decimal field as a float. The reason for that is because some of the values are even, for example, 100 and that is how the end user wants it displayed. As a decimal field, it displays as 100.00. I notice that when I do not cast it as a float, the lookup field displays correctly, but the amount received field still has the .00 displayed.

AmountReceived = CAST(j.[Amount Rcvd] AS float)

How can I resolve the issue with the decimals? I have tried using STR and CONVERT, but ultimately the same issue occurs.

tsqln00b
  • 355
  • 1
  • 4
  • 20
  • 1
    so... you want to lose precision? why cast to a float? adding decimals/numbers to a character field will automatically cast the number/decimal to varchar though implicit conversion. The engine knows it can't store text in number; but it knows it can store numbers as text. I'd probably just use `concat(a.supplier, a.ordernumber, a.ordertype, a.line, receiptDoc, a.amountreceived)` But ***the real question is WHY!?!?*** this seems like a bad thing to do. better would be to look up/match on each field individually! – xQbert Apr 25 '17 at 14:36
  • I do not want to lose precision per se. The end user does not wish to see a value of 100 displayed as 100.00. The field is used in Excel to do some vlookups and the field definition has to match the actual values. Because that field is a decimal, it displays as 100.00. That is why I cast to a float. It removes the .00 but keeps the .99. However, for some strange reason, sometimes the last decimal place drops off from the lookup field. It will only show .6 when it should be .61. – tsqln00b Apr 25 '17 at 14:39
  • that's what float does it loses precision like that. instead of casting to float define a format/mask which will display decimals when present and not when 00. I'll see if I can find it. – xQbert Apr 25 '17 at 14:41
  • Using the CONCAT function produces the same undesired result. – tsqln00b Apr 25 '17 at 14:41
  • http://stackoverflow.com/questions/2938296/remove-trailing-zeros-from-decimal-in-sql-server shows how to remove the zeros (look at 3rd answer from robert4 – xQbert Apr 25 '17 at 14:42
  • To remove only the `.00` portion (but not, for example, the trailing zero on 100.10), use `REPLACE(CAST(j.[Amount Rcvd] as VARCHAR(13)), '.00', '')`. – Laughing Vergil Apr 25 '17 at 21:36

0 Answers0