I need to create a report that numbers the rows returned, bu...

[Peter Vazny]

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

[Manoj Chaurasia]

Peter

You might want to consider having your DB2 DBA sequence the table so you have a value in there to use directly.

[Peter Vazny]

I was hoping to be able to use it as needed for different reports and avoid creating dedicated views for a specific report.

[David Briars]

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

[Peter Vazny]

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.

[Martin Yergeau]

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

[Peter Vazny]

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

[Douglas Lee 2]

Would you show us what the output that you desire looks like, redacted as needed

[Peter Vazny]

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.

[Martin Yergeau]

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

[Peter Vazny]

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.