Fexes got issues after upgrading to WebFOCUS version 9.3

[PETER PHAM]

Hi All,

 

  We are in the process of upgrading to Webfocus 9.3 version, and our fexes have had issues running in the new version due to  SQL compatibility issues as in the example below. The defined column "REQUESTED_GRP" in the BY clause causes the issue. We try to minimize the code changes in the fexes since we have many of them. Does anybody have any suggestions?

Quote

DEFINE FILE AGG_MTD_13MO_CUST_SALE
USPS_TKIT/A1 = IF CUST_GROUP EQ 'USPS' AND ITEM IN ( 'TKIT22','TKIT22FG' ) THEN 'Y'  ELSE  'N';
REQUESTED_GRP/A25 =  IF CUST_GROUP EQ 'USPS'                                                                THEN 'USPS'
ELSE IF ACCT_GROUP IN ('UC','UD') AND (NOT COST_CENTER  IN ('0011','0014','0015','0016','0017','0018','0019'))                     THEN 'UHAUL'
ELSE IF ACCT_GROUP IN ('LOC','TRSF','F100') AND (NOT COST_CENTER  IN ('0009','0011','0014','0015','0016','0017','0018','0019'))            THEN 'USLOC'
ELSE IF ACCT_GROUP EQ 'NAT' AND (NOT COST_CENTER  IN ('0009','0011','0014','0015','0016','0017','0018','0019')) AND (CUST_GROUP NE 'USPS') THEN 'USNAT'
ELSE IF ACCT_GROUP IN ('MOV','NMO') AND (NOT COST_CENTER  IN ('0009','0011','0014','0015','0016','0017','0018','0019'))                    THEN 'MOV'
ELSE IF ACCT_GROUP IN ('LOC','TRSF','F100','ARRP','MOV') AND (COST_CENTER  IN ('0009','0011','0014','0015','0016','0017','0018','0019'))   THEN 'MXLOC'
ELSE IF ACCT_GROUP EQ 'NAT' AND (COST_CENTER  IN ('0009','0011','0014','0015','0016','0017','0018','0019')) AND (CUST_GROUP NE 'USPS')     THEN 'MXNAT'
ELSE 'UNDEFINED';
END
TABLE FILE AGG_MTD_13MO_CUST_SALE
SUM
TOT_NET_SALE/D20.2CBM AS 'SALES'
COMPUTE GP/D20.2CBM = TOT_NET_SALE - TOT_COST;
COMPUTE GP_PCT/P14.2C% = IF TOT_NET_SALE EQ 0 THEN 0 ELSE (GP / TOT_NET_SALE) * 100;
BY COST_CENTER
BY ACCT_GROUP
BY CUST_GROUP
BY CUST_GROUP_DESC
BY MTH_DESC
BY USPS_TKIT
BY REQUESTED_GRP
WHERE MTH_DESC IN ('LY_MTD','TY_MTD','TY_YTD','LY_YTD')
-* WHERE REQUESTED_GRP IN ('USNAT','MXNAT')
ON TABLE HOLD AS HD1
END
-EXIT
(FOC1400) SQLCODE IS 144 (HEX: 00000090) XOPEN: 42000

(FOC1500)  :  Microsoft OLE DB Provider for SQL Server: [42000] Cannot use an
(FOC1500)  :  aggregate or a subquery in an expression used for the group by list of
(FOC1500)  :  a GROUP BY clause. [42000] Cannot use an aggregate or a subquery in an
(FOC1500)  :  expression used for the group by list of a GROUP BY clause. [42000]
(FOC1500)  :  Cannot use an aggregate or a subquery in an expression used for the
(FOC1500)  :  group by list of a GROUP BY clause. [42000] Cannot use an aggregate or
(FOC1500)  :  a subquery in an expression used for the group by list of a GROUP BY
(FOC1500)  :  clause. [42000] Statement(s) could not be prepared. [] Deferred prepare
(FOC1500)  :  could not be completed.
L    (FOC1406) SQL OPEN CURSOR ERROR.  : AGG_MTD_13MO_CUST_SALE

 

Thank you.

Peter

[David Beagan]

Is it the same SQL Server, same release, that works in the old WebFOCUS version but is now erroring with the new WebFOCUS 9.3 ?

[PETER PHAM]

Hi David. It's the same SQL Server and release. The code still works in the old version of WebFOCUS. Besides, we've just had a fresh installation of WebFOCUS on a new server.

[David Beagan]

There is both an MSODBC adapter and an SQLMSS adapter. I wonder if there could be a difference between the two?

[David Briars]

Along the lines of checking to make sure each instance of the reporting server is using the same adapter type, if the adapter types are the same, then check to make sure that that the MSSQL drivers used are the same.  

If you do an SQL trace, is the SQL generated the same by either release of WF?  

Quote

...Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause...

Looking at your code, and the MSSQL error message, what if you?:

1. Moved the REQUESTED_GRP from a DEFINE FILE to a COMPUTE.
2. Changed the BY REQUESTED_GRP to BY TOTAL REQUESTED_GRP.

...or moved the creation and sorting of/by REQUESTED_GRP to a subsequent step, after the extract from MSSQL? 

Guessing from just looking at your code, you might get the results you need, and avoid passing REQUESTED_GRP to an SQL GROUP BY clause. 

Edited November 21 by David Briars

[Brent Mason]

Maybe it's the hold file format?  That's just a guess, but it's easy to test.  I believe the default is BINARY, but you could try the following:

• ALPHA
• SQL_SCRIPT
• DATREC

[Brent Mason]

Sorry, I should have included the relevant documentation in my previous post.

https://docs.tibco.com/pub/wf-wf/9.3.0/doc/pdf/IBI_wf-wf_9.3.0_cr_language.pdf#page=551

https://docs.tibco.com/pub/wf-wf/9.3.0/doc/pdf/IBI_wf-wf_9.3.0_cr_language.pdf#page=598

 

[PETER PHAM]

23 hours ago, David Briars said:

Along the lines of checking to make sure each instance of the reporting server is using the same adapter type, if the adapter types are the same, then check to make sure that that the MSSQL drivers used are the same.  

If you do an SQL trace, is the SQL generated the same by either release of WF?  

Looking at your code, and the MSSQL error message, what if you?:

1. Moved the REQUESTED_GRP from a DEFINE FILE to a COMPUTE.
2. Changed the BY REQUESTED_GRP to BY TOTAL REQUESTED_GRP.

...or moved the creation and sorting of/by REQUESTED_GRP to a subsequent step, after the extract from MSSQL? 

Guessing from just looking at your code, you might get the results you need, and avoid passing REQUESTED_GRP to an SQL GROUP BY clause. 

Thanks, David. I can see the codes generated by the servers are different.  The drivers are the same on both servers. Besides, we could modify the code to bypass the issue, however, we try to avoid touching the codes since we have many fexes, and we don't have enough manpower to do it now.

Edited November 22 by PETER PHAM
Update information of the drivers of servers

[David Briars]

OK, you've now determined that the SQL generated by the adapters of each of two WF releases is different when running the same exact TABLE FILE command.  

You don't say how the two sets of SQL SELECTs are different but guessing from the error message... The WF Release 9.3 scenario has an aggregate function in the GROUP BY - 'GROUP BY SUM(some field)'. 

To fix this, not sure if you have many other choices, other than a code change so that an aggregate function isn't plunked down in the GROUP BY clause.

Edited November 25 by David Briars

[PETER PHAM]

TIBCO support(Nethaji E) gave us a hotfix to resolve the issue. Below are the instructions for applying the hotfix.

(FOC1500)  :  Cannot use an aggregate or a subquery in an expression used for the
(FOC1500)  :  group by list of a GROUP BY clause. 

 

Found that the error has been resolved after applying the WebFOCUS Reporting Server 9.3.0 HF002. 

 

This hotfix addresses the issue you have reported – WF 93 - SQL created when NOT IN or NE in DEFINE has an error. 

 

You can refer to Release Announcement Number 000053733 - https://support.tibco.com/s/article/ibi-WebFOCUS-Reporting-Server-9-3-0-HF-002-is-now-available - for complete details about this hotfix. 

 

For instructions on how to download and/or access all GA hotfixes, refer to Article Number 000022290 - https://support.tibco.com/s/article/hotfix.