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
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE transmetric/vwrevdetail
COMPUTE 13WeekAvg/D12.2=PARTITION_AGGR ( VWREVDETAIL.VWREVDETAIL.TOTALREV , TABLE , -13 , C , AVE) ;
COMPUTE PercentDifference/D12.2%=((VWREVDETAIL.VWREVDETAIL.TOTALREV - 13WeekAvg)/13WeekAvg)*100;
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
When I run the Alert, it produces the following error:
An error has occurred
(FOC1400) SQLCODE IS 102 (HEX: 00000066) XOPEN: 42000
(FOC1500) : Microsoft OLE DB Provider for SQL Server:  Incorrect syntax near
(FOC1500) : 'ROWS'.  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,
WebFOCUS Enterprise Edition Product 8.2
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.
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
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
WF versions : Prod 18.104.22.168M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
|Powered by Social Strata|