[SOLVED] (WHERE TOTAL xxx AND WHERE xxx) OR (WHERE xxx) results in a resounding thud.
I am attempting to code selection criteria that looks like this --
Table File ABC Sum MAX.EFFECTIVE_START_ID Print . . .
(WHERE TOTAL MAX.EFFECTIVE_START_ID EQ EFFECTIVE_START_ID AND PRORATION_STATUS EQ 'L') OR DIM_PERSON.EFFECTIVE_END_ID EQ MISSING
Focus is displeased. Does anyone know how I should talk nice to Focus to get it to digest this logic?This message has been edited. Last edited by: Kerry,
November 17, 2008, 03:12 PM
Dave Ayers
john,
Perhaps focus is displeased for the same reason I'm confused by the placement of your parenthesis. ei: (WHERE... vs. WHERE (...
WebFocus/Maintain 7.6.4-8 on Win2000 and 2003 Server
November 17, 2008, 03:17 PM
John_Edwards
Not happy with either one.
J.
November 17, 2008, 03:46 PM
FrankDutch
Dave's answer is a bit cryptic...
change your where statement to
WHERE (TOTAL MAX.EFFECTIVE_START_ID EQ EFFECTIVE_START_ID AND PRORATION_STATUS EQ 'L' )
OR DIM_PERSON.EFFECTIVE_END_ID EQ MISSING
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
November 17, 2008, 03:51 PM
John_Edwards
I understood what he said, but it just changes the error --
0 ERROR AT OR NEAR LINE 15 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: TOTAL BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT
It appears that the WHERE must be up against the TOTAL in order for the parser to figure it out.
It may be that the internal matrix isn't pleased with a WHERE TOTAL and a WHERE being put in the same logic. That would be disappointing.
J.
November 17, 2008, 03:53 PM
John_Edwards
The original logic throws --
0 ERROR AT OR NEAR LINE 15 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC262) UNBALANCED PARENTHESES (FOC009) INCOMPLETE REQUEST STATEMENT
J.
November 17, 2008, 04:00 PM
Francis Mariani
TABLE FILE CAR
SUM
SALES
BY COUNTRY
BY CAR
BY MODEL
BY BODYTYPE
WHERE (TOTAL MAX.COUNTRY EQ COUNTRY AND SEATS EQ 4)
END
Remove the brackets and it works. But that doesn't help if you have subsequent OR clauses.
By the way, what does TOTAL MAX.EFFECTIVE_START_ID EQ EFFECTIVE_START_ID mean? Perhaps that clause could be coded differently. TOTAL and MAX???
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
November 17, 2008, 04:07 PM
Frans
This works:
TABLE FILE CAR SUM SALES BY COUNTRY BY CAR BY MODEL BY BODYTYPE WHERE TOTAL (MAX.COUNTRY EQ COUNTRY AND SEATS EQ 4 OR COUNTRY EQ 'ENGLAND') END
WHERE
PRORATION_STATUS EQ 'L'
OR DIM_PERSON.EFFECTIVE_END_ID IS MISSING;
WHERE TOTAL
MAX.EFFECTIVE_START_ID EQ EFFECTIVE_START_ID;
- Jack Gross WF through 8.1.05
November 17, 2008, 09:05 PM
John_Edwards
Jack's where statements appear mathematically equivalent and work like a dream. Thanks bud!
J.
November 17, 2008, 10:46 PM
Danny-SRL
John,
What happened was that you were mixing two very different WHERE statements: 1. WHERE ... 2. WHERE TOTAL ... They must be separate: WHERE acts on input records WHERE TOTAL acts on output lines
Jack's syntax effectively did that.
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF