Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQL Decimal 7,3 proper usage and actual formats
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] SQL Decimal 7,3 proper usage and actual formats
 Login/Join
 
Gold member
posted
Hello,
I am using WebFOCUS 7.7.03. I have a sql table which has 4 decimal fields 2 defined as decimal(7,2) and 2 defined as decimal(7,3). I am updating this table via a maintain form. When I use the wizard to create the new synonym for this table, the master is created with the following formats:
FIELDNAME=FIELD1, ALIAS=Field1, USAGE=P9.2, ACTUAL=P8,
FIELDNAME=FIELD2, ALIAS=Field2, USAGE=P9.2, ACTUAL=P8,
FIELDNAME=FIELD3, ALIAS=Field3, USAGE=P9.3, ACTUAL=P8,
FIELDNAME=FIELD4, ALIAS=Field4, USAGE=P9.3, ACTUAL=P8,

When I populate a js datagrid from the database, the values are displayed correctly. When the user enters a value into a text edit box on the form, all the fields allow me to enter 9 digits including the decimal place. (IE: 12345678, 1234567.8, 12345.678). After I validate the date entered into the edit boxes re numeric and within an acceptable range, I update the js data cell with the appropriate values. The cells for Fields 1 and 2 in accept the value from the edit boxes, regardless of length and number of decimal places. The cells for Fields 3 and 4 a dialogue box stating "there are too many characters in your entry" is displayed the values are truncated to 6 digits. (IE: 123456.78, and 1234567.8 become 123456, 123.456 becomes 123.45, 12.345 remains 12.345.)
If the data in the cell is directly edited, Fields 1 and 2 limit the entry to 9 digits and Fields 3 and 4 to 6 digits. Fields 3 and 4 currently contain values between 99.99 and 999.999 and will need to allow that entry going forward. At this point, I don’t see the need for this value to be > 999.999.
I have tried several different values for USAGE and/or ACTUAL formats with no success.
Does anyone have any suggestions?
Thank you,
Deana

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


WebFOCUS 7.6.8 and 7.7.03; Windows Server 2003 R2 and Windows Server 2008 R2, respectively; Development environments - Windows Server 2003 R2 and Windows 7 Professional, respectively;
excel, html, pdf
 
Posts: 87 | Registered: March 19, 2011Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Hi Deana

Did the master file used to be USAGE=D7.2 ACTUAL D8 and when it was recreated it changed to P9.2 and P8? If so I would modify the master file back to the D format and values. Not sure if that is what you tried when changing them.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2070 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Chuck,

Thank you for your response.
When the .mas file is created by WebFOCUS, the field is defined AS USAGE=P9.3, ACTUAL=P8 for my view. In the .mas file for my table it is USAGE=P9.3, ACTUAL=P4. I have tried deleting the .mas and .acx files and use the wizard to recreate them from scratch. I rarely use the 'refresh synonym' option, because I have noticed the usage and actual values do not always get modified.

The sql definition for this field in both my view and my table is decimal(7,3). So I have never understood why the usage and actual values are different in the master files.

I do not appear to be having problems with the field definition and data modifications in the table. The problem only occurs when I am using the view.

Below is a list of the different Usage/Actual formats I remember testing.

P9.3,P4
P9.3,P8
P9.3,P9
P9.3,P10
P9.3,P11

P10.3,P8
P11.3,P8
P12.3,P8

P9.3,D8
P10.3,D8
P11.3,D8
P12.3,D8

I also tried switching the formats to A10 and then converting the field before I update the table using FTODBL and without an explicit conversion. The field gets updated, but I try to refresh the data from the database, it crashes.

Thank you,
Deana


WebFOCUS 7.6.8 and 7.7.03; Windows Server 2003 R2 and Windows Server 2008 R2, respectively; Development environments - Windows Server 2003 R2 and Windows 7 Professional, respectively;
excel, html, pdf
 
Posts: 87 | Registered: March 19, 2011Reply With QuoteReport This Post
Master
posted Hide Post
Deana
If there are only 4 fields in the grid, maybe you could compute a stack with the needed values.

For all next database.fields into stk1 -* Where database is the name of your database
compute I/i2=1;
Repeat stk1.foccount
Compute tstk(I).field1/d12.2 = database(I).field1;
Compute tstk(I).field2/d12.2 = database(I).field2;
Compute tstk(I).field3/d12.2 = database(I).field3;
Compute tstk(I).field4/d12.2 = database(I).field4;
Compute I=I+1;
endrepeat

Then reverse the computes when time to write back to the database.

Mark
 
Posts: 662 | Location: New York | Registered: May 08, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
Mark,
Thank you so much.

The fields were not the only fields in the js grid. However, I created a second js grid for the fields defined as decimal(7,3) in sql and kept the usage and actual formats assigned by the wizard (USAGE=P9.3,ACTUAL=P8). I did not move the fields with 2 decimal places to the new grid.

My data now updates and displays correctly and without the "there are too many characters in your entry" pop up box.

I still do not understand why the pop up only occurred on the fields with 3 decimal places and not 2.

Since the fields in the both grids are used in the background for data validation and are hidden, I do not need to have to display them in same control. The works perfectly for me.

Thank you,
Deana


WebFOCUS 7.6.8 and 7.7.03; Windows Server 2003 R2 and Windows Server 2008 R2, respectively; Development environments - Windows Server 2003 R2 and Windows 7 Professional, respectively;
excel, html, pdf
 
Posts: 87 | Registered: March 19, 2011Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQL Decimal 7,3 proper usage and actual formats

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.