Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] (WHERE TOTAL xxx AND WHERE xxx) OR (WHERE xxx) results in a resounding thud.
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] (WHERE TOTAL xxx AND WHERE xxx) OR (WHERE xxx) results in a resounding thud.
 Login/Join
 
Master
posted
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,



 
Posts: 972 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 165 | Location: Detroit Metro | Registered: September 17, 2003Reply With QuoteReport This Post
Master
posted Hide Post
Not happy with either one.

J.



 
Posts: 972 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Master
posted Hide Post
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.



 
Posts: 972 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Master
posted Hide Post
The original logic throws --

0 ERROR AT OR NEAR LINE 15 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC262) UNBALANCED PARENTHESES
(FOC009) INCOMPLETE REQUEST STATEMENT


J.



 
Posts: 972 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
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.
 
Posts: 427 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Master
posted Hide Post
Jack's where statements appear mathematically equivalent and work like a dream. Thanks bud!

J.



 
Posts: 972 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

 
Posts: 1952 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] (WHERE TOTAL xxx AND WHERE xxx) OR (WHERE xxx) results in a resounding thud.

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.