I am back. My fromula failing me.
Here is what records I have
000000030003 will give me 030003 instead of 30003
000000010010 will give me 010010 instead of 10010
000000123456 this is ok 123456
000000234300 this is ok 234300
I am using
if Mid({mytbl.myfield}, 8, 1) = '0' then Right({mytbl.myfield}, 7) else Right({mytbl.myfield}, 6)
Again, the lenght of the string is always 12 characters.
Some have six lead zeros to remove, some seven to remove.
It turns the field into a number, so that all leading zeros are removed. The text function just makes it a string without any decimals or comma dividers.
You'll find that you can probably offload this to the database for optimal performance by using a SQL Expression, as with Oracle you might use:
cast(table.field as number)
The database will perform the conversion, and then you'll have the field already in the appropriate format.
You might also consider exposing that table as a View with the conversion already within, this allowing for future flexibility in case your dba figures out that it can be more economically stored as a value because you can just replace it within the View.
The latest request was is not to include
val({table.field}) records where ({table.other_field}<>'S')
So everything must be redone...
Now I need to have
select * where ({table.other_field}<>'S')
and then insert val({table.field}) on already filtered data.
Should I insert SQL Expression field or go some other way?
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.