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

MEETDAYS=SSRMEET_SUN_DAY|SSRMEET_MON_DAY|SSRMEET_TUE_DAY  

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

MEETDAYS=SSRMEET_MON_DAY|SSRMEET_TUE_DAY  

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,

Try

MEETDAYS/A3V=SSRMEET_SUN_DAY || SSRMEET_MON_DAY || SSRMEET_TUE_DAY;

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.

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.

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

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.

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.

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:

MEETDAYS/A3=SSRMEET_SUN_DAY|SSRMEET_MON_DAY|SSRMEET_TUE_DAY;

OR

MEETDAYS/A3 MISSING ON/OFF NEEDS SOME/ALL=SSRMEET_SUN_DAY|SSRMEET_MON_DAY|SSRMEET_TUE_DAY;

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.