Change data type if VFP 3

[Terabithia]

I have a need to change the data type on specific fields when i open a Visual Foxpro DB in Visual Basic. I am using the following select string format:


select distinct cast(numeric_field as Char(30)),another_field from database_table

This errors out as unrecognized Visual Foxpro, where am I going wrong please?

 

[JRB-Bldr]

So you are not really changing the data type, but instead you are merely attempting to get another version of that field in a query result.

If you want the VFP code that would need to be executed, you would need something like:

SELECT STR(numeric_field, 30), another_field from database_table

NOTE - that unless your VFP table is in a VFP Database, its field names will be limited to 10 characters (consistent with VFP definitions).

Good Luck,
JRB-Bldr

 

[JRB-Bldr]

Some VFP syntax alternatives you might want to consider using might be:

* Trim the resultant string and then pad left to 30 char.
PADL(ALLTRIM(STR(numeric_field)),30)

* Trim the resultant string and then pad right to 30 char.
PADR(ALLTRIM(STR(numeric_field)),30)

Good Luck,
JRB-Bldr

 

[Terabithia]

JRB-Bldr,

Thanks for the tips. You are correct, I am not trying to change the actual data, what I really am trying to do is to load a grid with data, and have it all be character type data in the grid. Perhaps there is a better way to do what I want, I am doing this so a user can use a filter routine I have to filter the data. Even on a numeric field, I need to be able to type in "3" and see all records that start with the character (digit) 3.

Based on what you provided, here is the current select string:

    SELECT DISTINCT STR(gjdtl.gdacct,30),STR(gjdtl.gddesc,30) FROM gjdtl ORDER BY gdacct DESC

It now errors out on the order by clause. Am I going about this wrong, or is there a better way to allow the desired filtering?

TIA

 

[Mike Lewis]

Terabithia,

The reason that your ORDER BY claused failed is that, if you use SELECT DISTINCT, the items in the ORDER BY clause appear in the select list. This is a rule of SQL ANSI-92, and not specific to FoxPro.

There are two possibile solutions:

SELECT DISTINCT STR(gjdtl.gdacct,30) [b]AS Sortkey[/b],STR(gjdtl.gddesc,30) FROM gjdtl ORDER BY [b]SortKey[/b] DESC

or

    SELECT DISTINCT STR(gjdtl.gdacct,30),STR(gjdtl.gddesc,30) FROM gjdtl ORDER BY [b]1[/b] DESC

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Mike Lewis]

Sorry. Typing error in my first sentence above. I meant to say:

The reason that your ORDER BY claused failed is that, if you use SELECT DISTINCT, the items in the ORDER BY clause must appear in the select list.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Terabithia]

Mike,

Thanks for the help, your suggestions on the Order By will be useful. I did have the field used by Order By listed in the Select, just without explicitly listing the table name also. Testing showed it worked either way.

I tracked the problem down to having tried to use the STR function on the second field, gjdtl.gddesc, which was already a character field. Error messages sometimes are not much help.

Here is the code that worked, with a filter:

SELECT DISTINCT PADR(ALLTRIM(STR(gdacct)),30),gddesc FROM gjdtl WHERE PADR(ALLTRIM(STR( gjdtl.gdacct )),30) LIKE '3%' ORDER BY gdacct DESC

 

[Mike Lewis]

I tracked the problem down to having tried to use the STR function on the second field, gjdtl.gddesc, which was already a character field.

Yes, that would cause an error.

A slightly better approach might be to use TRANSFORM() instead of STR(). The advantage is that it will convert any data type to a string. STR() only converts numerics.

So you could do something like this (as per your earlier example):

SELECT DISTINCT ;
 PADL(TRANSFORM(gjdtl.gdacct),30), ;
 PADL(TRANSFORM(gjdtl.gddesc),30)  ;
   FROM gjdtl .... etc.

The PADL() function pads to the left with spaces. This is to right-justify the field.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

Terabithia,
Your original SQL looks good. I guess you didn't supply the real SQL that gave error. You can use cast() to convert types and it is especially useful when converting some times (like a memo, null as a specific data type...).
Be careful with the order by, if you convert to character then order by on it then your sort would be alphabetic sort (unless you also add zero padding). I would do something like this:

Select gdacct,  gddesc ;
  FROM ;
  ( Select Distinct ;
     Cast(gdacct As c(30)) As gdacct,;
     gddesc,;
     gdacct As sortBy ;
 FROM gjdtl ;
 ORDER By sortBy Desc) tmp ;
 WHERE gdacct Like '3%'

Cetin Basoz
MS Foxpro MVP, MCP

 

[Terabithia]

OK,

Things are working well at the moment. I was not able to get the Transform option to work, errored out. I used the exact same syntax as with STR, just replaced STR with TRANSFORM.

Current Select:

SELECT DISTINCT PADR(ALLTRIM(STR(gjdtl.gdacct)),20),gjdtl.gddesc FROM gjdtl ORDER BY gdacct DESC

One more problem to resolve. This Select is used with a connected ADO RS, and I need to search the RS for the first field that matches the search criteria. This worked fine prior to using the field type change options, now it errors on me.

Here is what I am using:

rs.MoveFirst
rs.Find gdacct like '3%'

 

[Mike Lewis]

Terabithia,

You mentioned two errors in your previous post: one with the TRANSFORM() and one with the ADO.

In general, it would help if you could quote the exact error message in such cases. We could have diagnosed your ORDER BY problem more quickly if you had done so earlier.

I apprciate that you have achieved the desired result with PADR(ALLTRIM(STR(gjdtl.gdacct)),20) rather than TRANSFORM(). The reason I suggested TRANSFORM() is that you can use with any data type, not just numerics. Or, rather, that you don't need to know the data type. In that respect, it's more generic. If you are happy with the existing solution, that's fine, but I'm curious to know why the TRANSFORM() gave an error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Terabithia]

Mike,

Sorry if I have not been as through with info as I should have been.

SQL Select that errors using Transform:

SELECT DISTINCT PADR(ALLTRIM(TRANSFORM(gjdtl.gdacct)),20),gjdtl.gddesc FROM gjdtl ORDER BY gdacct DESC

The error message just says "Function is not implemented".

I like the idea of using the Tranform, for the reasons you cited.

The error for the Find is error 3265, "Item cannot be found in the collection corresponding to the requested name or ordinal

 

[Mike Lewis]

Terabithia,

This is interesting. The "item cannot be found .... " message doesn't look like any VFP error message I've ever seen. In fact, the VFP error numbers only go up to about 2000, as far as I know.

Presumably it's either the OLE DB provider or VB that's generating the error message.

"Function not implemented" is a VFP error, but it's hard to see why it came up here.

Do you by any chance have a copy of VFP itself available? If so, the quickest way to debug these problems is to run the command in the VFP command window. That way, you get all the debugging features to help you out, and you can easily experiment with different syntax.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business