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] Multi-Verb Report with Compute Statements - Receiving Error
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Multi-Verb Report with Compute Statements - Receiving Error
 Login/Join
 
Gold member
posted
Hello,

I'm currently trying to develop a multi-verb report however am running into trouble because I am using Compute statements in my code. Getting the error "THE SETS OF SORT PHRASES ARE INCONSISTENT". I tried looking this up in the forums and doesn't look like too many people have run into this. The compute statements are defined by the system and then we have to reference them as Computes.

 
TABLE FILE _ADM_STAR
SUM
     COMPUTE CNT_E_M_OFFICE_VISITS;
BY  WEBSITE
BY  ESSN
WHERE _ADM_STAR.DIMPROVIDERPOC.SPECIALTYTYPE EQ 'PCP';
WHERE TOTAL CNT_E_M_OFFICE_VISITS GE 1
SUM
    COMPUTE Admits;
    COMPUTE SUM_MEMBER_MONTH;
BY  WEBSITE
BY  ESSN
WHERE WEBSITE EQ 'chicaremanagement';
WHERE GROUPCD EQ '15044';
WHERE EMPLOYERCD EQ &EMPLOYERCD;
WHERE PLANCD EQ &PLANCD;
WHERE LEVELID EQ &LEVEL1ID;
WHERE LEVELID EQ &LEVEL2ID;
WHERE PLANTYPECD EQ &PLANTYPECD.QUOTEDSTRING;
WHERE (( &DATEFIELD GE '&STARTDATE3_LB' ) AND ( &DATEFIELD LT '&STARTDATE3' ));
WHERE ESSN NE '-1'
ON TABLE HOLD AS 'PCP_UTIL_LIST_PERIOD1_1-YEAR_LOOKBACK' FORMAT FOCUS INDEX ESSN
END
 


Does anyone have any insight into how I can get this to work? Thanks in advance!

This message has been edited. Last edited by: Brandon Andrathy,


WebFOCUS 8204
 
Posts: 64 | Registered: July 10, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Shouldn't a COMPUTE have a format and formula associated with it? I'm not seeing the usual syntax associated with COMPUTE here.

https://webfocusinfocenter.inf...rce/CommandRef31.htm


WebFOCUS 8204, Unix, Windows
 
Posts: 1617 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Gold member
posted Hide Post
Normally yes but since we are referencing computed statements directly from the synonym, this is how we are directed to use them.


WebFOCUS 8204
 
Posts: 64 | Registered: July 10, 2018Reply With QuoteReport This Post
Expert
posted Hide Post
Ditto.

Should be COMPUTE {Field Name}/{Format} = {Formula, etc} ;


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Try removing the COMPUTE

Can you post the synonym ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
Hey Waz,

If I remove the COMPUTES, I get "Referenced Object Is Not Found in MFD. when I have COMPUTE next to the fields, the WEBFOCUS recognizes it. Is there a better way to reference these fields or define them?

I looked at the synonym and the Properties of the Measure Group have an expression in the COMPUTE field there. In the EXPRESSION property is the calculation that would normally be in a COMPUTE statement. It looks like this is how we get around joining to multiple data sources under the same synonym.


WebFOCUS 8204
 
Posts: 64 | Registered: July 10, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You're referencing the COMPUTE correctly. That's how you reference a COMPUTE in a synonym. Your sort fields are in the same order. The only thing I can think of is that you have 2 different set of WHERE tests applied to the two VERBs. What happens if you comment out the first set of WHERE's?


WebFOCUS 8204, Unix, Windows
 
Posts: 1617 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Gold member
posted Hide Post
I still get the same error Frowner . Looks like even when I remove both WHERE statements I still get the error. Seems like it doesn't like using Computes. If I try using normal measures that don't have an embedded COMPUTE. It works fine. Is there possibly something I need to set in the table?


WebFOCUS 8204
 
Posts: 64 | Registered: July 10, 2018Reply With QuoteReport This Post
Gold member
posted Hide Post
Seems like it's because some of the fields attributed to the computes belong to different segments in the synonym. Is there any work around for that or just something that isn't possible?


WebFOCUS 8204
 
Posts: 64 | Registered: July 10, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Brandon,
What type of file is _ADM_STAR?
If it is a FOCUS file you may try to address the file via a lower segment:
TABLE FILE _ADM_STAR.field
where field is on a child segment. Then maybe all the fields you reference can be on a single path.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1932 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Another option may be to reference a field in all segments that the computes use.

Pure guess, but who knows.....


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Perhaps it's time to ask what you're trying to accomplish instead of trying to figure out why the code errors out. I'm at a loss what the expected result is supposed to be. Maybe there's an easier way of doing this than a multiverb.


WebFOCUS 8204, Unix, Windows
 
Posts: 1617 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Gold member
posted Hide Post
Hey Babak, here is what I'm currently trying to do. I'm trying to Limit down the MEMBER_IDs in the "ADMITS_BY_MEMBER" table that I identify in the first table "MEMBER_LIST". The only way that I have been able to get this to work is via JOIN. It takes forever to run though since I'm pulling back records for all members in the second table. I figured I could use a Multi-verb report to filter down the MEMBER_IDs right away before pulling the second data set. Is there another way to accomplish this?

 

TABLE FILE _ADM_STAR
SUM
	 COMPUTE OFFICE_VISITS;
BY  WEBSITE
BY  MEMBER_ID
WHERE TOTAL CNT_E_M_OFFICE_VISITS GE 1
ON TABLE HOLD AS 'MEMBER_LIST' FORMAT FOCUS INDEX MEMBER_ID
END

TABLE FILE _ADM_STAR
SUM
	COMPUTE ADMITS;
	COMPUTE MEMBER_ENROLLMENT;
BY  WEBSITE
BY  MEMBER_ID
ON TABLE HOLD AS 'ADMITS_BY_MEMBER' FORMAT FOCUS
END

JOIN FILE MEMBER_LIST AT WEBSITE TAG A1 TO UNIQUE
FILE ADMITS_BY_MEMBER AT WEBSITE TAG B1
WHERE A1.WEBSITE EQ B1.WEBSITE;
WHERE A1.ESSN EQ B1.ESSN;
END

TABLE FILE MEMBER_LIST
SUM
	OFFICE_VISITS
	ADMITS
	MEMBER_ENROLLMENT
BY  WEBSITE
BY MEMBER_ID
END

 


WebFOCUS 8204
 
Posts: 64 | Registered: July 10, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I doubt a multiverb will make this process any faster. If your strategy of holding the two data sets and joining them works but needs to run faster, consider caching one of the hold files. Depending on the velocity of change in your data and how up to date you need that hold file to be, you can schedule the first TABLE request to run on a schedule (e.g. nightly) so your users don't have to wait for the hold files to be created. Alternatively, if this is a RDBMS, see if you can create a Relational View that'll give you the same outcome and have the join set up in the database so, you don't have to pay for the creation of a HOLD FORMAT FOCUS file. That has a cost associated with it that'll be avoided if you could just run your requests against the RDBMS View. In general, if the data isn't constantly changing and you can live with some lag time, scheduling the hold is a better approach than creating it on the fly, particularly when you're dealing with very large data sets.


WebFOCUS 8204, Unix, Windows
 
Posts: 1617 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Gold member
posted Hide Post
Gotcha. Well thank you Babak. I appreciate all of the information that you provided.

Thank you Waz as well.


WebFOCUS 8204
 
Posts: 64 | Registered: July 10, 2018Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Multi-Verb Report with Compute Statements - Receiving Error

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