Re: select mline returns truncated values

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

  • 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)
  • Re: select mline returns truncated values
    ... padrdidn't work - it forces the columns width, but still truncates data. ... SELECT CASTas char ) from zoot where selectme ... This is what VFP does - it evaluates the first record in the ...
    (microsoft.public.fox.programmer.exchange)
  • Re: SP Question
    ... less overhead than a cursor. ... > Hi, Hari ... >> First record you can retrive using TOP clause in select statement. ... You need to use cursor with FETCH LAST. ...
    (microsoft.public.sqlserver.server)
  • Re: Search and Replace gives GPF error
    ... I can only get it to work if the cursor is on the first record of parent ... Both as TableFrames with 5-9 rows showing. ... operation Customer is only showing one row. ...
    (comp.databases.paradox)
  • Trying to avoid using a cursor....
    ... it without resorting to a cursor. ... to run an procedure that will take the value of that first record in column ... move through the records one by one, but I've read often in this forum that ...
    (microsoft.public.sqlserver.programming)