Page 1 of 1

[Solved] AddressLine1 AddressLine2 line Field Suppression

Posted: Sun Aug 02, 2015 9:22 am
by Minderbinder
Sorry, but I am very new at this, and I'm not sure where (or where) I would be able to suppress and empty AddressLine2 and also remove the vertical space that had been allotted for it (on a report?). I understand how to, in queries, take out horizontal space between fields, and then use the new "field" in reports I create in Report Builder. Beyond that, I understand very little about shrinking up spaces that are not occupied.
Thanks for any help.

Re: AddressLine1 AddressLine2 line Field Suppression

Posted: Sun Aug 02, 2015 12:23 pm
by Villeroy

Re: AddressLine1 AddressLine2 line Field Suppression

Posted: Sun Aug 02, 2015 12:47 pm
by Minderbinder
Sorry, I should have made it clear that I was creating a report in Base - not a mail merge.

Re: AddressLine1 AddressLine2 line Field Suppression

Posted: Sun Aug 02, 2015 1:23 pm
by Villeroy
Sorry, my bad. I did not even read your posting to the end after reading "AddressLine2 suppression". I don't know much about the report builder.

Re: AddressLine1 AddressLine2 line Field Suppression

Posted: Thu Aug 06, 2015 6:42 pm
by chrisb
Hello Minderbinder,

the area occupied by a text box is permanent & cannot be suppressed.

if your objective is to remove the white space created by an empty address field then this can be achieved by concatenating your data into a single string.

assuming you have the fields 'Name', 'AddressLine1', 'AddressLine2', 'PostCode' & that 'AddressLine2' may contain a null value then the query on which the report is based could contain the code:-

Code: Select all

select
"Name"
|| char(10)
|| "AddressLine1"
|| char(10)
||
case when "AddressLine2" is not null then "AddressLine2" || char(10) else '' end
||
"PostCode"

as "Name&Address"

from "MyTableName"
the case function checks whether or not the field "AddressLine2" contains a value.
if it does then the contents + char(10) (line feed) are appended.
if it does not then an empty string is appended.

your text box must be deep enough to display the maximum number of lines possible (in this example that would be 4).

Re: AddressLine1 AddressLine2 line Field Suppression

Posted: Thu Aug 06, 2015 9:00 pm
by Villeroy
Thank you for pointing out this simple solution.
Alternatively:

Code: Select all

select
"Name" 
|| char(10)
||  "AddressLine1"
|| coalesce(char(10)|| "AddressLine2", '')
|| char(10) 
|| "PostCode" as "Name&Address"
from "MyTableName"
The concatenation of a char with Null gives Null (like any other operation with Null) and coalesce returns the empty string ('' two single quotes) in this case.

Re: AddressLine1 AddressLine2 line Field Suppression

Posted: Fri Aug 07, 2015 7:28 am
by Minderbinder
That definitely improved things - thanks to you both! I'd previously tried something like what chrisb suggested, but I had been using CHR(10) - not CHAR(10); and I had been using the Oracle Report Builder interface (not sql) to build the query. This produced errors. I am trying to interpret Villeroy's char and coalesce remarks (doing online research) to see the advantages, but may have follow-up questions. I am assuming there is not a way to make the report's "text box" conform to three lines when there are only three - again this is in the report builder extension, with which I understand you may not be completely familiar. Again - really appreciate the help!

Re: AddressLine1 AddressLine2 line Field Suppression

Posted: Fri Aug 07, 2015 3:10 pm
by chrisb
Hello Minderbinder,

the suggestion of Villeroy in using the 'coalesce' function is the most appropriate solution.

the sole purpose of 'coalesce' is to eliminate nulls.
if the field contents are valid (not null) then they are selected.
if the field contents are unknown (null) then a predetermined value is selected.

as Villeroy said any operation which involves a null value will result in null.
so the result of char(10) (a known value) when concatenated with an unknown value (null) is null.

with name & address fields (which can vary enormously) i set every field a default value of '' (2 single quotes, empty string).
for labels, envelopes & occasionally display purposes i use the combination of char(10) & concatenation.
every field is tested using 'case when "MyFieldName" > '' then'................. function.
i suspected that you may have null values but posted without thinking it through.

[SOLVED] AddressLine1 AddressLine2 line Field Suppression

Posted: Fri Aug 07, 2015 6:01 pm
by Minderbinder
You have been immensely helpful. Thanks for the guidance.