SQL Server Performance

Functions within Formula property

Discussion in 'General Developer Questions' started by pbrewer, Aug 20, 2003.

  1. pbrewer New Member

    I'm trying to use multiple functions within a formula property for a field and I'm receiving a message saying that there is an error validating the formula.

    The new column name is FromNum and the formula is Cast(Substring(CallRecord, 15, 10) As Decimal(10, 0)). CallRecord is a char(210), and the values in bytes 15 - 24 are numbers. Is there a restriction on the number of functions within the formula property?

    Thank you for your help.

    Pat
  2. gaurav_bindlish New Member

    There is no such restriction. Can you post some sample data for the CallRecord field and also the complete SQL statemt that you are trying to execute.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. bambola New Member

    As fas as I can see, this formula gives an error when string has less then 15 characters.

    this returns an error
    declare @CallRecord char(210)
    select @CallRecord = '12347890123456'
    select Cast(Substring(@CallRecord, 15, 10) As Decimal(10, 0))

    this doesn't. it returns 7
    declare @CallRecord char(210)
    select @CallRecord = '123478901234567'
    select Cast(Substring(@CallRecord, 15, 10) As Decimal(10, 0))

    Bambola.
  4. pbrewer New Member

    I'm not trying to execute a SQL statement, I'm adding a new field in a table, and instead of setting the data type, I'm using a formula. When I exit the formula property, I get the error.
  5. bambola New Member

    I understood, I was just tyring to explain what I mean <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />What I am saying is that it is possible that there are rows that has less than 15 chars in field CallRecord.<br /><br />run this:<br /><br />SELECT count(*)<br />FROM your_table_name<br />WHERE LEN(CallRecord) &lt; 15<br /><br />Does it return a number bigger tahn 0?<br /><br />Bambola. <br />
  6. pbrewer New Member

    SELECT count(*)
    FROM your_table_name
    WHERE LEN(CallRecord) < 15

    returns zero records.
  7. bambola New Member

    and how about

    SELECT count(*)
    FROM your_table_name
    where ISNUMERIC(Substring(@CallRecord, 15, 10)) = 0

    Bambola.
  8. pbrewer New Member

    SELECT count(*)
    FROM your_table_name
    where ISNUMERIC(Substring(@CallRecord, 15, 10)) = 0

    zero records.
  9. bambola New Member

    And

    select Cast(Substring(CallRecord, 15, 10) As Decimal(10, 0))
    FROM your_table_name

    Bambola.
  10. pbrewer New Member

    Gives me the selection I want, but when I apply that to the formula property I get an error. Also, if I put in just Substring(CallRecord, 15, 10) in the formula property it works just fine, but I want it to be a decimal. I would love it to be an Integer, but that would cause an overflow.
  11. bambola New Member

    Are you trying to modify it from EM? if so try to do it from QA.
    And btw, a biging will not cause an overflow.

    select Cast(Substring(@CallRecord, 15, 10) As bigint)

    Bambola.
  12. pbrewer New Member

    Yes, it works in QA, but not in EM.

    Thank you very much.

Share This Page