Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] COMPUTE in Alert Test
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] COMPUTE in Alert Test
 Login/Join
 
Member
posted
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
 
Posts: 3 | Location: Dallas, TX | Registered: May 28, 2019Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1569 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2165 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] COMPUTE in Alert Test

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.