INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Tips -N- Tricks

How to pre-format certain field in result of query (either for views or SELECT statement) by TomasDill
Posted: 5 Sep 00

  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.


Back to Microsoft: Visual FoxPro FAQ Index
Back to Microsoft: Visual FoxPro Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close