select statement trims results

[Akourou]

hello

i have the following select statement:

SELECT bdate, platforma.descr, CDOW(checkin)+" "+DMY(checkin), ALLTRIM(STRTRAN(strtran(booking.notes,CHR(13)," "),CHR(10)," ")), guest FROM booking .......

I noticed that when the field contains an expression like
ALLTRIM(STRTRAN(strtran(booking.notes,CHR(13)," "),CHR(10)," "))
or CDOW(checkin)+" "+DMY(checkin)

the results going into a cursor are trimmed.


I remember I have found once a thread regarding this issue but I cannot find it anymore.

Does anybody know about this problem?

thank you

 

[Chris Miller]

The length of the first result record will determine the width of the result field, so don't ever use ALLTRIM on an expression leading to a field or you likely end up with too short fields.
The rule to remember is: You are responsible for the result cursor or table structure regarding computed fields by using expressions resulting in a fixed field definitions.

Besides that you can do what you want to do with the double STRTRAN in a single CHRTRAN and you should just cast it as a memo field so there is no problem with longer booking notes:

CAST(CHRTRAN(notes,chr(13)+chr(10),'  ') as M) as notes_in_one_line

and use padding for the other one, as both CDOW() and DMY() have different lengths, give the whole expression 40 chars, for example:

PADR(CDOW(checkin)+' '+DMY(checking),40) as checkindate

or also go for CAST:

CAST(CDOW(checkin)+' '+DMY(checking) as C(40)) as checkindate

By the way, TRANSFORM(checkin,'@YL') produces a string like 'Monday, April 11th 2022':

CAST(TRANSFORM(checkin,'@YL') as C(40)) as checkindate

Pick what works best for you or think of even other expressions, but keep in mind: You are responsible for a constant width of expressions that are used to compute fields in a query.

Chriss

 

[Mike Lewis]

As Chris says, the reason you are seeing this is that, in order to decide the width of a field in the result cursor, VFP takes the width of the actual data in the first row of the cursor. So if the result set looks like this:

[tt]ABC
DEFGH
IJ
KLMNOPQ[/tt]

then the field in the cursor will be only three characters wide, because that's its width in the first row.

The way I usually deal with this is place either PADL() or PADR() around the expression (in the SELECT); For example:

[tt]SELECT .... , PADL(CDOW(checkin)+" "+DMY(checkin), 20) ... FROM etc.[/tt]

which will give a field width of 20 characters.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

The other lesson to learn that always comes to me is formatting should be done only for output, either on screen or for printing.

Both controlsource of form controls and report controls can store expressions, not only single fields. So there almost never is a point in computing fields in a query for a form or report control to display or print, just keep the raw data until you actually need the expressions for formatting the output. And that should be done exclusively at that stage only.

That also means you don't need PADR/PADL or CAST for that context, as the expressions used for output are not executed to define a field, just to have the output value. Width or length can vary there.

Chriss

 

[Akourou]

thank you all for the solutions. it worked fine.

I do the formatting during the select query because I present this info in a grid afterwards.

 

[Chris Miller]

Well, as said, controls can display expressions.

You can set a grid column.controlsource to (TRANSFORM(checkin,'@YL')), for example. So all the query needs to deliver is checkin for that column.
If you do it that way around, first only pick out the fields you need in expressions, then use expressions in controlsources, you don't need to worry about padding or casting types.

Chriss

 

[Chris Miller]

And another way for dates specifically is the textbox.DateFormat property. You're in control how a date is displayed on a control, aside of the control you have with SET DATE settings, SET DATE AMERICAN and SET DATE LONG, for example.

The time you invest in your query is better invested in how you handle data at that very last stage from a DBF or cursor to a form or report control.

Also: did you ever wonder why the table designer offers a Display section where you can specify a Format, for example?

Chriss

 

[Akourou]

thank you Chris

very optimized approach