Functions within Formula property

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

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

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.

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.

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 />

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

returns zero records.

and how about

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

Bambola.

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

zero records.

And

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

Bambola.

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.

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.

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

Thank you very much.