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     [SOLVED] How use a field defined as USAGE=D20, ACTUAL=D8 in where clause

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] How use a field defined as USAGE=D20, ACTUAL=D8 in where clause
 Login/Join
 
Member
posted
A synonym (Sales) created against an Oracle view has field which is defined as follows.
FIELDNAME=AMOUNT, ALIAS=AMOUNT, USAGE=D20, ACTUAL=D8, MISSING=ON, $
In the oracle view definition, column Amount is defined as a Number (with no explicit precision specified) and has values with 2 decimal places.

But the following procedure does not returns any records.
TABLE FILE Sales
PRINT tran_date
Amount
WHERE AMOUNT EQ 275.59
END

There are records with amount=275.59 exist in the database. Also if I run the SQL generated by the fex (given below) directly through sqlplus, it returns the records.
SELECT
T1."TRAN_DATE",
T1."AMOUNT",
FROM
SALES.SALES T1
WHERE
(T1."AMOUNT") = 275.59

If I change the condition to WHERE ( AMOUNT GE 275 ) AND ( AMOUNT LT 276), the fex displays the records. This has something to do with the data format. But I am not sure how to solve it.

Any help is truly appreciated.
  
  
  
  

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 4 | Registered: January 24, 2011Report This Post
Expert
posted Hide Post
quote:
the fex displays the records.

Please show us the results of each fex.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
I would change the usage to D20.2.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
I changed the usage to D20.2 and it did not help.

Case 1: WHERE AMOUNT EQ 275.59

PAGE 1

TRAN_DATE Amount

From HTML source
< !--
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED
SELECT
T1."TRAN_DATE",
T1."AMOUNT"
FROM
SALES.SALES T1
WHERE
(T1."AMOUNT" = 275.59) AND
(T1."TRAN_DATE" BETWEEN TO_DATE('26-03-2014','DD-MM-YYYY') AND
TO_DATE('27-03-2014','DD-MM-YYYY'));
0 HOLDING HTML FILE ON PC DISK ...
1
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
0

-->

Case 2: WHERE ( AMOUNT GE 275 ) AND ( AMOUNT LE 276)

PAGE 1

TRAN_DATE Amount
2014/03/26 11:04:00 275.79
2014/03/26 11:06:00 275.59

From HTML source
< !--
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED
SELECT
T1."TRAN_DATE",
T1."AMOUNT"
FROM
SALES.SALES T1
WHERE
(T1."AMOUNT" BETWEEN 275 AND 276) AND
(T1."TRAN_DATE" BETWEEN TO_DATE('26-03-2014','DD-MM-YYYY') AND
TO_DATE('27-03-2014','DD-MM-YYYY'));
0 HOLDING HTML FILE ON PC DISK ...
1
0 NUMBER OF RECORDS IN TABLE= 2 LINES= 2
0

-->

View definition
create view sales.sales
as
select tran_date, amount /100 as amount from sales_daily;

sales_daily.amount NUMBER(8) with values stored as 27559

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


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 4 | Registered: January 24, 2011Report This Post
Member
posted Hide Post
After changing the field definition from USAGE=D20.2, ACTUAL=D8 to USAGE=D20.2, ACTUAL=P8.2, both the where conditions are returning correct records.

Could someone explain the reason for the different behaviour, even though the SQL generated behind the scenes are the same.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 4 | Registered: January 24, 2011Report This Post
Virtuoso
posted Hide Post
new2ibi: If AMOUNT is defined as NUMBER(8), then your expression AMOUNT/100 could safely be expressed as NUMBER(8,2).

Try making your view definition more explicit:

CREATE VIEW SALES.SALES AS
SELECT TRAN_DATE, CAST(AMOUNT/100 AS NUMBER(8,2))
  FROM SALES_DAILY
;


Recreate the WF synonym for it and you'll have a definition such as USAGE=P10.2, ACTUAL=P8. Your queries should accurately operate on the data rendered by the view.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
This is really to do with floating point arithmetic. Try:
DEFINE FILE CAR
PRCOST/P12.2 =  RCOST/101;
DRCOST/D12.2 =  RCOST/101;
END
TABLE FILE CAR
PRINT
PRCOST DRCOST
BY COUNTRY
ON TABLE HOLD AS PDCAR FORMAT FOCUS
END
-RUN
TABLE FILE PDCAR
PRINT COUNTRY
PRCOST/P12.8  DRCOST/D12.8
IF COUNTRY EQ ENGLAND
END

gives
COUNTRY PRCOST       DRCOST 
ENGLAND  87.90000000  87.90099010 
ENGLAND 133.57000000 133.57425743 
ENGLAND 176.73000000 176.73267327 

this shows what can happen and is actually held in floating point data and why an EQ is never a good idea on these (F or D) fields.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Member
posted Hide Post
njsden,
As you suggested, I changed the view definition and recreated the synonym. Everything makes much more sense now.

Alan,

Appreciate your example which helped me understand the differences between these two data formats.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 4 | Registered: January 24, 2011Report 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     [SOLVED] How use a field defined as USAGE=D20, ACTUAL=D8 in where clause

Copyright © 1996-2020 Information Builders