Unexplained truncation with str function when used in a query 2

[daveb77]

I'm using the VFP 6.0 command line to interrogate some free tables.

There is a field called MON defined as a numeric (width 4, decimal 1) which I've been attempting to export to Access.
There are issues with a straightforward pull via the VFP OBDC driver so I've been attempting to get around it by casting the field using the str function. When I run the following query from the command window:

select mon, str(mon,4,1) as mon1 from employeetime where mon = 12.0
VFP returns "1" in the query results for all mon1 fields.

for
select mon, str(mon,5,1) as mon1 from employeetime where mon = 12.0
VFP returns "" in the query results for all mon1 fields.

When I use ? in the command line like
? str(12.0,4,1)
VFP returns "12.0" (Which is what I would expect)

Why isn't VFP returning consistent results? 4 as a width should be plenty as "12.0" contains 4 characters. 5 as a width should return " 12.0" and it does with the ? but not in the query. Thanks in advance for any help

 

[JRB-Bldr]

Lets start with the code you posted....

select mon, str(mon,4,1) as mon1 from employeetime where mon = 12.0

This doesn't make much sense since all the values for mon will be 12.0 and mon1 will be '12.0' or else no records will be returned if nothing matches mon = 12.0

So I am guessing that your real query string is something else.

Begin by trying something more simplistic:

SELECT mon FROM employeetime WHERE <[u]real criteria needed[/u]>

After doing so, what is the mon value for the very first record returned?

Generally the width of the result columns from a SQL Query are established by the first record value encountered - in part regardless of the expression used.

If you find this occurring, you may have to 'artificially pad out' the resultant field width to overcome this issue and then, if needed or desired, change its width at a later time.

I've been attempting to export to Access.
BTW...
Are you doing this in VFP and then trying to 'export' it to Access as your statement implies?
Or are you doing this in Access and attempting to acquire the data through the VFP ODBC?

Good Luck,
JRB-Bldr

 

[TamarGranor]

Try using the PADL() or PADR() function rather than STR().

Tamar

 

[daveb77]

@jrbbldr
Thanks for your reply
<So I am guessing that your real query string is something else.>
Yes, this was a test query to isolate the problem.

<This doesn't make much sense since all the values for mon will be 12.0 and mon1 will be '12.0' or else no records will be returned if nothing matches mon = 12.0>

The query (select mon, str(mon,4,1) as mon1 from employeetime where mon = 12.0 ) returns 12 rows. The value for every mon column is 12.0. The value for every mon1 column is a left aligned "1" without the quotes.
I agree with you that it should be "12.0", but it's not! I'd really
like to know how this can occur.

<Begin by trying something more simplistic:>
<SELECT mon FROM employeetime WHERE <real criteria needed>
Since I need the whole table I tried:

select mon from employeetime where 1=1

The mon value for the first row is .Null. It's selecting from a numeric field with 1 decimal place.

<Generally the width of the result columns from a SQL Query are established by the first record value encountered - in part regardless of the expression used.>

I don't know why this would determine the length of the query result of a numeric field
field but I would be interested in an explanation.

The real task is to get the entire table into Access (which I use to stage into SQL Server) without losing or modifying any data.

* A straightforward link or import through the MS Visual FoxPro ODBC driver yields the following error whenever
there is a numeric field with at least one decimal place in the FoxPro source table:
"The decimal fields precision is to small to accept the numeric you attempted to add"
Googling this error with foxpro returned nothing on point.

Using the Access import utility you get no opportunity to change the datatype of the target fields.


I'm attempting to get around the problem by "casting" the decimal field to a character field using str()
Casting is in scare quotes because VFP 6 doesn't have a CAST() function.

* A text export eg COPY TO "c:\data\export\EmployeeTime.txt" DELIMITED WITH '"' WITH CHAR TAB
does succesfully export a text version of the decimal fields. However, it doesn't work with
memo or general fields.

My solution now will be to create 1 text export file and 1 table for each table with problem fields in it. The text export
is the primary key and the problem numeric fields. The table is the primary key plus everything but the problem numeric fields. I'll join 'em up in Access. Seems messy for what should be straightforward but it is what it is.

 

[daveb77]

@TamorGranor

Thanks for your reply. I tried PADL, PADR and even PADC to no avail. I used the equal sign as my pad character and the query returned a left aligned = sign for the column interrogated.

 

[Olaf Doschke]

You have to understsand one thing about queries: In a first step VFP determines which field type it needs for expressions like STR(field,4,1) and while that seems straight forward to create a C(4), vfp may fail on a null value and create a C(1) field instead. See the AFIELDs of the result cursor.

If you want to make sure you get a result field with the wanted lengthz of 4, use CAST(STR(mon,4,1) as C(4)) as mon1
Also, make sure NULLs are turned to 0, perhaps: CAST(STR(NVL(mon,0),4,1) as C(4)) as mon1

What is the type of the field mon? Integer? Numeric?

PADL() has one advantage: It takes strings and numbers and pads them as strings, to the fixed desired length. Before we had CAST it was the only way to specify field lengths of query results of expressions via this in char fields.

Also, what version of VFP are you using? VFP7 without SP had a bug with Indexes on STR(field) at least.

Bye, Olaf.

 

[Mike Lewis]

Dave, normally I would agree with Olaf and say that you should use CAST() in this context. But I see that you are using VFP 6.0, in which CAST() is not available.

A good alternative would be TRANSFORM():

SELECT TRANSFORM(Mon, "99.9") .....

However, this does not properly deal with NULLs. If Mon is NULL, the above expression will return the word ".NULL." as a character string. If that's an issue, you could do this:

SELECT TRANSFORM(NVL(Mon, 0), "99.9") .....

which would return NULLs as zero.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

For testing purposes, I created a data table of 4 records

CREATE TABLE employeetime FREE (mon N(4,1))
SELECT employeetime
FOR Cntr = 1 TO 4
   APPE BLANK
   REPL Mon WITH 12.0
ENDFOR

I then ran your precise query against it:

select mon, str(mon,4,1) as mon1 from employeetime where mon = 12.0

I got precisely what you said you were expecting - no truncated mon1 values.

Since that worked just fine under the limited conditions, it appears as though our problem has to do with the value found in the first record of your REAL query.

If you had run the simplistic test I recommended

SELECT mon FROM employeetime WHERE <[u]REAL[/u] criteria needed>

you should be able to see what that specific value is and then be better informed as to how to address it so that the entire resultant record set is how you want it.

Maybe you need to do as Olaf, Mike, or Tamar have suggested above, or perhaps you will need to do something else.

Good Luck,
JRB-Bldr

 

[daveb77]

Thanks to Olaf & JRB-Bldr, and the others that replied.

@Olaf
This is exactly what happened:
<In a first step VFP determines which field type it needs for expressions like STR(field,4,1) and while that seems straight forward to create a C(4), vfp may fail on a null value and create a C(1) field instead>

@JRB-Bldr
It seems that VFP is looking at the mon column for the first record in the table, regardless of whether the record
is being returned by the query. In the table under discussion, there are columns for every day of the week. I had the same issue for every day except for Wed, and that column happens to have a non-null value for the first record. So it is null that is causing the problem and this expression:

str(nvl(mon,0.0),4,1)

that is a workaround.

I have to say that it is unexpected, bad behavior to not to be able to pass null to a function being used in a database context without the possibility of null being returned. After all if the starting value is unknown then the return value should be equally so. The creation of a C(1) field instead seems like a non-sequitur. Although Microsoft documents that it's supposed to happen that way for most functions:
"

http://support.microsoft.com/kb/130307"

I guess that str() is one of those functions for which this rule doesn't apply.

 

[Olaf Doschke]

Well, STR(.NULL.,4,1) results in NULL.

Still the database engine needs to declare some field type for the result. There is no type that applies to NULL, as any field type may be NULL and not all records may have .NULL. in that field, so what do you expect, a field type X?

In this case VFP could deduct C(4) from STR(...,4,.) alone, no need to investigate further, but the intelligence of the sql engine ends somewhere. C(1) surely is not ideal, but in worse case I've also seen C(0) fields, in conjunction with empty Varchar(MAX) returned by SQLExec().

The overall rule of thumb is make expressions type safe (which means making use of NVL in case of VFP6, CAST in VFP8+) and in case of char fields let them have a fixed length by PAD or the parameters of TRANSFORM() or STR().

Even if all your fields are not nullable, an SQL result can have NULLs, if you lef/right join two or more tables. You have to take that into account, too, so NVL() is your friend anyway.

Bye, Olaf.



Bye, Olaf.