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]Number of Decimal Places and the value

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Number of Decimal Places and the value
 Login/Join
 
dburton
Gold member
posted
I have a value from our SQL database that is decimal,19,9.

If the value is 0.002 then the field is packed with zeroes after the 2. So, in the database it is stored as 0.002000000. When I pull that value out, it comes with all the extra zeroes. Unfortunately, I have no way of knowing how many decimal places are going to be used. One value could be 0.002 and the next could be 0.2 or 0.02 and so on.

Is there a way to find out how many decimal places are being used?

Example

Value 1 = 0.002000000 -> 3 decimal places
value 2 = 0.200000000 -> 1 decimal place

Thanks.

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


WebFOCUS 7.7.03
Windows Web Server 2008
MS SQL Server 2000
Excel,CSV,PDF,HTML
 
Posts: 71 | Location: Kingston, ON | Registered: May 03, 2011Report This Post
Francis Mariani
Expert
posted Hide Post
Sure. If a value is between 0.001000000 and 0.009999999 then 3 "decimal places are being used".


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
dburton
Gold member
posted Hide Post
what happens if the value is 0.0012? That value is between 0.001000000 and 0.009999999.


WebFOCUS 7.7.03
Windows Web Server 2008
MS SQL Server 2000
Excel,CSV,PDF,HTML
 
Posts: 71 | Location: Kingston, ON | Registered: May 03, 2011Report This Post
RSquared
Guru
posted Hide Post
You can do this the long way using defines. eg.

/*
DEFINE FILE WHATIS
DIGIT9/I1=EDIT (FIELDX, '$$$$$$$$9');
DIGIT8/I1=EDIT (FIELDX, '$$$$$$$9');
.
.
.
NUM_DEC/I2=IF DIGIT9 GT 0 THEN 9 ELSE
IF DIGIT8 GT 0 THEN 8 ELSE
ETC.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Francis Mariani
Expert
posted Hide Post
Your objective is to display a numeric value without the trailing zeroes?


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
dburton
Gold member
posted Hide Post
Francis,
my objective is to count the number of decimal places being used. I need to use that in a different calculation. I use TRIM later to get rid of the trailing zeroes.

RSquared,
The problem I have had with the EDIT function is that it doesn't actually account for decimal values at all. It strips the decimal places from the value. At least that has been my experience with it.

Thanks.


WebFOCUS 7.7.03
Windows Web Server 2008
MS SQL Server 2000
Excel,CSV,PDF,HTML
 
Posts: 71 | Location: Kingston, ON | Registered: May 03, 2011Report This Post
RSquared
Guru
posted Hide Post
Dburton,

You misunderstood. I meant that you can use the defines to dtermine how many decimal places there are in the data.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
dburton
Gold member
posted Hide Post
quote:
Originally posted by RSquared:
Dburton,

You misunderstood. I meant that you can use the defines to dtermine how many decimal places there are in the data.


Ok how would I do it without using EDIT? This is where I am stuck. I understand your solution in doing it the long way, I somewhat anticipated that, but I just dont know which functions to use.

Thanks,
Dave


WebFOCUS 7.7.03
Windows Web Server 2008
MS SQL Server 2000
Excel,CSV,PDF,HTML
 
Posts: 71 | Location: Kingston, ON | Registered: May 03, 2011Report This Post
Francis Mariani
Expert
posted Hide Post
This may help:

SET CENT-ZERO = ON
-RUN

DEFINE FILE CAR
WHEELD/D19.9 = WHEELBASE / 1000;
NUMVALX1/A21 = FTOA(WHEELD, '(D19.9)', 'A21');

NUMVALX2/A9 = SUBSTR(21, NUMVALX1, 13, 21, 9, 'A9');
NUMVALX3/A9 = REVERSE(9, NUMVALX2, 'A9');
NUMVALI/I9 = EDIT(NUMVALX3);
NUMVALL/I2 =
IF NUMVALI FROM 100000000 TO 999999999 THEN 9 ELSE
IF NUMVALI FROM 10000000 TO 99999999 THEN 8 ELSE
IF NUMVALI FROM 1000000 TO 9999999 THEN 7 ELSE
IF NUMVALI FROM 100000 TO 999999 THEN 6 ELSE
IF NUMVALI FROM 10000 TO 99999 THEN 5 ELSE
IF NUMVALI FROM 1000 TO 9999 THEN 4 ELSE
IF NUMVALI FROM 100 TO 999 THEN 3 ELSE
IF NUMVALI FROM 10 TO 99 THEN 2 ELSE
1;
END

TABLE FILE CAR
SUM
WHEELD
NUMVALX1

NUMVALX2
NUMVALX3
NUMVALI
NUMVALL

BY MODEL

END


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
RSquared
Guru
posted Hide Post
Have you tried using a single define such as;

DEFINE FILE WHATEVER
DEV_NUM/I1=IF FIELDX < 000000010 THEN 9 ELSE
IF FIELDX < 000000100 THEN 8 ELSE
IF FIELDX < 000001000 THEN 7 ELSE
and so on.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Francis Mariani
Expert
posted Hide Post
Doesn't work for trailing zeros.


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
dburton
Gold member
posted Hide Post
Both solutions worked. I could use the solution from RSquared and trim the trailing zeroes.

Although, I did use your solution Francis. It worked really well.

Thank you both.


WebFOCUS 7.7.03
Windows Web Server 2008
MS SQL Server 2000
Excel,CSV,PDF,HTML
 
Posts: 71 | Location: Kingston, ON | Registered: May 03, 2011Report This Post
Francis Mariani
Expert
posted Hide Post
I'm glad you figured out the "reverse engineering"!


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
dburton
Gold member
posted Hide Post
quote:
Originally posted by Francis Mariani:
I'm glad you figured out the "reverse engineering"!


Hey Francis,

I did figure out the reverse engineering. Smiler It works well. I now have a new problem that just came to my attention. I use your method twice. Once for decimal values and the other for anything greater than zero. With that being said, I have taken out the Reversing function. Now, if my result is Greater Than 999 it doesn't count the number digits.

Any suggestions?

Thanks,

Dave

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


WebFOCUS 7.7.03
Windows Web Server 2008
MS SQL Server 2000
Excel,CSV,PDF,HTML
 
Posts: 71 | Location: Kingston, ON | Registered: May 03, 2011Report This Post
dburton
Gold member
posted Hide Post
I finally figured it out. Smiler

It was because any value greater than 999, it would insert a comma. I just used the STRIP function to strip the comma from the number and then do the comparison. That did the trick.

Thanks.


WebFOCUS 7.7.03
Windows Web Server 2008
MS SQL Server 2000
Excel,CSV,PDF,HTML
 
Posts: 71 | Location: Kingston, ON | Registered: May 03, 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]Number of Decimal Places and the value

Copyright © 1996-2020 Information Builders