len function issue

  • 12 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

  • 12 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
  • 12 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
  • 12 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





  • 12 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

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Walking on water and developing software from a specification are easy if both are frozen.” - Edward V Berard