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,
WebFOCUS Enterprise Edition Product 8.2
August 08, 2019, 12:52 PM
FP Mod Chuck
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.
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
August 08, 2019, 01:17 PM
MartinY
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
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007