Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Bizarre behavior using AVE

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Bizarre behavior using AVE
 Login/Join
 
jwoelfel
Member
posted
This is really strange- it has to be a bug.

I've created a define to perform some math on a physical field in my database only when a specific flag is set, otherwise the define should return NULL. For the most part, this works pretty well- the define does return the MISSING value when the flag is not set. However, if I attempt to use the AVE function, I get some strange results back.

Here is my code:

  
DEFINE FILE PD_WORKORDER_DETAILS
EXCD_APROLOS_DAYS_Q/D12.1BC MISSING ON NEEDS SOME = IF APRO_EXCEED_LOS EQ 'Y' THEN (APRO_LOS_Q - 72)/24 ELSE MISSING ;
END

TABLE FILE PD_WORKORDER_DETAILS
SUM
AVE.EXCD_APROLOS_DAYS_Q
-*CNT.EXCD_APROLOS_DAYS_Q
WHERE WORK_ORD_V.APRO_DAY GE '11292009' AND WORK_ORD_V.APRO_DAY LE '01022010'
WHERE PD_RPT_LOC_V.REF_CO_LOC_I IN (3)
END
-RUN
-EXIT


If I run the above code, I get some monsterous negative number. However, the really bizarre part is that if I uncomment the CNT.EXCD_APROLOS_DAYS_Q line, I get the correct average value of 27.6.

I tried to reproduce this using the CAR file, however the AVE function works correctly there. I suspect there is some sort of glitch in how FOCUS and the SQL Server adapter are handling the NULL values. Has anyone else run into issues getting the AVE aggregation operation to work correctly on SQL Server?

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


Web Server: iPlanet 6.0
App Server: Websphere 6.0
WFClient: 7.6.9
WFServer: 7.6.9 (Windows)
MRE & SSA
 
Posts: 20 | Location: Minneapolis | Registered: July 20, 2004Report This Post
Dan Satchell
Virtuoso
posted Hide Post
You should check the SQL trace to see how your query is being handled by the translator. Does column APRO_EXCEED_LOS have missing values?


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
GamP
Virtuoso
posted Hide Post
Also issuing the CNT. probably forces the logic to be done by WebFOCUS. For CNT it needs to know if the result is missing or not, to be able to do a correct count. An average can be calculated by the db engine, which apparently does not take the missing of the defined field into account. That's why you get these big negatives - a missing value is represented by a large negative number.
Does it also work ok if you do the CNT with a NOPRINT following it?


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Hua
Guru
posted Hide Post
quote:
WHERE WORK_ORD_V.APRO_DAY GE '11292009' AND WORK_ORD_V.APRO_DAY LE '01022010'


I find it hard to believe this will give you any data. Maybe I don't understand how date comparision works in WF. Confused


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Bizarre behavior using AVE

Copyright © 1996-2020 Information Builders