How should I set up a COMPUTE field in an Alert test? Similarly, is it possible for an Alert Test to filter by a COMPUTE criteria?

Ultimately, I would like the Alert Result report to be sent if the Alert Test shows that the Total Revenue from the prior week is >2% from 13 Week Moving Average. Here is my code for the Alert Test:

ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
SET SQUEEZE=ON
-DEFAULTH &WF_HTMLENCODE=ON;
SET HTMLENCODE=&WF_HTMLENCODE

SET HTMLCSS=ON
-DEFAULTH &WF_EMPTYREPORT=ON;
SET EMPTYREPORT=&WF_EMPTYREPORT

-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE transmetric/vwrevdetail
SUM VWREVDETAIL.VWREVDETAIL.TOTALREV
COMPUTE 13WeekAvg/D12.2=PARTITION_AGGR ( VWREVDETAIL.VWREVDETAIL.TOTALREV , TABLE , -13 , C , AVE) ;
COMPUTE PercentDifference/D12.2%=((VWREVDETAIL.VWREVDETAIL.TOTALREV - 13WeekAvg)/13WeekAvg)*100;
BY VWREVDETAIL.VWREVDETAIL.PERIODENDING
WHERE VWREVDETAIL.VWREVDETAIL.PERIODENDING EQ &&PRIORSAT;
WHERE TOTAL PercentDifference GT 2;
-*WHERE TOTAL VWREVDETAIL.VWREVDETAIL.TOTALREV GT 10000000;
ON TABLE HOLD
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
END

When I run the Alert, it produces the following error:

An error has occurred
Detail:
(FOC1400) SQLCODE IS 102 (HEX: 00000066) XOPEN: 42000

(FOC1500) : Microsoft OLE DB Provider for SQL Server: [42000] Incorrect syntax near
(FOC1500) : 'ROWS'. [42000] Statement(s) could not be prepared. [] Deferred prepare
(FOC1500) : could not be completed.
L (FOC1406) SQL OPEN CURSOR ERROR. : VWREVDETAIL

If I take out the COMPUTE fields and use the filter "WHERE TOTAL VWREVDETAIL.VWREVDETAIL.TOTALREV GT 10000000", the Alert runs perfectly. Please advise.

This message has been edited. Last edited by: drewtollenaar,

drewtollenaar

Because a compute is not a real database field the where total can not be sent as SQL. I think you will have to hold the results of the first query and then apply the where to the hold file.

quote:

(FOC1400) SQLCODE IS 102 (HEX: 00000066) XOPEN: 42000
(FOC1500) : Microsoft OLE DB Provider for SQL Server: [42000] Incorrect syntax near
(FOC1500) : 'ROWS'. [42000] Statement(s) could not be prepared. [] Deferred prepare
(FOC1500) : could not be completed.
L (FOC1406) SQL OPEN CURSOR ERROR. : VWREVDETAIL

You may have a connection or DB issue which may have to be fixed first.
Also where does ROWS is coming from ?

The WHERE TOTAL should work

Run your code outside the Alert to insure that it works