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] Decimal Output - Informix SQL Pass-Through

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Decimal Output - Informix SQL Pass-Through
 Login/Join
 
Member
posted
Why am I only getting whole dollars out of my fex? It doesn’t make any Cents, get it? Smiler 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, 2008Report This Post
Expert
posted Hide Post
quote:
It doesn’t make any Cents
I think the wooden spoon for that pun Roll Eyes

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, 2004Report This Post
Guru
posted Hide Post
When you use Direct Pass Thru to an RDBMS you are not using a synonym. But you can set the precison for approximate numeric types with:

ENGINE database SET CONVERSION {FLOAT|REAL} PRECISION nn [mm]

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


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Member
posted Hide Post
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, 2008Report This Post
Virtuoso
posted Hide Post
quote:
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.



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
If you run this:

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.



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



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
Member
posted Hide Post
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". Smiler 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, 2008Report This Post
Virtuoso
posted Hide Post
As Tony suggests, try changing the numeric format in your SQL code.

sum(a11.sales_d::decimal(22,2)) AS sales_d,
sum(a11.sales_sngl_u::decimal(22,2)) AS units


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Member
posted Hide Post
Dan, Thanks for responding.

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, 2008Report This Post
Virtuoso
posted Hide Post
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, 2007Report This Post
Expert
posted Hide Post
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, 2004Report This Post
Member
posted Hide Post
Tony,

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

TABLE FILE SQLOUT
PRINT *
---------------------------------------------

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, 2008Report This Post
Expert
posted Hide Post
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, 2004Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
Tony, Changing the precision of the master file's actual and usage to P12.2, did make any difference.

Francis, The command "ENGINE SQLINF SET CONVERSION RESET", did make any difference.

The only work around I have so far is; Round(xxxx,2). But again this not a permanent solution to resolve future fex issues w/informix sql pass-thrus.

Thank you to both of you for responding,
Kevin


WebFOCUS 7.6.11, Windows XP, Excel, HTML, PDF
 
Posts: 25 | Location: Salt Lake City | Registered: June 03, 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] Decimal Output - Informix SQL Pass-Through

Copyright © 1996-2020 Information Builders