SQL select statement results in 3 asterisks on some calc'd records 2

[montypython1]

Hi,

Why do some of my records show 3 asterisks in a calculated field? In fact, only those calcs that results in 4 digits or more seem to display *** (3 asterisks). In a related, but different SQL statement, some of my Text fields will also be tructated. What am I missing?

Thanks,
Dave Higgins

 

[TheSofty]

Dave,

a possible cause could be that in the *first* row of the cursor/table resulting from your SELECT the field has a value represented by 3 digits: all subsequent rows will have 3 as max number of available digits. Solution:

SELECT 00000 as <field>, ... FROM ...

TheSofty

 

[torturedmind]

it could also be that the original field being calculated has a smaller fieldsize than that of the result. that is, if, for example, nfield1 has a fieldsize of 3 and the result of SUM(nfield) in your select statement has a size of 4, then you may encounter such problem.

hope this helps. peace!

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Mike Lewis]

Dave,

I agree with TheSofty's diagnosis, but I'm not sure I agree with the solution.

In general, when there is an expression (as opposed to a simple field name) in the SELECT list, VFP will decide how wide to make the column according to the size of the result in the first row. If the first row contains a number with two digits, the column will only be two digits wide, and subsequent rows will contain truncated values.

So, you need to rewrite your expression so that it always returns the correct number of digits. If you would care to post the SQL code here, no doubt one of us will help you do that.

The same thing is happening in your other query -- the one with the character expression. In the case, the usual solution is to wrap the expression in a PADR() function.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[montypython1]

Hi,

Thank you all for your input. I wrapped the expressions in a PADL (for the numeric calcs) and a PADR (for the character expressions), and it worked.

Many thanks,

Dave Higgins

 

[montypython1]

Hi Mike Lewis,

In response to your question in the CS forum, Yes, I am Don Higgins brother. I'll be the guy asking the simple questions, while Don will be asking the difficult questions .