After upgrading to 8207.28.11, some of our sql script based hold files are now generating an error and the sql file can no longer be created.

[Todd Van Valkenburg]

We use Oracle. I took one of the fex that output to SQL_SCRIPT and got it down to one MAX function. Here is the fex:

ENGINE INT CACHE SET ONSET PAGE-NUM=NOLEADSET SQUEEZE=ONSET HTMLCSS=ON*-HOLD_SOURCE-DEFAULTH &WF_SUMMARY='Summary';-DEFAULTH &WF_TITLE='WebFOCUS Report';TABLE FILE GRADUATESCHOOL/SZRGRTRSUM MAX.SZRGRTR.SZRGRTR.SZRGRTR_TRAINING_DATE WITHIN SZRGRTR.SZRGRTR.SZRGRTR_PIDMBY SZRGRTR.SZRGRTR.SZRGRTR_PIDMON TABLE HOLD AS graduateschool/graduateschool_hold/GTA_Disciplinary_NEW FORMAT SQL_SCRIPTON TABLE NOTOTALON TABLE SET CACHELINES 100ON TABLE SET GRWIDTH 1ON TABLE SET ASNAMES SUBSTON TABLE SET HOLDATTRS ONON TABLE SET HOLDLIST PRINTONLYON TABLE SET STYLE *INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/combine_templates/endeflt_fso.sty,$TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $ENDSTYLEEND-RUN

When I run a SQL Trace on this fex, I get this:

Inline Messages URL390 2022-08-30_13.31.06.933(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...(FOC2689) AGGREGATION DONE ...13.31.06.859000 SELECT13.31.06.859000 T1."SZRGRTR_PIDM",13.31.06.859000 MAX(T1."SZRGRTR_TRAINING_DATE")13.31.06.859000 FROM13.31.06.859000 SATURN.SZRGRTR T113.31.06.859000 GROUP BY13.31.06.859000 T1."SZRGRTR_PIDM"13.31.06.859000 ORDER BY13.31.06.859000 T1."SZRGRTR_PIDM";GRADUATESCHOOL/GRADUATESCHOOL_HOLD/gta_disciplinary_new HELD AS DATREC FILE10 NUMBER OF RECORDS IN TABLE= 8142 LINES= 8142

If I take the SQL and run it against our Oracle DB, I get results and the number of records matches what I see in WF.

SELECT t1."SZRGRTR_PIDM", MAX (t1."SZRGRTR_TRAINING_DATE")

FROM saturn.szrgrtr t1

GROUP BY t1."SZRGRTR_PIDM"

ORDER BY t1."SZRGRTR_PIDM";

So I was wondering if someone can help me understand why WebFOCUS cannot generate the sql_script. This worked (well, a much more complicated fex) in 8202. Could there have been a setting/config change our WF server during the upgrade that is impacting this? Or is this FOCUS code tightening?

Thanks!

[Todd Van Valkenburg]

Just following up on this question. I created a case with TIBCO. Unfortunately, TIBCO could not confirm with certainty when the WITHIN stopped working with SQL_SCRIPT output type and Oracle RDMS. I searched release notes as well as the TIBCO rep but could not find anything related to this. So we will rewrite these hold files where possible so that the first BY correlates with the MAX (or MIN). Guessing some code tightening since 8202.

[David Beagan]

In WebFOCUS 9.0, I just tried using the WITHIN on a SQL Server table and received a similar message for creating a SQL_SCRIPT hold file:

(FOC2629) REQUEST IS IMPOSSIBLE TO CONVERT TO SQL (SEE SQLAGGR TRACE)