Focal Point
[SOLVED] (WHERE TOTAL xxx AND WHERE xxx) OR (WHERE xxx) results in a resounding thud.

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/5641095703

November 17, 2008, 03:03 PM
John_Edwards
[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 (...


Regards,
Dave

http://www.daveayers.com

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


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
November 17, 2008, 04:50 PM
j.gross
This is probably what's intended:
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