Hello,
My program was running slow so in order to speed things up I added SQLMSS. However, ever since I did that I get the below error and not sure how to fix it. Before that it was working fine just running super slow.
Any idea?

TABLE FILE MSEXT_ACTIVITYPOINTER
SUM
CNT.DST.ACTIVITYTYPECODE WITHIN TABLE AS CNT_PARTYID
MIN.PARTYIDNAME
MIN.ACTIVITYTYPECODE
BY PARTYID
WHERE SCHEDULEDSTART FROM DT(20071101 00:00:00.000) TO DT(20080131 23:59:59.999);
WHERE CF_COMPANYIDNAME EQ 'BMO Nesbitt Burns Inc.';
WHERE ACTIVITYTYPECODE EQ 4201;
WHERE PARTYOBJECTTYPECODE EQ 2;
WHERE PARTICIPATIONTYPEMASK EQ 5;
ON TABLE HOLD AS #H001M1 FORMAT SQLMSS
END
0 NUMBER OF RECORDS IN TABLE= 470 LINES= 470
(FOC019) THERE ARE TOO MANY VERBS IN THE REQUEST

quote:

THERE ARE TOO MANY VERBS IN THE REQUEST

This is a known issue and is a result of

SUM
CNT.DST.xxx

The work arouund is to hold the output as a flat file and then table the flat file
and hold it in the data base format.

This will not help performance!

I do not see how holding in DB format will help, as first it brings the data down to the WebFocus
server holds it and then loads it.

The SQL translator can not translate a mult-verb load (which is what CNT.DST.xxx will create)
into SQL because SQL is second rate compared to Focus (sorry SQL guys).

The performance problem is almost certainly due to what you are doing.

Look at the extract process and try and resolve it there.

What performance advantage do you expect from HOLD FORMAT SQLMSS?

What does the generated SQL look like?

If you change your TABLE request to a simple HOLD, and then move the HOLD SQLMSS to a separate step...

-RUN
TABLEF FILE HOLD
PRINT * AND HOLD AS #H001M1 FORMAT SQLMSS
END

do you still get error msgs?

Jack, it's the CNT.DST.xxx that causes the error

I do not see any advantage of holding it in DB format.

Spend the effort optimizing the SQL generated by WebFocus which is the real issue.

Yes when I remove the CNT.DST.xxx it works, so that's the problem.

Nelly - please update your signature with what version you are on.

1) Logon to Focal Point and go into your complete profile.
2) Scroll down the page until you see the “Signature” field.
3) Fill in the signature you want to use. Here is a sample to model:

Prod: WebFOCUS 5.2.3 on Win 2K/IIS 6/ISAPI Mode

4) Once all changes are made/added, scroll down to the bottom of the page and click on the Submit button

I updated my signature.

Thanks!

This looks familiar. As I came to realize back in June, FOCUS's "WITHIN" and SQL's OVER() are generally not equivalent. But for count distinct, if the underlying column is the sort key, they are: the number of distinct values within each sort-key value is guaranteed to be 1, and the sum of those 1's over the rows of the answer-set (which is what WITHIN TABLE means in Focus) equals the count of distinct sork-key values at large (which is what "count(distinct ...) over ()" means in SQL).

So if one changes CNT.DST.ACTIVITYTYPECODE WITHIN TABLE AS CNT_PARTYID in this request to CNT.DST.PARTYID WITHIN TABLE AS CNT_PARTYID, Focus could validly generate SQL with windowing:

select
PARTYID,
count(distinct PARTYID) over (),
...
sort by PARTYID
group by PARTYID
...

which is valid in most SQL dialects -- and equivalent to the Focus code without any dependence on the WHERE clauses -- without tripping over the focus two-verb-to-single-select translation issue.

But I don't know whether the Focus engine is sharp enough to do so.

- - -

However, Francis M. already noted (op. cit.) that CNT.DST.PARTYID is off-limits here because of SQLMSS restrictions on Uniqueidentifier columns.

So it's

TABLE FILE MSEXT_ACTIVITYPOINTER
SUM
-*CNT.DST.ACTIVITYTYPECODE WITHIN TABLE AS CNT_PARTYID
  MIN.PARTYIDNAME
  MIN.ACTIVITYTYPECODE
BY PARTYID
WHERE SCHEDULEDSTART FROM DT(20071101 00:00:00.000) TO DT(20080131 23:59:59.999);
WHERE CF_COMPANYIDNAME EQ 'BMO Nesbitt Burns Inc.';
WHERE ACTIVITYTYPECODE EQ 4201;
WHERE PARTYOBJECTTYPECODE EQ 2;
WHERE PARTICIPATIONTYPEMASK EQ 5;
ON TABLE HOLD
END

TABLE FILE HOLD
SUM CNT.DST.PARTYID WITHIN TABLE AS CNT_PARTYID
  MIN.PARTYIDNAME
  MIN.ACTIVITYTYPECODE
BY PARTYID
ON TABLE HOLD AS #H001M1 FORMAT SQLMSS
END

Francis and I work together and in June we were created a similiar report but this one is more complicated, that's why the data looks fimiliar.

Yea CNT.DST.PARTYID doesn't work. I've tried that.