Hello, is there a way to define a field consisting of 3 different field concat together coming from 3 different tables (joined together)?
When I do it the usual way : A/A3=B|C|D it tells me that fields used in define are coming from more than one data path.
Thanks a lot

I assume these tables are in an RDBMS DB? I would do the concatenation in a subsequent step.

Thanks Francis, I was also thinking about it, but hoped that there is some trick I could use.

Never bother with ALL|MULTIPLE if you are going against a single RDBMS since the database will ignore these settings.

However wf will still apply checks on defines looking for a multi path structure.

If these 3 tables are the same rdbms then drop the ALL or MULTIPLE from your joins and then the define will work.

Use caution when using (or not using as the case may be) ALL|MULTIPLE against RDMBMS. Depending on the optimization settings, FOCUS will try to take over the join, especially when keys are set in the AFD.

This code gave me no error messages regarding concatenation:

JOIN
BASEL_BSL_CL_LQ_ACT_D.LQ_ACT_NO IN BASEL_BSL_CL_LQ_ACT_D TO ALL
BASEL_BSL_CL_LQ_ACT_F.LQ_ACT_NO IN BASEL_BSL_CL_LQ_ACT_F AS J1
END
JOIN
BASEL_BSL_CL_LQ_ACT_F.TIME_DIM_KEY IN BASEL_BSL_CL_LQ_ACT_D TO
BASEL_TIME_D.TIME_DIM_KEY IN BASEL_TIME_D AS J2
END
JOIN
BASEL_BSL_CL_LQ_ACT_D.RESP_NODE_ID IN BASEL_BSL_CL_LQ_ACT_D TO
BASEL_BSL_REPTG_RESP_F.RESP_NODE_ID IN BASEL_BSL_REPTG_RESP_F AS J3
END
-RUN

DEFINE FILE BASEL_BSL_CL_LQ_ACT_D
CONCAT1/A200 = BASEL_BSL_CL_LQ_ACT_D.SRC_SYS_ACT_NO | BASEL_TIME_D.DAY_OF_WEEK | BASEL_BSL_REPTG_RESP_F.ADDR_LINE_1;
END
-RUN

TABLE FILE BASEL_BSL_CL_LQ_ACT_D
SUM
CONCAT1
BY CONCAT1
WHERE BASEL_BSL_REPTG_RESP_F.TIME_DIM_KEY EQ 37520;

WHERE READLIMIT EQ 10
END
-RUN

The SQL generated:

SELECT SK001, MAX(VB001) FROM (SELECT ((T1."SRC_SYS_ACT_NO" ||
 T4."DAY_OF_WEEK") || T2."ADDR_LINE_1") AS SK001,
 ((T1."SRC_SYS_ACT_NO" || T4."DAY_OF_WEEK") || T2."ADDR_LINE_1")
 AS VB001 FROM BASEL.BSL_CL_LQ_ACT_D T1,BASEL.BSL_REPTG_RESP_F
 T2,BASEL.BSL_CL_LQ_ACT_F T3,BASEL.TIME_D T4 WHERE
 (T2."RESP_NODE_ID" = T1."RESP_NODE_ID") AND (T3."LQ_ACT_NO" =
 T1."LQ_ACT_NO") AND (T4."TIME_DIM_KEY" = T3."TIME_DIM_KEY") AND
 (T2."TIME_DIM_KEY" = 37520) ) X  GROUP BY SK001 ORDER BY SK001
 FETCH FIRST 10 ROWS ONLY FOR FETCH ONLY;

So, whether concatenation works or not depends on the JOIN and on the DEFINE.

The error you're getting is most likely due to what John and Gizmo mention.

It actually worked with changing the joins to unique!
Thanks a ton everybody!
JOIN
LEFT_OUTER PRODUCTSKU.PRODUCTSKU.SKUCOLORID IN catalogproduct TO UNIQUE
SKUCOLOR.SKUCOLOR.SKUCOLORID IN skucolor AS J1
END
JOIN
LEFT_OUTER PRODUCTSKU.PRODUCTSKU.SKUSIZEID IN catalogproduct TO UNIQUE
SKUSIZE.SKUSIZE.SKUSIZEID IN skusize AS J2
END
JOIN
LEFT_OUTER PRODUCTSKU.PRODUCTSKU.SKUDESIGNID IN catalogproduct TO UNIQUE
SKUDESIGN.SKUDESIGN.SKUDESIGNID IN skudesign AS J3
END
DEFINE FILE CATALOGPRODUCT
SHRT/A50=SKUCOLOR | SKUSIZE | SKUDESIGN;
END

The other option is to do the field as a COMPUTEd field. At the time it is evaluated all extracted data is in the internal table (internal matrix) so the paths are no longer relevant.

I try to use COMPUTEd fields as much as possible rather than DEFINEs if I get the same answer in both cases. COMPUTEd fields will never be less efficient than DEFINEs and as we've seen in other posts you can unintentionally disable optimization when using DEFINEd fields.

BTW... if you want to sort on your concatenated value I would do this to ensure sorting gets passed to the rdbms:

TABLE FILE CATALOGPRODUCT
SUM 
  COMPUTE SHRT/A50=SKUCOLOR | SKUSIZE | SKUDESIGN;
BY SKUCOLOR  NOPRINT
BY SKUSIZE   NOPRINT
BY SKUDESIGN NOPRINT
END

Remember where WebFOCUS stems from: from FOCUS. Which is a hierarchical system at heart.
This means that the way of looking at data and data structures is still done in terms of segments.
When a parent segment has a unique child (SEGTYPE=U, or 1:1 JOIN), this child is treated as if it was part of the parent segment. This is not the case when the child is not unique (SEGTYPE=Sx or 1:n JOIN). In the latter case you will also see missing data for field in the child segment and the ALL parameter suddenly kicks in, while this is not the case with unique childs.
This is also the reason for the different database paths.
If you have unique segments under your parent and exactly one 1 non-unique, you'll never get a database path error, because all uniques are considered to be part of the parent.
You will only get the database pathing error with more than 1 non-unique child segment (or join).
And then of course only if you reference fields from both paths in the same request.

In some cases you might be able to circumvent this problem by selecting an alternate entry into your db structure. For instance TABLE FILE CAR.WARRANTY which will read the car database starting from the warranty segment and then work its way up to the root of the file.
This does change the logical structure of the file, which you can examine with CHECK FILE CAR.WARRANTY PICT

It's really amazing what this product can do....

Yeah, that's true, I didn't even think about COMPUTED field.
Thanks a lot Piipster.