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]packed decimal to decimal/integer error

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[Closed]packed decimal to decimal/integer error
 Login/Join
 
srajeevan
Platinum Member
posted
Hi,

I am in the process of creating a report and i encountered a weird behavior with a packed decimal field.

I am calculating the sum of Hours Field(which is in packed decimal format).
When i tested the result by directly querying the Oracle database,i see different result.So i printed the results instead of taking aggregate and i see different values for packed decimal field.

Oracle db query result
  
P_ID	REF_NO		HOURS
11	D5F508D4EB18	0.17
11	007B08D4EFBB	0.08
11	25EF08D44C66	0.02
11	7AD008D505AA	4
11	95FF08D45FDE	0.03
11	C7F208D44F65	0.33
11	19FB08D45415	0.08
11	180A08D4CB65	16
11	C3FD08D4CB63	16


Webfocus Result
  
P_ID	REF_NO		HOURS
11	D5F508D4EB18	13
	007B08D4EFBB	10
	25EF08D44C66	08
	7AD008D505AA	13
	95FF08D45FDE	09
	C7F208D44F65	08
	19FB08D45415	08
	180A08D4CB65	06
	C3FD08D4CB63	06


I have never noticed this earlier when i worked with packed decimal fields.Can give a clarification?

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


WF8206,Windows 7,8,10
HTM,PDF,EXCEL
 
Posts: 229 | Location: MI | Registered: September 13, 2017Report This Post
vinodh
Gold member
posted Hide Post
can u specify the datatype of the field in the master file.

it has to do with the datatype.


WebFOCUS 8
Windows, All Outputs
 
Posts: 71 | Registered: January 23, 2018Report This Post
BabakNYC
Virtuoso
posted Hide Post
Could you please turn on the SQL trace for your WebFOCUS report and run that SQL in SQL*Plus? That's a much better test. Are you using a Reporting Object to create this report? Is there a JOIN involved?
  
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF

-*** Show SQL statements
SET TRACEON    = STMTRACE//CLIENT

-*** Show SQL generated sub-statement trace
SET TRACEON    = STMTRACE/2/CLIENT

-*** Disable the trace stamp (Date/Time etc)
SET TRACESTAMP = OFF

-*** Set trace line wrapping - # of characters
SET TRACEWRAP  = 132

-*** Activate SQL tracing
SET TRACEUSER  = ON


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
srajeevan
Platinum Member
posted Hide Post
@Vinodh. The datatype for the field in master file is P9.2 and in the Oracle database the datatype is NUMBER(7,2).
@Babak I am not using any Reporting Object,but i am using a cluster master file.I will get the SQL trace and try and see.


WF8206,Windows 7,8,10
HTM,PDF,EXCEL
 
Posts: 229 | Location: MI | Registered: September 13, 2017Report This Post
srajeevan
Platinum Member
posted Hide Post
I don't know what is wrong,the SQL trace is putting another field instead of my HOUR field in the select also a field from my where condition also in the select.

I have checked the values in oracle database for hours and there the hours are in decimals like 0.17,0.30 etc but in the corresponding master file the hours are totally different like 08,07,10,13 etc..


WF8206,Windows 7,8,10
HTM,PDF,EXCEL
 
Posts: 229 | Location: MI | Registered: September 13, 2017Report This Post
BabakNYC
Virtuoso
posted Hide Post
Just remember if you have a join (like in a cluster), unless you reference a field from the joined table, the join is ignored.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
srajeevan
Platinum Member
posted Hide Post
I am not referencing field from other joined tables.so the join should be ignored.But the SQL trace is adding another field in the generated SQL.Something is wrong.


WF8206,Windows 7,8,10
HTM,PDF,EXCEL
 
Posts: 229 | Location: MI | Registered: September 13, 2017Report This Post
BabakNYC
Virtuoso
posted Hide Post
Check the Master and Access files your WebFOCUS report is using. Are they pointing to the same Oracle Table you expect? Are there any other Master files with the same name in a different app path?

One way to test this is to create a dummy synonym for the table and since you're not using anything in the join use that dummy master file to create the report. Does that generate the right answer set? If it does, the problem is with duplicate Master files or something you've done in the Cluster.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
srajeevan
Platinum Member
posted Hide Post
Hi Babak,

Creating dummy file and building report worked.
Like you mentioned,some duplicate master file is creating trouble.

Thanks.


WF8206,Windows 7,8,10
HTM,PDF,EXCEL
 
Posts: 229 | Location: MI | Registered: September 13, 2017Report 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]packed decimal to decimal/integer error

Copyright © 1996-2020 Information Builders