Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.


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.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] (WHERE TOTAL xxx AND WHERE xxx) OR (WHERE xxx) results in a resounding thud.
 Login/Join
 
John_Edwards
Virtuoso
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: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Dave Ayers
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, 2003Report This Post
John_Edwards
Virtuoso
posted Hide Post
Not happy with either one.

J.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
FrankDutch
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, 2006Report This Post
John_Edwards
Virtuoso
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: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
John_Edwards
Virtuoso
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: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Francis Mariani
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, 2005Report This Post
Frans
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: 454 | Location: Europe | Registered: February 05, 2007Report This Post
j.gross
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, 2005Report This Post
John_Edwards
Virtuoso
posted Hide Post
Jack's where statements appear mathematically equivalent and work like a dream. Thanks bud!

J.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Danny-SRL
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: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders