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.
Why am I only getting whole dollars out of my fex? It doesn’t make any Cents, get it? I have a sql pass-through fex (see below). I’m using sales_d/D12.2 at the output print.
For sales_d, it should be 1.99, but I getting 2.
ENGINE SQLINF SET DEFAULT_CONNECTION tagus
SQL SQLINF PREPARE SQLOUT FOR
select a12.manufacturer_id,
a12.department_id,
a12.category_id,
a11.store_id,
sum(a11.sales_d) AS sales_d,
sum(a11.sales_sngl_u) AS units
from f_wlsr_data AS a11,
p_item AS a12
where a11.item_id = a12.item_id
and a12.manufacturer_id = 'U0248'
and a11.store_id = 10015
group by a12.manufacturer_id,
a12.department_id,
a12.category_id,
a11.store_id;
END
TABLE FILE SQLOUT
PRINT
manufacturer_id
department_id
category_id
store_id
sales_d/D12.2
units/I7
This message has been edited. Last edited by: Kerry,
WebFOCUS 7.6.11, Windows XP, Excel, HTML, PDF
Posts: 25 | Location: Salt Lake City | Registered: June 03, 2008
What is the format of sales in the definition of f_wlsr_data? SQL passthru will not change the format unless you specify that in your SQL.
What about your data? Can you just pull out the sales column together with the grouping columns - but do not sum or group - so that you can see the raw data prior to summation and without reformatting? It could be that, with rounding, the summation of the data comes to 1.997 which would be displayed as 2.00 when the format of D12.2 was applied.
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004
Tony: DataType=Float/22. Even without Grp/Sum, WebFocus rounds to 2.00 In another tool, the same query brings back 1.99.
I thought by changing the master file field from ACTUAL=D8 to D8.2 would work, but it didn't. FIELDNAME=SALES_D, ALIAS=sales_d, USAGE=D20.2, ACTUAL=D8.2,MISSING=ON
Clif: I tried setting the precision within the fex.... ENGINE SQLINF SET CONVERSION FLOAT PRECISION 12 2 (also tried 22 2). But setting the precision didn't make any difference.
Thanks to both of you for responding.
WebFOCUS 7.6.11, Windows XP, Excel, HTML, PDF
Posts: 25 | Location: Salt Lake City | Registered: June 03, 2008
I thought by changing the master file field from ACTUAL=D8 to D8.2 would work, but it didn't.
It would not work anyway, since you are using SQL passthru which gives you "direct" access to the database tables therefore skipping any WebFOCUS metadata defined on them.
Is there any way you can put your query inside of a database view and create a masterfile on the latter instead? That would give you some extra control on how you want to use your fields by tweaking the masterfile.
SQL SQLINF
select a12.manufacturer_id,
a12.department_id,
a12.category_id,
a11.store_id,
sum(a11.sales_d) AS sales_d,
sum(a11.sales_sngl_u) AS units
from f_wlsr_data AS a11,
p_item AS a12
where a11.item_id = a12.item_id
and a12.manufacturer_id = 'U0248'
and a11.store_id = 10015
group by a12.manufacturer_id,
a12.department_id,
a12.category_id,
a11.store_id;
TABLE ON TABLE HOLD AS HOLD_DATA
END
? HOLD HOLD_DATA
-EXIT
Can you post the description of the HOLD file as it'll appear in your browser? I'm just interested to see.
Of course, this all raises a question ... why are you using a SQL passthru for such a basic query? Can't you create WF synonyms on both f_wlsr_data and p_item and run your request in "standard" WebFOCUS code?
I would still like to understand why SQL passthru does not seem to be keeping the native data type as defined in your Informix database table.
njsden, Thanks for responding. To answer your question: "why are you using a SQL passthru for such a basic query?" A: This is a test sample before pulling in more complex queries already written in sql (several). Better not to reinvent the "SQwheeL". Anything new will be written in WF.
HOLD file description:
0 NUMBER OF RECORDS IN TABLE= 33 LINES= 33 0DEFINITION OF HOLD FILE: HOLD_DAT 0FIELDNAME ALIAS FORMAT manufacturer_id E01 A10V MISSING = ON department_id E02 I6 MISSING = ON category_id E03 I6 MISSING = ON store_id E04 I11 MISSING = ON sales_d E05 P32 MISSING = ON units E06 P32 MISSING = ON
WebFOCUS 7.6.11, Windows XP, Excel, HTML, PDF
Posts: 25 | Location: Salt Lake City | Registered: June 03, 2008
The following error was caused by changing the format syntax to: sum(a11.sales_d::decimal(22,2)) AS sales_d
(FOC1400) SQLCODE IS -201 (HEX: FFFFFF37) XOPEN: 42000 : A syntax error has occurred. L (FOC1405) SQL PREPARE ERROR. (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT BYPASSING TO END OF COMMAND
I will have to get the correct informix format syntax from my DBA and try forcing it again.
WebFOCUS 7.6.11, Windows XP, Excel, HTML, PDF
Posts: 25 | Location: Salt Lake City | Registered: June 03, 2008
Sorry I'm not an Informix SQL expert. With Informix, supposedly built-in numeric format conversions eliminate the need for the CAST function. But maybe the language does not permit use of in-line format onversion in conjunction with the SUM verb. Hopefully your DBA can give you the correct approach/syntax for converting your FLOATs to fixed DECIMAL.
WebFOCUS 7.7.05
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007
Likewise, I am not conversant with Informix but in MS SQL or Oracle SQL, even if you cannot use the equivalent of CAST with SUM in the same line, you should be able to -
select S1.manufacturer_id
, S1.department_id
, S1.catgory_id
, S1.store_id
, SUM(S1.sales)
from (select a12.maunfacturer_id
, a12.department_id
, a12.category_id
, a11.store_id
, CAST(a11.sales_d as data_type) AS sales
from f_wlsr_data a11
, p_item a12
where a11.item_id = a12.item_id
and a12_manufacturer_id = 'U2048'
and a11.store_id = 10015) S1
group by S1.manufacturer_id
, S1.department_id
, S1.catgory_id
, S1.store_id
;
Should you not?
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004
Thanks for your suggestion, however I received the following error. (FOC1400) SQLCODE IS -201 (HEX: FFFFFF37) XOPEN: 42000 : A syntax error has occurred. L (FOC1405) SQL PREPARE ERROR. (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT BYPASSING TO END OF COMMAND
--------------------------------------------- But my DBA helped me with a work-around to my ssue. By forcing the precision with a round(xxxx,2), the result is now 1.99, not 2.00. But I'm still looking for a permanent solution.
SQL SQLINF select a12.manufacturer_id, a12.department_id, a12.category_id, a11.store_id, round(sum(a11.sales_d),2) AS sales_d, sum(a11.sales_sngl_u) AS units from f_wlsr_data AS a11, p_item AS a12 where a11.item_id = a12.item_id and a12.manufacturer_id = 'U0248' and a11.store_id = 10015 group by a12.manufacturer_id, a12.department_id, a12.category_id, a11.store_id; END
As mentioned previously, I changed the master file field from ACTUAL=D8 to D8.2 would work, but it didn't. FIELDNAME=SALES_D, ALIAS=sales_d, USAGE=D20.2, ACTUAL=D8.2,MISSING=ON
I also tried setting the precision within the fex.... ENGINE SQLINF SET CONVERSION FLOAT PRECISION 12 2 (also tried 22 2). But setting the precision didn't make any difference.
The table structure for the decimal field in Informix is Float. The W/F master file is USAGE=D20.2, ACTUAL=D8.2, should either of these be set to D16.2 (*see Infx-Def below)?
*Informix Definition: FLOAT Stores double-precision floating-point numbers with up to 16 significant digits. The float-precision parameter is accepted in datatype declarations for compliance with the ANSI/ISO standard for SQL, but this parameter has no effect on the actual precision of values that the database server stores.This message has been edited. Last edited by: AFS-Skier,
WebFOCUS 7.6.11, Windows XP, Excel, HTML, PDF
Posts: 25 | Location: Salt Lake City | Registered: June 03, 2008
With Dnn.n format the underlying values will remain in the precision that they come through at and changing the data on the fly (sales_d/D12.2) is only changing the usage.
You could try specifying an actual of P12.2 and a usage of P12.2 to see what difference that makes?
As for your syntax error then you need to ratify the SQL that you used against what Informix will accept and then correct the error. But you knew that.
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004
As has previously been pointed out, since you're setting the engine to Informix, SQL passthru is used in the request - no WebFOCUS meta-data (MAS, ACX) is used.
Is it possible that the default numeric precision is overridden in the code or some profile?
quote:
How to Override Default Precision and Scale ENGINE [SQLINF] SET CONVERSION RESET ENGINE [SQLINF] SET CONVERSION format RESET ENGINE [SQLINF] SET CONVERSION format [PRECISION precision [scale]] ENGINE [SQLINF] SET CONVERSION format [PRECISION MAX]
Try adding
ENGINE SQLINF SET CONVERSION RESET
before your SQL statement to reset any overrides.
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