[SOLVED]How to get aggregation done for a sum of products field in WF
Hi all,
I am now facing one issue about aggregation in WebFOCUS. Below is the description:
In WebFOCUS report, I would like to add up the value of (QUANTITY * UNIT_PRICE).
WebFOCUS
TABLE FILE TABLE_1
SUM
COMPUTE FIELD_1/D12.2 = QUANTITY * UNIT_PRICE;
BY CUSTOMER_ID
END
To improve the performance, the aggregation should be done in database level. However when I ran the report with the above code, I got the below log:
SQL code generated by WebFOCUS
SELECT
T1.CUSTOMER_ID,
SUM(T1.QUANTITY),
SUM(T1.UNIT_PRICE)
FROM TABLE_1 T1
GROUP BY
T1.CUSTOMER_ID
ORDER BY
T1.CUSTOMER_ID
Can you please guide how to execute aggregation for the calculation (QUANTITY * UNIT_PRICE) in database level? Thanks in advance!This message has been edited. Last edited by: <Emily McAllister>,
WebFOCUS 8105 Windows, All Outputs
December 19, 2016, 02:42 AM
Dave
S.G.
this should be basic knowledge. Please consider a course or some education. We're not a paid WebFocus-webcare team. Just peers.
COMPUTE's are executed on the result. Exactly what the SQL shows.
use DEFINE to create row-level calculations
DEFINE FILE TABLE_1
FIELD_1/D12.2 = QUANTITY * UNIT_PRICE;
END
TABLE FILE TABLE_1
SUM
FIELD_1
END
And no, you do not have to add QUANTITY or UNIT_PRICE to the SUM statement.
Greets,
_____________________ WF: 8.0.0.9 > going 8.2.0.5
December 19, 2016, 04:34 AM
S.G
Hi Dave,
Thank you for the suggestion!
There is huge data in TABLE_1, so DEFINE operation will query and get all data from TABLE_1 into WebFOCUS. I think it will lead to low performance.
Could you please let me know if there is any other method? Thanks!
WebFOCUS 8105 Windows, All Outputs
December 19, 2016, 04:58 AM
WF_IL
Hi
Please note that most relational adapters translate Define to SQL:
DEFINE FILE wfretail81/wf_retail_lite
Define_1/D12.2=WF_RETAIL_LITE.WF_RETAIL_SALES.GROSS_PROFIT_US / WF_RETAIL_LITE.WF_RETAIL_SALES.DISCOUNT_US ;
END
TABLE FILE wfretail81/wf_retail_lite
SUM Define_1
BY WF_RETAIL_LITE.WF_RETAIL_PRODUCT.PRODUCT_CATEGORY
END
SELECT
T7."PRODUCT_CATEGORY",
SUM((T1."GROSS_PROFIT_US" / T1."DISCOUNT_US"))
FROM
( wrd_wf_retail_sales T1
LEFT OUTER JOIN wrd_wf_retail_product T7
ON T7."ID_PRODUCT" = T1."ID_PRODUCT" )
GROUP BY
T7."PRODUCT_CATEGORY"
ORDER BY
T7."PRODUCT_CATEGORY";
Define will translate to, depending on the logic in it, to SQL.
Even IF statement will be converted to CASE. But you have to test all of it. Not all WebFocus statements are translated to SQL. ( depending on your data-provider and adapter I guess ).
_____________________ WF: 8.0.0.9 > going 8.2.0.5
December 19, 2016, 10:49 AM
EricH
S.G, You are saying that you want the database to perform the COMPUTE due to performance issues (large volume of data). But since there are no WHERE clauses in your queries, you are going to get the same # of rows back regardless of where the math is performed. If you have 5 million CUSTOMER_IDs you will get back 5 million rows. Having the additional column will only introduce trivial extra cycles, most of the work is done retrieving those 5 million rows.
But otherwise, I also agree with Eran's suggestion of converting to DEFINE.
December 19, 2016, 09:33 PM
S.G
Thanks all! I got it.
I wonder that if there are some documents for the similar technical question to refer?