Peter Vazny Posted July 8, 2021 Share Posted July 8, 2021 I need to create a report that numbers the rows returned, but I have not found a way to do that so it does not break aggregation. I am aware of the standard technique of Row/I9 = LAST Row + 1, but that results in FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON: FOC2565 - THE OBJECT Row OF BY/ACROSS CANNOT BE CONVERTED TO SQL FOC2566 - DEFINE Row CANNOT BE CONVERTED TO SQL FOC2586 - SELF-REFERENCING DEFINE Row CANNOT BE CONVERTED TO SQL I have also tried PARTITION_AGGR with the same results. I want to save the the result as SQL_SCRIPT (ON TABLE HOLD AS <table_name> FORMAT SQL_SCRIPT) to be able to create a subselect and push the bulk of the processing to DB2 Link to comment Share on other sites More sharing options...
Manoj Chaurasia Posted July 8, 2021 Share Posted July 8, 2021 Peter You might want to consider having your DB2 DBA sequence the table so you have a value in there to use directly. Link to comment Share on other sites More sharing options...
Peter Vazny Posted July 8, 2021 Author Share Posted July 8, 2021 I was hoping to be able to use it as needed for different reports and avoid creating dedicated views for a specific report. Link to comment Share on other sites More sharing options...
David Briars Posted July 9, 2021 Share Posted July 9, 2021 If I understand your post correctly your requirements are to: Display a line counter on your final summarized report. Insure that the SQL generated by WebFOCUS is optimized, that is, the aggregation is passed to the relational database. From the error message you show, it looks like you attempted to DEFINE a row counter. You might want to try a COMPUTEd field instead. Here is an example against the WebFOCUS WF_Retail_Lite test MSSQL table: -* -* Turn on SQL Tracing. -* SET TRACEOFF = ALL SET TRACEON = SQLTRANS SET TRACEON = STMTRACE//CLIENT SET TRACEON = SQLAGGR//CLIENT SET TRACESTAMP = OFF SET TRACEWRAP = 78 SET TRACEUSER = ON -RUN -* -* Create Revenue Report -* TABLE FILE WF_RETAIL_LITE "Revenue By Product Category By Date" SUM COMPUTE ROW_COUNTER/I9 = ROW_COUNTER + 1; AS '#' PRODUCT_CATEGORY TIME_DATE REVENUE_US BY PRODUCT_CATEGORY NOPRINT BY TIME_DATE NOPRINT ON TABLE PCHOLD FORMAT HTML ON TABLE NOTOTAL ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$ ENDSTYLE END And the generated SQL is optimized: image.png494562 10.1 KB Link to comment Share on other sites More sharing options...
Peter Vazny Posted July 9, 2021 Author Share Posted July 9, 2021 Not quite. What I am tryin to do is create a subselect that can later be used in a join or as a filter. Ive been successful doing this using the ON TABLE HOLD AS <temp_table> FORMAT SQL_SCRIPT and then using the <temp_table> in a join of the final report. This all gets converted to SQL and the processing is pushed to the relational database. However, if I need to number the rows in the <temp_table>, to preserve ordering for example, this will break the process and wont allow me to save the table as SQL_SCRIPT. I am aware that when you use a COMPUTEd field, it is rendered after data retrieval and does not affect the SQL query sent to the relational database. However that only helps in the final report. Not in a subselect. Let say I want a list of all sales for top X customers ranked by total sales. I need to get a sum of all sales per customer, I need to rank them, pick top X and then filter sales based on that. Here is an example from wf_retail_light: ENGINE INT CACHE SET ON SET PAGE-NUM=NOLEAD SET SQUEEZE=ON -DEFAULTH &WF_HTMLENCODE=ON; SET HTMLENCODE=&WF_HTMLENCODE SET HTMLCSS=ON -DEFAULTH &WF_EMPTYREPORT=ON; SET EMPTYREPORT=&WF_EMPTYREPORT -DEFAULTH &WF_ARVERSION=1; SET ARVERSION=&WF_ARVERSION *-HOLD_SOURCE -DEFAULTH &WF_SUMMARY='Summary'; -DEFAULTH &WF_TITLE='WebFOCUS Report'; TABLE FILE retail_samples/wf_retail_lite SUM WF_RETAIL_LITE.WF_RETAIL_SALES.COGS_US AS ('TotalSales', WF_RETAIL_LITE.WF_RETAIL_SALES.COGS_US, 'TotalSales' ) BY WF_RETAIL_LITE.WF_RETAIL_CUSTOMER.ID_CUSTOMER ON TABLE HOLD AS SalesTotal FORMAT SQL_SCRIPT ON TABLE NOTOTAL ON TABLE SET CACHELINES 100 ON TABLE SET GRWIDTH 1 ON TABLE SET ASNAMES SUBST ON TABLE SET HOLDATTRS ON ON TABLE SET HOLDLIST PRINTONLY ON TABLE SET STYLE * INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$ TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $ ENDSTYLE END -RUN *-HOLD_SOURCE -DEFAULTH &WF_SUMMARY='Summary'; -DEFAULTH &WF_TITLE='WebFOCUS Report'; TABLE FILE SalesTotal PRINT SALESTOTAL.SALESTOTAL.ID_CUSTOMER BY HIGHEST SALESTOTAL.SALESTOTAL.TotalSales ON TABLE HOLD AS SalesRanked FORMAT SQL_SCRIPT ON TABLE NOTOTAL ON TABLE SET CACHELINES 100 ON TABLE SET ASNAMES SUBST ON TABLE SET HOLDATTRS ON ON TABLE SET HOLDLIST PRINTONLY ON TABLE SET STYLE * INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$ TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $ ENDSTYLE END -RUN -*COMPONENT=Join_J001 JOIN WF_RETAIL_LITE.WF_RETAIL_CUSTOMER.ID_CUSTOMER IN RETAIL_SAMPLES/WF_RETAIL_LITE TO UNIQUE SALESRANKED.SALESRANKED.ID_CUSTOMER IN SALESRANKED TAG J001 AS J001 END -DEFAULTH &WF_SUMMARY='Summary'; -DEFAULTH &WF_TITLE='WebFOCUS Report'; TABLE FILE retail_samples/wf_retail_lite SUM WF_RETAIL_LITE.WF_RETAIL_SALES.QUANTITY_SOLD BY WF_RETAIL_LITE.WF_RETAIL_CUSTOMER.FIRSTNAME BY WF_RETAIL_LITE.WF_RETAIL_CUSTOMER.LASTNAME BY J001.SALESRANKED.TOTALSALES BY WF_RETAIL_LITE.WF_RETAIL_TIME_SALES.TIME_DATE_YEAR_COMPONENT ON TABLE PCHOLD FORMAT HTML ON TABLE NOTOTAL ON TABLE SET CACHELINES 100 ON TABLE SET STYLE * INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$ TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $ ENDSTYLE END -RUN at this point I have not used any row counting or ranking so it happily translates to SQL: SELECT T2."ID_CUSTOMER" AS "SK001_ID_CUSTOMER", SUM(T1."COGS_US") AS "VB001_SUM_COGS_US" FROM ( qwqretail/qwqretail_wf_retail_sales T1 LEFT OUTER JOIN qwqretail/qwqretail_wf_retail_customer T2 ON T2."ID_CUSTOMER" = T1."ID_CUSTOMER" ) GROUP BY T2."ID_CUSTOMER"; FOC2546 - SQL SCRIPT SALESTOTAL.SQL CREATED SUCCESSFULLY (BUT NOT EXECUTED) _EDATEMP/salestotal HELD AS SQL_SCRIPT SELECT T1."VB001_SUM_COGS_US" AS "SK001_TotalSales", T1."SK001_ID_CUSTOMER" AS "VB001_PRN_ID_CUSTOMER" FROM ( /* vvv */ SELECT T2."ID_CUSTOMER" AS "SK001_ID_CUSTOMER", SUM(T1."COGS_US") AS "VB001_SUM_COGS_US" FROM ( qwqretail/qwqretail_wf_retail_sales T1 LEFT OUTER JOIN qwqretail/qwqretail_wf_retail_customer T2 ON T2."ID_CUSTOMER" = T1."ID_CUSTOMER" ) GROUP BY T2."ID_CUSTOMER" ) /* ^^^ */ T1; FOC2546 - SQL SCRIPT SALESRANKED.SQL CREATED SUCCESSFULLY (BUT NOT EXECUTED) _EDATEMP/salesranked HELD AS SQL_SCRIPT (FOC2689) AGGREGATION DONE ... SELECT T2."FIRSTNAME", T2."LASTNAME", T4."SK001_TotalSales", T5."TIME_DATE_YEAR_COMPONENT", SUM(T1."QUANTITY_SOLD") FROM ( ( ( qwqretail/qwqretail_wf_retail_sales T1 LEFT OUTER JOIN qwqretail/qwqretail_wf_retail_customer T2 ON T2."ID_CUSTOMER" = T1."ID_CUSTOMER" ) INNER JOIN ( /* vvv */ SELECT T1."VB001_SUM_COGS_US" AS "SK001_TotalSales", T1."SK001_ID_CUSTOMER" AS "VB001_PRN_ID_CUSTOMER" FROM ( /* vvv */ SELECT T2."ID_CUSTOMER" AS "SK001_ID_CUSTOMER", SUM(T1."COGS_US") AS "VB001_SUM_COGS_US" FROM ( qwqretail/qwqretail_wf_retail_sales T1 LEFT OUTER JOIN qwqretail/qwqretail_wf_retail_customer T2 ON T2."ID_CUSTOMER" = T1."ID_CUSTOMER" ) GROUP BY T2."ID_CUSTOMER" ) /* ^^^ */ T1 ) /* ^^^ */ T4 ON (T4."VB001_PRN_ID_CUSTOMER" = T2."ID_CUSTOMER") ) LEFT OUTER JOIN qwqretail/qwqretail_wf_retail_time_lite T5 ON T5."ID_TIME" = T1."ID_TIME" ) GROUP BY T2."FIRSTNAME", T2."LASTNAME", T4."SK001_TotalSales", T5."TIME_DATE_YEAR_COMPONENT" ORDER BY T2."FIRSTNAME", T2."LASTNAME", T4."SK001_TotalSales", T5."TIME_DATE_YEAR_COMPONENT" FOR FETCH ONLY; However as soon as I try to add row numbering or ranking to the SalesRanked intermediate table/subselect, the report breaks: _EDATEMP/salestotal HELD AS SQL_SCRIPT (FOC2646) OPTIMIZATION OF HOLD FORMAT SQL_SCRIPT CANNOT BE DONE: (FOC2629) REQUEST IS IMPOSSIBLE TO CONVERT TO SQL (SEE SQLAGGR TRACE) (FOC2647) CHOOSING FALLBACK OPTION... _EDATEMP/salesranked HELD AS DATREC FILE This is just a sample use case. I am pretty sure that some clever workaround can be made in this specific case, but I am interested in a solution that would allow me to use row numbers or ranking without breaking the SQL optimization. Link to comment Share on other sites More sharing options...
Martin Yergeau Posted July 9, 2021 Share Posted July 9, 2021 The following seems to work Ok *-HOLD_SOURCE -DEFAULTH &WF_SUMMARY='Summary'; -DEFAULTH &WF_TITLE='WebFOCUS Report'; TABLE FILE wf_retail_lite SUM WF_RETAIL_LITE.WF_RETAIL_SALES.COGS_US AS ('TOTALSALES', WF_RETAIL_LITE.WF_RETAIL_SALES.COGS_US, 'TOTALSALES' ) BY WF_RETAIL_LITE.WF_RETAIL_CUSTOMER.ID_CUSTOMER ON TABLE HOLD AS SALESTOTAL FORMAT SQL_SCRIPT ON TABLE NOTOTAL ON TABLE SET CACHELINES 100 ON TABLE SET GRWIDTH 1 ON TABLE SET ASNAMES SUBST ON TABLE SET HOLDATTRS ON ON TABLE SET HOLDLIST PRINTONLY ON TABLE SET STYLE * INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$ TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $ ENDSTYLE END -RUN *-HOLD_SOURCE -DEFAULTH &WF_SUMMARY='Summary'; -DEFAULTH &WF_TITLE='WebFOCUS Report'; TABLE FILE SALESTOTAL PRINT ID_CUSTOMER -*-* Give a RANK by TOTALSALES value RANKED AS 'RANK' BY HIGHEST TOTALSALES -*-* ================================ ON TABLE HOLD AS SALESRANKED FORMAT SQL_SCRIPT ON TABLE NOTOTAL ON TABLE SET CACHELINES 100 ON TABLE SET ASNAMES SUBST ON TABLE SET HOLDATTRS ON ON TABLE SET HOLDLIST PRINTONLY ON TABLE SET STYLE * INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$ TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $ ENDSTYLE END -RUN -*COMPONENT=Join_J001 JOIN WF_RETAIL_LITE.WF_RETAIL_CUSTOMER.ID_CUSTOMER IN WF_RETAIL_LITE TO UNIQUE SALESRANKED.SALESRANKED.ID_CUSTOMER IN SALESRANKED TAG J001 AS J001 END -DEFAULTH &WF_SUMMARY='Summary'; -DEFAULTH &WF_TITLE='WebFOCUS Report'; TABLE FILE wf_retail_lite SUM WF_RETAIL_LITE.WF_RETAIL_SALES.QUANTITY_SOLD -*-* Print RANK just to validate value BY RANK -*-* ============================ BY WF_RETAIL_LITE.WF_RETAIL_CUSTOMER.FIRSTNAME BY WF_RETAIL_LITE.WF_RETAIL_CUSTOMER.LASTNAME BY J001.SALESRANKED.TOTALSALES BY WF_RETAIL_LITE.WF_RETAIL_TIME_SALES.TIME_DATE_YEAR_COMPONENT -*-* Filter on RANK value WHERE RANK LE 10; -*-* ==================== ON TABLE PCHOLD FORMAT HTML ON TABLE NOTOTAL ON TABLE SET CACHELINES 100 ON TABLE SET STYLE * INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$ TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $ ENDSTYLE END -RUN Link to comment Share on other sites More sharing options...
Peter Vazny Posted July 9, 2021 Author Share Posted July 9, 2021 Maybe this is due to a WebFocus version difference and/or the specific SQL optimizer for my flavor of DBMS (DB2), but that did not work for me. It somehow knows that there should be a generated column with the name RNKD0008, but it does not do it. This is the result of your code in my environment: 11.25.56.438216 SELECT 11.25.56.438296 T2."ID_CUSTOMER" AS "SK001_ID_CUSTOMER", 11.25.56.438328 SUM(T1."COGS_US") AS "VB001_SUM_COGS_US" 11.25.56.438352 FROM 11.25.56.438376 ( qwqretail/qwqretail_wf_retail_sales T1 11.25.56.438400 LEFT OUTER JOIN 11.25.56.438432 qwqretail/qwqretail_wf_retail_customer T2 11.25.56.438456 ON T2."ID_CUSTOMER" = T1."ID_CUSTOMER" ) 11.25.56.438480 GROUP BY 11.25.56.438504 T2."ID_CUSTOMER"; 11.25.56.438800 (FOC2546) SQL SCRIPT SALESTOTAL.SQL CREATED SUCCESSFULLY (BUT NOT EXECUTED) _EDATEMP/salestotal HELD AS SQL_SCRIPT 11.25.56.508944 SELECT 11.25.56.509024 T1."VB001_SUM_COGS_US" AS "SK001_TOTALSALES", 11.25.56.509048 T1."SK001_ID_CUSTOMER" AS "VB001_PRN_ID_CUSTOMER" 11.25.56.509080 FROM 11.25.56.509096 ( /* vvv */ 11.25.56.509120 SELECT 11.25.56.509144 T2."ID_CUSTOMER" AS "SK001_ID_CUSTOMER", 11.25.56.509176 SUM(T1."COGS_US") AS "VB001_SUM_COGS_US" 11.25.56.509200 FROM 11.25.56.509224 ( qwqretail/qwqretail_wf_retail_sales T1 11.25.56.509248 LEFT OUTER JOIN 11.25.56.509272 qwqretail/qwqretail_wf_retail_customer T2 11.25.56.509296 ON T2."ID_CUSTOMER" = T1."ID_CUSTOMER" ) 11.25.56.509320 GROUP BY 11.25.56.509344 T2."ID_CUSTOMER" 11.25.56.509368 ) /* ^^^ */ T1; 11.25.56.509656 (FOC2546) SQL SCRIPT SALESRANKED.SQL CREATED SUCCESSFULLY (BUT NOT EXECUTED) _EDATEMP/salesranked HELD AS SQL_SCRIPT (FOC2689) AGGREGATION DONE ... 11.25.56.626016 SELECT 11.25.56.626064 T4."RNKD0008", 11.25.56.626088 T2."FIRSTNAME", 11.25.56.626112 T2."LASTNAME", 11.25.56.626136 T4."SK001_TOTALSALES", 11.25.56.626160 T5."TIME_DATE_YEAR_COMPONENT", 11.25.56.626184 SUM(T1."QUANTITY_SOLD") 11.25.56.626208 FROM 11.25.56.626232 ( ( ( qwqretail/qwqretail_wf_retail_sales T1 11.25.56.626256 LEFT OUTER JOIN 11.25.56.626280 qwqretail/qwqretail_wf_retail_customer T2 11.25.56.626304 ON T2."ID_CUSTOMER" = T1."ID_CUSTOMER" ) 11.25.56.626328 INNER JOIN 11.25.56.626352 ( /* vvv */ 11.25.56.626376 SELECT 11.25.56.626400 T1."VB001_SUM_COGS_US" AS "SK001_TOTALSALES", 11.25.56.626424 T1."SK001_ID_CUSTOMER" AS "VB001_PRN_ID_CUSTOMER" 11.25.56.626448 FROM 11.25.56.626472 ( /* vvv */ 11.25.56.626496 SELECT 11.25.56.626520 T2."ID_CUSTOMER" AS "SK001_ID_CUSTOMER", 11.25.56.626544 SUM(T1."COGS_US") AS "VB001_SUM_COGS_US" 11.25.56.626568 FROM 11.25.56.626592 ( qwqretail/qwqretail_wf_retail_sales T1 11.25.56.626616 LEFT OUTER JOIN 11.25.56.626640 qwqretail/qwqretail_wf_retail_customer T2 11.25.56.626664 ON T2."ID_CUSTOMER" = T1."ID_CUSTOMER" ) 11.25.56.626688 GROUP BY 11.25.56.626712 T2."ID_CUSTOMER" 11.25.56.626736 ) /* ^^^ */ T1 11.25.56.626760 ) /* ^^^ */ T4 11.25.56.626784 ON (T4."VB001_PRN_ID_CUSTOMER" = T2."ID_CUSTOMER") ) 11.25.56.626808 LEFT OUTER JOIN 11.25.56.626832 qwqretail/qwqretail_wf_retail_time_lite T5 11.25.56.626856 ON T5."ID_TIME" = T1."ID_TIME" ) 11.25.56.626880 WHERE 11.25.56.626904 (T4."RNKD0008" <= 10) 11.25.56.626928 GROUP BY 11.25.56.626952 T4."RNKD0008", 11.25.56.626976 T2."FIRSTNAME", 11.25.56.627000 T2."LASTNAME", 11.25.56.627016 T4."SK001_TOTALSALES", 11.25.56.627040 T5."TIME_DATE_YEAR_COMPONENT" 11.25.56.627064 ORDER BY 11.25.56.627088 T4."RNKD0008", 11.25.56.627112 T2."FIRSTNAME", 11.25.56.627136 T2."LASTNAME", 11.25.56.627160 T4."SK001_TOTALSALES", 11.25.56.627184 T5."TIME_DATE_YEAR_COMPONENT" 11.25.56.627208 FOR FETCH ONLY; (FOC1400) SQLCODE IS -205 (HEX: FFFFFF33) XOPEN: 42703 (FOC1500) : [42703] Column RNKD0008 not in table T4 in *N. L (FOC1406) SQL OPEN CURSOR ERROR. : WF_RETAIL_SALES Link to comment Share on other sites More sharing options...
Douglas Lee 2 Posted July 9, 2021 Share Posted July 9, 2021 Would you show us what the output that you desire looks like, redacted as needed Link to comment Share on other sites More sharing options...
Peter Vazny Posted July 9, 2021 Author Share Posted July 9, 2021 I am not looking for a workaround or a compromise for a specific report. I was hoping that there is a technique that would allow me to push row numbering or ranking to the database. Lets say I am looking for a sum, min, max and average for each column in every 5th record of a table that has 5M records. If I am not able to push the row numbering down to the database level, WebFocus has to read 5M rows instead of 1. Link to comment Share on other sites More sharing options...
Martin Yergeau Posted July 12, 2021 Share Posted July 12, 2021 I understand that using SQL_SCRIPT output format may have advantage, but what if you hold has a SQL table instead As you stated, it may be related to your WF version or DB2 usage, but I am sure that there is a solution Link to comment Share on other sites More sharing options...
Peter Vazny Posted July 12, 2021 Author Share Posted July 12, 2021 Unfortunately it seems that SAME_DB format for DB2 on iSeries is not supported for temp tables: (FOC2624) RDBMS DOES NOT IMPLEMENT TEMPORARY TABLES OF THIS TYPE : VOLATILE for the other options it seems that: it will create a permanent synonym there is no way to specify schema and the table is placed to QGPL. Which on iSeries is not really a temp location and is accessible to anybody by default. Besides, it my hypothetical scenario, it would still have to read at least the primary key for all 5M records, row number them and save them as a table. Which is probably better than doing the aggregation on all columns, but still less ideal. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now