Focal Point Banner

As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Concatenating alpha fields where some fields are missing

Read-Only Read-Only Topic
[SOLVED]Concatenating alpha fields where some fields are missing
Platinum Member
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,

Posts: 117 | Registered: May 28, 2015Report This Post
posted Hide Post


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
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
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.

Posts: 117 | Registered: May 28, 2015Report This Post
Platinum Member
posted Hide Post
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

Posts: 117 | Registered: May 28, 2015Report This Post
Platinum Member
posted Hide Post
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
Posts: 190 | Registered: May 19, 2017Report This Post
posted Hide Post
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
Posts: 258 | Location: Palm Coast, FL | Registered: February 05, 2010Report This Post
posted Hide Post
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.
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Concatenating alpha fields where some fields are missing

Copyright © 1996-2020 Information Builders