[Solved] Extra spaces in field

Discuss the database features

[Solved] Extra spaces in field

Postby MPEcho » Wed Mar 22, 2017 3:34 am

I'm not sure whether this is a Base question or Writer. It seems to be the former.

I have a table that includes a Yes/No Boolean field "is_dev". There is also a query that does the following:
Code: Select all   Expand viewCollapse view
CASEWHEN( "is_dev", 'devisee', 'heir' ) AS "status"


It seems to work fine. BUT In my writer document, if I insert the value for "status" at the end of a sentence, I get either

" . . . devisee." or ". . . heir ." Note that there are spaces after the shorter word. It appears that the field is a fixed length based on the length of the longest possible result.

Is there a way to modify my query to change this result?

Thanks for any thoughts.
Last edited by MPEcho on Wed Mar 22, 2017 4:51 am, edited 1 time in total.
Libre Office 5.1 Ubuntu 16.04
MPEcho
 
Posts: 92
Joined: Wed Sep 07, 2016 11:30 pm

Re: Extra spaces in field

Postby chrisb » Wed Mar 22, 2017 4:12 am

MPEcho,
your analysis is spot on.
use the trim function to return the desired result.
Code: Select all   Expand viewCollapse view
trim(CASEWHEN( "is_dev", 'devisee', 'heir' )) AS "status"
open office 4.1.6 & LibreOffice 6.0.7.3 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 177
Joined: Mon Jun 07, 2010 4:16 pm

Re: Extra spaces in field

Postby MPEcho » Wed Mar 22, 2017 4:51 am

chrisb wrote:MPEcho,
your analysis is spot on.
use the trim function to return the desired result.
Code: Select all   Expand viewCollapse view
trim(CASEWHEN( "is_dev", 'devisee', 'heir' )) AS "status"


Perfect! That works a charm. I knew there had to be a simple way to get there.
Libre Office 5.1 Ubuntu 16.04
MPEcho
 
Posts: 92
Joined: Wed Sep 07, 2016 11:30 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests