SELECT STATEMENT? 1

[rtatum]

We are trying to use the following line, okay well while debugging we took it down to the following line, in hopes of solving the problem, but thus far havent.

SELECT ATC(' ',misc.desc),LEFT(misc.desc,ATC(' ',misc.desc)) FROM misc

The first ATC() returns the correct Position of the space from the field (i.e. 4,5,7,3,2,9,etc.); however, the LEFT() with the ATC() always returns four charcters and not the Position that corresponds with the field.

Is this by default, a bug, or are we doing something wrong?

TIA,

Jason and Randal

 

[jimstarr]

Hi Jason and Randal,

The output from a SELECT is a table/cursor with columns of fixed width. So each column will be as wide as your first selected result.

Jim

 

[ramani]

Hi Jason and Randal,

SQL will always return the size of field based on the first record selected. In this case, the first record slected on LEFT(misc.desc,ATC(' ',misc.desc)) happens to be 4, the SQL always returns the first 4 characters.

To avoid this.. use the following code..

SELECT ATC(' ',misc.desc), ;
PADR(LEFT(misc.desc,ATC(' ',misc.desc)), ;
LEN(misc.desc)," &quot FROM misc





____________________________________________
Ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[rtatum]

Jim,

Thanks for your answer, we were afraid that it was something like that.

Thanks

J and R

 

[Stella740pl]

Jason and Randal,
I tried, it works correctly for me (VFP6), returns just the first word of the character field, no matter how long (or short it is). What version are you running? Is this SELECT a part of a UNIONized block of SELECT statements? How does the whole thing look? Are there any additional circumstances?
Stella.

 

[rtatum]

Stella40pl,

We are using VFP 8.0 and it never returned the correct value???? We broke it down to its simplest form and still could not get it to work. Any ideas?

Ramani,

Your suggestion worked! Thank you very very much!

J and R.

 

[Stella740pl]

Well, your list of the values returned (4,5,7,3,2,9,etc.) starting with 4 should have given me a clue right away. A file that I tested your statement on had a very long first word, so of course it worked well for all the following, shorter words. I thought at first, though, that your statement is a part of a UNION, and not the first part. In this case, too, the length of the field precreated by a first statement would stay the same for all the following statements; and just forgot that the first record's length would be enough to set the structure. So you need to pad the result, as Ramani showed you.
Stella.