len function issue

• • Reply

  15 years ago

  by bogdan

  Bogdan Iliescu

  FL, USA, United States

  Joined 19 years ago

  Hello,
  

  
  I really don't know why, every time I try to execute the following statement:
   
                  select convert(decimal(10,len(AMOUNT)),AMOUNT) from CALIB_ANALYTES
  

  
  I receive an error message: "Line 1: Incorrect syntax near 'len'."
  

  
  If instead of Len(AMOUNT) I put a number, it works fine.
  

  
  Please advise me.
  

  
  Thank you,
  Bogdan

  Report abuse
• • Reply

  15 years ago

  by GeoffAtDatagaard

  Geoff Sutcliffe

  Adelaide, Australia, Australia

  Joined 15 years ago
  I dont know what db you are using but I suspect that you cannot supply an Expression to decimal's precision and scale parameters. I know you cant with Sybase or SQL Server.
  
  
  
  Regards
  
  Geoff
  
  Report abuse
• • Reply

  15 years ago

  by bogdan

  Bogdan Iliescu

  FL, USA, United States

  Joined 19 years ago
  Yes, I'm using SQL Server.
  Are you very sure that I cannot a function instead of a certain value for parameters in decimal? If this is so, how else could I solve this?
  
  Thank you,
  Bogdan
  Report abuse
• • Reply

  15 years ago

  by GeoffAtDatagaard

  Geoff Sutcliffe

  Adelaide, Australia, Australia

  Joined 15 years ago

  
  Yes it is illegal in SQL Server.
  

  
  If you are trying to turn integers into decimals then
  

  
         select AMOUNT / Power( 10 , len(AMOUNT) )
  

  
  will return .12345 where AMOUNT = 12345
  

  
  It really depends on what datatype AMOUNT is and what you are trying to do to it.
  

  
  HTH
  

  
  Geoff
  

  
  

  
  

  
  

  
  

  Report abuse
• • Reply

  15 years ago

  by bogdan

  Bogdan Iliescu

  FL, USA, United States

  Joined 19 years ago

  You're right, I'm sorry.
  

  
  The AMOUNT field is varchar and contains values like 4.0 or 12.20.
       What I'm actually trying is to format another filed(numeric this time, containing a calculated value based on AMOUNT), from another table to have the same number of decimals like this one. I solved that issue using round instead (not a very good solution, but at list accepts expresions as parametes) and a very complicated nested cases.
  

  
  Thanks anyway for your responses
  Bogdan

  Report abuse