Re: select mline returns truncated values

padr() didn't work - it forces the columns width, but still truncates data.
One of the quys here came up with this:

SELECT CAST(MLINE(zz,2) as memo) from zoot where selectme

Which returns the data in a memo field, un-truncated. Another alternative:

SELECT CAST(MLINE(zz,2) as char (10)) from zoot where selectme

This requires that you know the max length of the field before you run the
query.


"Dan Freeman" <spam@xxxxxxxxxxxxx> wrote in message
news:%23IJ0VVY2FHA.1576@xxxxxxxxxxxxxxxxxxxxxxx
> Your output columns must be a consistent width. No SQL implementation
> supports variable width columns.
>
> Use PADR() to force your column widths.
>
> Dan
>
> Matthew L Reed wrote:
>> This is an issue I remember from years ago, but don't remember the
>> solution. Do this:
>>
>> CREATE CURSOR zoot (zz m, SelectMe L)
>> INSERT INTO zoot (zz, SelectMe) VALUES ('aa' + CHR(13) + 'bb' +
>> CHR(13) + 'cc', .F.)
>> INSERT INTO zoot (zz, SelectMe) VALUES ('aa' + CHR(13) + 'bbbbb' +
>> CHR(13) + 'cccccccccc', .T. )
>> INSERT INTO zoot (zz, SelectMe) VALUES ('aa' + CHR(13) + 'bbbbb' +
>> CHR(13) + 'cccccccccc', .T. )
>> INSERT INTO zoot (zz, SelectMe) VALUES ('aa' + CHR(13) + 'bbbbb' +
>> CHR(13) + 'cccccccccc', .T. )
>> SELECT MLINE(zz,2) FROM zoot WHERE SelectMe
>>
>> Look at your output. Notice that the data is truncated and instead of
>> 'bbbbb', we only get 'bb'. Look at the structure of your cursor. It
>> is c(2). This is what VFP does - it evaluates the first record in the
>> table and uses that to determine the width of the field in your
>> output cursor, regardless of whether or not this record is actually
>> included in the result set. The filter on SelectMe proves this, as
>> the first record is not included in the output set, yet VFP still
>> evaluates it with the MLINE function from the SELECT statement. In
>> this case, the first record returns 'bb', so the query engine assumes
>> that we want an output structure with c(2). This causes truncation of
>> any data that exceeds c(2) in other fields.
>>
>> I hesitate to call this a bug, as Fox has always done this. It's more
>> of a stupid feature by design.
>>
>> Question - how to work around this?
>
>


.

Relevant Pages

  • Re: select mline returns truncated values
    ... Depends on which version of VFP you're on. ... SELECT CASTas Varchar) FROM zoot WHERE SelectMe ... This is what VFP does - it evaluates the first record in the table ...
    (microsoft.public.fox.programmer.exchange)
  • Re: select mline returns truncated values
    ... > SELECT MLINEFROM zoot WHERE SelectMe ... Look at the structure of your cursor. ... This is what VFP does - it evaluates the first record in the ... > the first record is not included in the output set, ...
    (microsoft.public.fox.programmer.exchange)
  • select mline returns truncated values
    ... CREATE CURSOR zoot ... SELECT MLINEFROM zoot WHERE SelectMe ... The filter on SelectMe proves this, as the first record is not included ...
    (microsoft.public.fox.programmer.exchange)
Loading