Focal Point
[SOLVED]Concatenating alpha fields where some fields are missing

This topic can be found at:

October 13, 2016, 10:54 AM
[SOLVED]Concatenating alpha fields where some fields are missing
I have fields for each day of the week and I want to concatenate these fields into one field. I tried in the metadata to create a define with the following syntax

The Results return a blank field when the SSRMEET_SUN_DAY field is missing, which is almost always. Each field is a A1V type and the MEETDAYS field is an A7V field. When I change the concatenation to the following


I get results when there is a value in the two fields but no values when there is only a value in one of the fields. How do I code this to ignore those fields that are missing?

Thanks Trudy

This message has been edited. Last edited by: Trudy,

October 13, 2016, 11:15 AM


The single pipe "|" keeps spaces where the double one "||" remove trailing spaces from left field.
Meaning remove trailing spaces from field 1 before concatenating field 2. But leading spaces from field 2 will be kept.

In your case it should work because trailing spaces from field 2 (in it's case it's only spaces) will be removed because it's followed by "||" to concatenate with a field 3.

WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
October 13, 2016, 11:34 AM
Martin, thanks for this and yes it does work. I guess I was trying to do this in the metadata so that when I use this table it will always be in the correct format. I cannot seem to get this to work in the metadata but can by using a compute or define in a report. Which I guess I'll have to do for every report that uses these fields.

October 13, 2016, 11:38 AM
I figured it out. You need to change the MISSING property in the DEFINE field to 'ON - missing only if all missable operands are missing'

Thanks T

October 07, 2019, 01:31 PM
Uggh... the simplest things take way too long...

So looks like I'm trying to do the same as Trudy up there, but I not successful.

I am trying to create a DEFINE in the metadata (synonym) that contcatenates a few fields, but seems like things go to poop when there is null data.

I've tried | and || and all three options for MISSING (ya I'm at the point where I'm just trying all combinations). I can't seem to figure this out. Should the data type be A250 or A250V, what's the difference?

I would really like to do this in the metadata to reap its obvious advantages over DEFINES in the fex.

WebFOCUS 8201, SP 0.1, Windows 7, HTML
October 08, 2019, 07:51 AM
Mike in DeLand
I've had decent luck using the DBEXPR function in the metadata - using that, you can use the NVL function of SQL to help handle null values. Also, using this function will avoid the issue of some webfocus functions that don't translate to SQL, requiring a full download of the selected data instead of getting the advantage of aggregation in sql.

Webfocus 8
Windows, Linux
October 08, 2019, 08:59 AM
Shingles, you should refer to the Creating Reports With WebFOCUS Language manual. See chapter 13 in the 8.2.04 manual. What you say is happening sounds like a bug unless your synonyms are not accurate. The default for a DEFINE is that if the field is computed if SOME of the values are not missing. So in Trudy's original example, I think MEETDAYS should look like this:



With the defaults on, the result should be '.11'. This is how it works for me.

As for the A250 versus A250V, V is a variable length field. For several reasons, probably related to how we have our environment set up, it is just easier to use fixed length fields. We have reports that still run the pre-date WebFOCUS by 10+ years.

In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.