Good day I am a new WebFocus developer and am trying to createa simple report that joins three tables from the same database. When I run the report I get the following messages in the trace:
11.25.21 BS (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
11.25.21 BS (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED

when I create the sql statement raw I get results, but within webfocus I get no results. The master tables were generated using webfocus synomym generator via the JDE Adaptor.
The WebFocus code is:
JOIN
LEFT_OUTER F060116.F060116.BUSINESS_UNIT IN F060116 TO MULTIPLE
F0006.F0006.BUSINESS_UNIT IN F0006 TAG J2 AS J2
END
JOIN
INNER F060116.F060116.SUPERVISOR IN F060116 TO UNIQUE
F0101.F0101.ADDRESS_NUMBER IN F0101 TAG J3 AS J3
END
TABLE FILE F060116
PRINT
ADDRESS_NUMBER
ALPHA_NAME
BUSINESS_UNIT
DESCRIPTION
HEADING
""
FOOTING
""
WHERE BUSINESS_UNIT EQ ' 540343';

The result of the Webfocus trace is:

11.25.21 BS (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
11.25.21 BS (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
11.25.21 AE SELECT T1."YAAN8",T1."YAALPH",T1."YAMCU",T3."MCAN8",
11.25.21 AE T3."MCDL01" FROM PRODDTA/F060116 T1,PRODDTA/F0006 T3 WHERE
11.25.21 AE (T3."MCMCU" = T1."YAMCU") AND (T1."YAMCU" = ' 540343') AND
11.25.21 AE (T3."MCAN8" BETWEEN 1 AND 99999999) FOR FETCH ONLY;
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

As you can see the generated sql is not showing the F0101 table.

the SQL code I'm trying to duplicate is:

select yaan8, yaalph, yamcu, mcdl01, abalph from f060116
left outer join f0006 on f060116.yamcu = f0006.mcmcu
inner join f0101 on f060116.yaanpa = f0101.aban8
where yamcu = ' 540343'

Any help you can provide would be greatly appreciated.

We run 7.6.10

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

quote:

TABLE FILE F060116
PRINT
ADDRESS_NUMBER
ALPHA_NAME
BUSINESS_UNIT
DESCRIPTION
...

Your must use/select a specific field from F0101 in order for WebFOCUS to include it in the actual JOIN structure.

Based on your TABLE FILE statement, it seems to me like you are indeed attempting to use a field from that table (ADDRESS_NUMBER) but would it be possible that you have another field with the same name either in F060116 or F0006?

Sadly, the cryptic field names used in the database tables do not shed any lights on this so you will need to mznually inspect your master files (synonyms) to ensure your field names are not duplicated.

As a quick test, you may want to prefix the field name to hint WebFOCUS as to what actual table/column you want to read. Something like:

TABLE FILE F060116
PRINT
      J3.ADDRESS_NUMBER
      J2.BUSINESS_UNIT
      ...

I have a question (based on my obvious lack of knowledge about your data), can you really join a SUPERVISOR to an ADDRESS_NUMBER? Are your data types and lengths "compatible"?

The aggregation message is not an error message - it is informational, and is generated when you are not aggregating data (PRINT=no aggregation, SUM=aggregation).

The F0101 table is not referenced in the generated SQL, meaning the JOIN is ignored, if no reference is made to a column in the table.

quote:

11.25.21 BS (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
11.25.21 BS (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED

That message is OK. It is just indicating that there are no aggregations being done by the database engine which makes sense as you are using a PRINT verb which is supposed to bring back each record that meets your selection criteria.

Aggregations would take place when using a SUM verb but this is not your case, so you can safely ignore that message.

njsden:
yes F0101 and F060116 both will have address_number as it is primary field in most JDE tables, it is a way to link multiple child tables to the primary table (F0101). I will use your suggestion about prefixing the field names. I was not aware that you had to reference a field in order for it's respective table to be included. Thanks for that info.

Both supervisor and address_number are P8S in the master definition and on the JDE side they are Numeric 8, so I can safely join them. As I supervisor is YAANPA in the SQL I pasted into the original code.
I'm sorry, I should've used alpha descriptions instead of the aliases in the sql code.
YAAN8=F060116_address_number
yaalph=F060116_alpha_name
yamcu = F060116_business_unit
yaanpa = F060116_Supervisor
mcmcu = F0006_business_unit
mcdl01 = F0006_business_unit_description
aban8 = F0101_address_number
abalph = F0101_alpha_name

quote:

reference a field in order for it's respective table to be included

- Only when the same field name exists in more than one table in the join structure. This appears to be the case in your set of joins.

Thanks for all your replies and assistance. I did what you suggested and referenced tables to fields, so here is what the code looks like now:

JOIN
LEFT_OUTER F060116.F060116.BUSINESS_UNIT IN F060116 TO MULTIPLE
F0006.F0006.BUSINESS_UNIT IN F0006 TAG J2 AS J2
END
JOIN
INNER F060116.F060116.SUPERVISOR IN F060116 TO UNIQUE
F0101.F0101.ADDRESS_NUMBER IN F0101 TAG J3 AS J3
END
TABLE FILE F060116
PRINT
ADDRESS_NUMBER
ALPHA_NAME
BUSINESS_UNIT
J2.F0006.DESCRIPTION
J3.F0101.ALPHA_NAME
HEADING
""
FOOTING
""
WHERE BUSINESS_UNIT EQ ' 540343';

The result that comes back is:

12.54.05 BS (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
12.54.05 BS (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
12.54.05 AE SELECT T1."YAAN8",T1."YAALPH",T1."YAMCU",T1."YAANPA",
12.54.05 AE T2."ABALPH",T3."MCAN8",T3."MCDL01" FROM PRODDTA/F060116 T1,
12.54.05 AE PRODDTA/F0101 T2,PRODDTA/F0006 T3 WHERE (T2."ABAN8" =
12.54.05 AE T1."YAANPA") AND (T3."MCMCU" = T1."YAMCU") AND (T1."YAMCU" = '
12.54.05 AE 540343') AND (T3."MCAN8" BETWEEN 1 AND 99999999) FOR FETCH
12.54.05 AE ONLY;
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

When I take this code and paste it into SQL it doesn't work, but when I take out the line about "T3.MCAN8 BETWEEN 1 and 9999999 FOR FETCH ONLY" it works in the SQL. I do not know where that line comes from as I didn't put that criteria in the code; neither text nor GUI. THE MCAN8 field is not a primary key in the F0006 table, though it is an index on that table.

quote:

FROM PRODDTA/F060116 T1, PRODDTA/F0101 T2, PRODDTA/F0006 T3

Good. Prefixing the field names gave WF enough information as to which fields needed to be included in your query and I can see that the 3 tables are now part of your JOIN structure.

Trying to give any advice without knowing much about the underlying database technology or the details about the masterfiles you're working with is very difficult. I have never worked with "JDE tables" so it's hard to tell why the resolved SQL statement ended up with that filter on T3.MCAN8 when no particular WHERE/IF statement was used.

Could you please add:

JOIN CLEAR *

before your JOIN statements to make sure no other "stuff" is being carried over from previous sections in your code? Sorry if that makes no sense, but I'm trying to guide you in the blind here. Maybe someone with actual experience in JD Edwards could give you more useful hints.

You may also want to take a look at the iWay Adapter for JD Edwards manual for particular details.

Best,
Neftali.

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

Neftali,

Thank you so much for assisting me. Yes, "JDE Tables" is JD Edwards, we are using OneWorld E8.12. I have placed the JOIN CLEAR * on the code, I should've done that sooner as that was what I was shown in classes and what consultants have done on-site. Thanks for the reminder

SQL being SQL, nothing I have seen should put that last statement in it. I'm not referring to it anywhere in the code, it's nothing more then an index in the table. What is even more surprising we have records that meet that criteria so why they wouldn't show up is another inquiry. I just would like it gone, but I don't know how, since I don't know where it comes from.

quote:

SQL being SQL, nothing I have seen should put that last statement in it

You're right on that and I would usually think the same thing. However, one doesn't necessarily have total control over the SQL that is generated by the iWay Adapter unless all of the rules it uses are properly understood and followed. This is why it would be very useful for you to take a good look at the iWay Adapter for JD Edwards One World manual (available in the documentation section at the Information Builder's Tech Support site).

A general rule when dealing with relational databases says that, for an database index to be used during data retrieval (which might improve performance in some cases) the underlying indexed field *must* be used as part of the selection criteria, right? Well, who knows! maybe the iWay Adapter for JDE "knows" that there is an index on F0101.F0101.MCAN8 (that's why I've asked you to carefully review the masterfiles) and is automagically adding a filter on that field to force the database index to get used. If the field is defined as P8 for instance, it sort of makes sense that the condition: (T3."MCAN8" BETWEEN 1 AND 99999999) would always evaluate to true *IF* there are no null, zero or negative values for that field in your table. You can tell I'm totally guessing right there and what I'm saying may not make much sense (it doesn't to me anyway) but I just cannot find a logical explanation to what you describe so I'm making my own dangerous assumptions

Jeremey:

Have you tried removing F0006 out of the equation? Try to join F060116 to F0101 for now and analyze the SQL generated. Baby steps might help you there.

As a final comment, it looks rather suspicious to me that even though you're defining an OUTER JOIN to F0006 and a regular INNER JOIN to F0101, there is nothing in the final SQL statement that indicates an outer join taking place at all. You may want to look into that as well.

quote:

What is even more surprising we have records that meet that criteria so why they wouldn't show up is another inquiry

You are joining 3 tables, so it's possible that some instances of T1 don't have matching records in both T2 and T3 and that's why the're being discarded. I know that your intention is to perform an outer join to T2 but the generated SQL does not seem to be doing an outer join at all. You may need to review the iWay adapter's connection settings to see what's going on.

I'm afraid this is as far as I can go due to my non-experience with JD Edwards and their corresponding adapter in WF. Hopefully someone in the forum with more experience in the matter will step in and provide more meaningful insights.

Neftali.

quote:

T3."MCAN8" BETWEEN 1 AND 99999999

The replacement of the LEFT OUTER JOIN with an INNER JOIN is probably caused by this WHERE clause. We've seen similar behavior with other relational databases. Because of the WHERE clause on a data element in the lowest table in the join structure, F0006, the translator assumes data will exist in higher level tables for records selected from F0006. Therefore, the LEFT OUTER JOIN must not necessary. You might try adding SET ALL = PASS before your JOIN statements to see if this will cause the translator to leave the LEFT OUTER JOIN in place.

Thanks for all your suggestions/advise. I talked with the CE and he couldn't figure it out either, so as was recommeded I opened a ticket with support. The CE seems to think it may be an issue with the adaptor and Neftali also mentioned maybe the adaptor is putting it in there. So time to see what the developers with support say.