I have been trying to code below into webfocus. SELECT SUM(F...

[srajeevan .]

I have been trying to code below into webfocus.

SELECT

SUM(FIELD1) over (partition by FIELD2, FIELD3, FIELD4 ) AS SUM_FLD,

COL2,COL3,COL4,COL5

FROM DEMOTBL

TABLE FILE DEMOTBL

SUM

FIELD1

BY FIELD2

BY FIELD3

BY FILED4

PRINT

COL2

COL3

COL4

COL5

BY FIELD2

BY FIELD3

BY FILED4

END

 

I use SUM and BY to achieve partitioning. But this forces me to use BY fields for the PRINT fields as well(which i dont want to). Is there any other functions/alternatives to achieve partitioning other than sql pass through solutions

[David Beagan]

sreejith.rajeevan:

 

But this forces me to use BY fields for the PRINT fields as well(which i dont want to).

 

 

Note that the PRINT BY fields only affect the sort order of the output rows, not grouping or partitioning.

Are you getting an incorrect or undesired output

[Brian Suter]

It is unclear what you want - in SQL there is groupby which is done on your selected columns (and the rest of the columns need to have aggregation functions. the BY clauses in table command perform a groupby and a sortby. TABLE has one other attribute that it can build multiple answer sets and merge them on output - which is what you have. The first answer set (the sum) will have one row for each unique combination of field2,field3,field4 with the sum of field1. The second answer set is all source rows with the values of col2, col3, etc - and then the output is merged with the first on values from FIELD2,3,&4. If yu do an on table hold as bla and then table file bla print * you will see the answer set.

[srajeevan .]

If i remove the BY fields along with PRINT i get the below error which is why i was looking for a way to implement sql partition as shown in the main post.

**(FOC023) 'BY' FIELDS FROM PREVIOUS VERB ARE MISSING**

Here is an example using CAR file.

TABLE FILE CAR

SUM

SALES

BY COUNTRY

BY MODEL

PRINT

MODEL

BODYTYPE

SEATS

BHP

RPM

MPG

END

Here i dont apply the by fields to the PRINT from the first verb and it generates the error mentioned above.

I dont want to apply sort by/group by as it skews the results.

[Martin Yergeau]

As mentioned by Brian, your first sample code is like if you have created two requests and have them merged together. But this is done in one step using the multiverb option of WF.

In you second sample code (with the error) you are trying to perform a multiverb (SUM & PRINT) request but without the necessary grouping elements.

Meaning that you cannot do it without BY fields in the second part of the code because this is how WF know how to group the multiverb result together.

It has to be at least the same BY fields as in first part (SUM section).

[Martin Yergeau]

Except if we misunderstood your need, below sample is giving the same data result

-*-* Using WF code

TABLE FILE WF_RETAIL_LITE

SUM QUANTITY_SOLD

BY BRANDTYPE

SUM QUANTITY_SOLD

BY BRANDTYPE

BY PRODUCT_CATEGORY

WHERE TIME_YEAR IN (2016, 2017);

ON TABLE SET PAGE-NUM NOLEAD

END

-RUN

 

-*-* Using SQL passthru

ENGINE SQLMSS SET DEFAULT_CONNECTION Con_XYZ

 

SQL SQLMSS PREPARE SQLOUT FOR

SELECT DISTINCT BRANDTYPE,

PRODUCT_CATEGORY,

SUM(QUANTITY_SOLD) OVER (PARTITION BY BRANDTYPE) AS 'TOTQTYBRAND',

SUM(QUANTITY_SOLD) OVER (PARTITION BY BRANDTYPE, PRODUCT_CATEGORY) AS 'TOTQTYPROD'

FROM wrd_wf_retail_sales S

INNER JOIN wrd_wf_retail_product P

ON S.ID_PRODUCT = P.ID_PRODUCT

INNER JOIN wrd_wf_retail_time T

ON S.ID_TIME = T.ID_TIME

WHERE T.TIME_YEAR IN (2016, 2017)

ORDER BY BRANDTYPE,

PRODUCT_CATEGORY

END

-RUN

 

TABLE FILE SQLOUT

PRINT TOTQTYBRAND/P9C AS 'TotQtyBrand'

TOTQTYPROD/P9C AS 'TotQtyProd'

BY BRANDTYPE

BY PRODUCT_CATEGORY

ON TABLE SET PAGE-NUM NOLEAD

END

-RUN

[David Beagan]

I dont think there is anything better than this multi-verb feature to accomplish partitioning. Even though you are required to have the BY fields after the PRINT, it seems like we could make this work. The BY fields after the PRINT only affect the output in terms of the ordering of the output rows. So I tried an example.

WebFOCUS supports SQL reporting that is not SQL Passthru. In WebFOCUS 8.2.07 I found that it supports partitioning.

SQL

SELECT SUM(SALES) OVER (PARTITION BY COUNTRY,MODEL) AS SALES,

COUNTRY,MODEL,BODYTYPE, SEATS, BHP, RPM, MPG

FROM CAR

END

 

 

I can exactly replicate this result with multi-verb and a hold file:

TABLE FILE CAR

SUM SALES

BY COUNTRY

BY MODEL

PRINT MODEL

BODYTYPE

SEATS

BHP

RPM

MPG

BY COUNTRY

BY MODEL

ON TABLE HOLD AS RESULT

END

 

TABLE FILE RESULT

PRINT SALES COUNTRY MODEL BODYTYPE SEATS BHP RPM MPG

END

 

When run produces the same output as above.

[Steven Hall]

Have you looked into PARTITION_AGGR

Docs:

https://webfocusinfocenter.informationbuilders.com/wfappent/TLs/TL_server/source/partition_aggr.htm

Function syntax:

PARTITION_AGGR(aggregation.field, reset_key TABLE, lower, upper, post_aggregation)

Function description:

Operations over groups of lines

 

 

 

 

Parameter name

Parameter type

Parameter description

Parameter available value

Parameter value description

 

 

 

 

aggregation

Keyword

Is the type of aggregation.

 

 

 

 

 

 

 

SUM.

SUM

 

 

 

 

 

MAX.

MAX

 

 

 

 

 

MIN.

MIN

 

 

 

 

 

CNT.

CNT

 

 

 

 

 

AVE.

AVE

 

 

field

any relevant

Is the name of the input field.

 

 

 

 

reset_key

any relevant

Is the name of the BY field which specifies the partition boundary (or full TABLE).

 

 

 

 

lower

 

Is the starting point in the partition for the post aggregation (type B or -1 or -N).

 

 

 

 

upper

Constant

Is the end point in the partition for the post aggregation.

C

C - Current row in the partition

 

 

 

 

 

E

E - End of the sort group

 

 

post_aggregation

Constant

Is the post aggregation action.

AVE

AVE

 

 

 

 

 

SUM

SUM

 

 

 

 

 

CNT

CNT

 

 

 

 

 

MIN

MIN

 

 

 

 

 

MAX

MAX

 

 

 

 

 

FST

FST

 

 

 

 

 

LST

LST