When you need to create SQL with secial numeric field size (for example, UDF or just expression field), add constant value like '000.000' to the expression field in query.
Following are samples of such query:
SELECT *, sum(nMyNumber) + 000000.0000 as MySum from ....
MySum field in result will always have N(11,4) despite format of nMyNumber field.
SELECT *, MyFunction() + 000000.00 as MyField from ...
MyField will always have N(9,2) despite format of number returned by function.
Same rules work also for character fields. 'PADR' function is useful here. Unfortunately, no way to convert character fields and expressions into memo for query by such simple way, so all character expressions are limited to 255 characters unless array used as result of query. You may do this, however, using UNION in SELECT-SQL by specifying first SQL statement to query empty cursor that contains pre-formatted fields.
It DOES NOT works for currency or other field types. Use converting functions for other types. It DOES NOT works to cut format of numeric fields. Use this only to enhance format or fix it to the similar length for all records.
Did not investigated speed penalties of this method. (Don't know whether '0' really added to result values taking a time, or just ignored.) For character fields speed reduced because PADR fnction called for each result row.
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.