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>,

quote:

the fex displays the records.

Please show us the results of each fex.

I would change the usage to D20.2.

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,

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.

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.

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.

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.