Excluding Preceding spaces in ACE Report 1

[ufobaby]

hi,

if i have a field (text) and the value in the field is sometimes with preceding spaces ie.

" niraj" and in my format section i want to just get "niraj"

is it possible how... ??? the space may vary so can't take field[1,5] , sometimes the spaces won't be there.

is there a function like ltrim() or trim() ???? i guess not

pls help, am really stuck on this and can't find a solution anywhere.... !!!!


Niraj

 

[vpshriyan]

Hi Niraj,

You can use TRIM function, to remove all the preceding and/or succeeding blank spaces at SQL level or the following 4GL code imitates the same functionality.

function alltrim(in_string)
define i,len smallint, in_string varchar(255)

let len = length(in_string) clipped
for i=1 to len
if in_string[i,i] != " " then
exit for
end if
end for
return in_string[i,len]
end function

Regards,
Shriyan

 

[ufobaby]

Thanks Shriyan,

but we do not have access to the back end directly, i mean can't write functions in 4GL. we only access the ACE report module.
So i need , some thing which works in the ACE report, w/o the need of writing any 4GL code.

currently i use a for loop - on every row to eliminate spaces, but i guess this is not a very effective way to do it.

any ideas !!!


Niraj

 

[olded]

Niraj:

If you have at least 7.1x of Online, the trim built-in function of the SELECT statement removes leading or trailing characters in a give column. It works like this:

SELECT TRIM(LEADING '0' FROM buffer) INTO newbuffer FROM systables WHERE tabname = "systables";

Now, I don't know if this works in an ACE report.

If that doesn't work, can you call stored procedures with a select in ACE?

If so, I can give you a stored procedure I wrote. It's just a wrapper around the above select.

Regards,

Ed

 

[ufobaby]

hey man, dammm cool it worked like magic !!!!

Thanks a ton .....
i used it like this ->

select trim(leading ' ' from cltref)
from tabname
where
condi....

works well with ACE reports also
just one more favour, where can i get a exhaustive listing of these functions , say a PDF file or some thing... can u pls give the stie link
wud be really helpful...

Thanks once again...


Niraj

 

[olded]

Niraj:

Thanks for the star! In answer to your question:

I'm certain these functions are somewhere in the Informix documentation, but the most complete list, and explanation I've seen is in the Informix Handbook by Ron Flannery:

Algegraic functions:
ABS absolute vale
MOD modulus remainder value
POW returns value raised to an exponent
ROOT returns the root vlaue of the given column
SQRT returns the square root of the given column
ROUND returns the rounded value
TRUNC returns the truncated value

Statistical functions:
RANGE returns the difference between the maximum and min values of the given rows
VARIANCE returns the simple variance of the given rows
STDEV returns the standard deviation

Built-in exponent & log functions:
EXP returns the exponential value
LOGN returns the natural log value
LOG10 returns the log to the base 10

Trig functions
COS, SIN, and TAN as well as the ARC functions - ACOS, ASIN, ATAN.

Other functions:
USER returns the name of the current user
HEX returns the hexadecimal value of the given column
LENGTH returns the length of the given column
TRIM already covered

built-in functions for IDS
BSERVERNAME or SITENAME returns the name of your database server
DBINFO("Special_keyword&quot returns the value of the given keyword.
for example:
DBINFO("sqlca.sqlerrd1&quot returns the last serial value on an insert and
DBINFO("sqlca.sqlerrd2&quot returns the number of rows processed by the last DML statement

Flannery also gives examples of using all of these. I've seen this book for sale at amazon.com.

Regards,

Ed

 

[ufobaby]

hey , u deserved it
i actually have the ref doc for 6.0 and want it for 9.X as we using Informix 9.1

thanks for the functions, wll try and search for the new ref doc. if u got some PDF or some thing , or links wud be of gr8 help.

thanks....


Niraj

 

[olded]

Niraj:

I don't know if you're aware or not, but IBM provides a lot of Informix documtation in PDJ format at:

http://www-3.ibm.com/software/data/informix/pubs/library/

Regards,

Ed