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,

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

Normally yes but since we are referencing computed statements directly from the synonym, this is how we are directed to use them.

Ditto.

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

Try removing the COMPUTE

Can you post the synonym ?

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.

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?

I still get the same error . 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?

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?

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.

Another option may be to reference a field in all segments that the computes use.

Pure guess, but who knows.....

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.

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

 

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.

Gotcha. Well thank you Babak. I appreciate all of the information that you provided.

Thank you Waz as well.