PB Newbie - Auto rounding of decimals

[Former Member]

Hi PB Gurus,

I'm trying to save into a table a decimal value, I tried to capture the variable in a messagebox before my save event, just to check if it's the same:

But when the event was triggerd, it rounds the value.

Im using PB12.0 Classic and SQL Server 2008.

the field is decimal(10,2).

TIA,

[Former Member · Accepted solution]

How do you get the value?

--> you have to use u_dw.GetItemDecimal(... (not GetItemNumber)

--> store the value in a dec-Variable (not an integer)

Best regards

Heiko

[Former Member · Accepted solution]

Hi Heiko,

I'm using editmask control with MaskDataType as decimalmask!

e.g.

dec(em_1.text)

then I save this to a table, but it saves a rounded value. not the exact value which I typed.

TIA,

[Former Member · Accepted solution]

Provide your complete code, please!

[Former Member · Accepted solution]

Hi Ryan;

  FWIW: there were a lot of issues with PB 12.0 GA release. I would first of all strongly suggest that you upgrade PB to at least v12.1 build 6518.

  The next issue might be related to your DBMS connection mechanism vs PB's DataWindow. Can you tell us the settings you have in your PB IDE/Application?

ie: SQLCA.DBMS = "????"

SQLCA.DBPARM = "????"

etc.

  Also, what is the column definition in your DB table(s) that is holding the decimal value?

Regards ... Chris

[Former Member · Accepted solution]

Hi Heiko, my code is something like this:

My SP:

ALTER procedure [dbo].[insert_mf_ded](@ded_amount dec(10,2) )

as

begin

INSERT INTO dbo.tbl_mf_ded(ded_amount)

VALUES (@ded_amount)  ;

end

In my PB:

ded_amount = dec(em_1.text)

then, I just execute the stored procedure.

TIA,

[Former Member · Accepted solution]

Hi Chris,

nothing special,

Here:

DBMS = "SNC SQL Native Client(OLE DB)"

LogPass ="pass1"

ServerName = "server1"

LogId = "id1"

AutoCommit = False

DBParm = "Provider='SQLNCLI10',Database='db1',CommitOnDisconnect='No'"

and the field is decimal (10,2).

I tried to edit it in the painter, no luck

TIA,

[Former Member · Accepted solution]

Hi Ryan,

Looking at your column Specification settings, I would suggest you to use the value 2 instead of 10 in the Decimal column. Indeed, this column figure out the number of digit used after the decimal separator (the scale), not its precision. For example, if you use a DEC(15,3) at dbms level, you will see 3 in the decimal column not 15 in the Column Specification view of the DW.

You can't see the precision in PB as it use the maximum value allowed  that is 28 digits.

Then I will suggest you to check what decimal separator you're currently using :

- on your PC, see Regional Setting in the control panel

- on the Transaction Tab page of your DbProfile used

They should both match to avoid any side effect.

At the end, follow the advice of Chris and update to last stable version/build of PB.

[Former Member · Accepted solution]

Hi Ryan;

  I just set up a test to emulate yours, as follows:

1) Create an Insert SP ...

2) Create a SP DW ....

This is where your DW seems to be wrong

3) Map the insert SP to the SP based DW ...

4) Use DW's "preview" pane to test ...

- inserted a new insert row (2x)

- added value for each row

- pressed update icon on DW's toolbar

- pressed retrieve icon

   (Retrieve & insert SP's worked as expected).

HTH

Regards ... Chris

[ricardojasso · Accepted solution]

1) Are you using a stored procedure to retrieve the values? In that case, can you show us the complete code of such stored procedure?

2) Is the value truncated in the table? You can verify this using Interactive SQL in the database painter.

[Former Member · Accepted solution]

Hi Ricardo,

1 .

CREATE PROCEDURE [dbo].[get_mf_ded]
-- Add the parameters for the stored procedure here
@srch_empid char(7)
AS
BEGIN
SET NOCOUNT ON;

SELECT empid, ded_code, ded_desc, ded_amount,
datefrom, dateto, entrydate, entryby, no_of_months from dbo.tbl_mf_ded where empid = @srch_empid
END

2.

Yes, it is truncated.

I tried to show the amount right before I save it in a table using a messagebox. The messagebox shows the exact amount(with decimal). But when saved in the table it becomes rounded off.

But if I manually execute it a DB Painter, it is working fine.

TIA,

[Former Member · Accepted solution]

Hi Chris,

My insert sp:

My column spec:

My SP based DW

Still the same(decimal rounded off).

But when I execute my insert SP in DB painter, it's working.

TIA,

[Former Member · Accepted solution]

Thank you, Patrice

I've checked my pc's regional setting and transaction tab of my DB profile.

They have the same decimal separator.

[ricardojasso · Accepted solution]

How is the argument defined in the Specify Retrieve Arguments dialog for the Stored Procedure Data Source? It should be of decimal type...

[ricardojasso · Accepted solution]

...for the INSERT stored procedure, that is.

[Former Member · Accepted solution]

My Insert SP

[Former Member · Accepted solution]

Hi Ricardo;

  FWIW: Ryan's decimal issue has nothing to do with RA's. He uses EmpID and not the Decimal column for his RA. The issue is the rounding/truncation that occurs on an Insert.

Regards ... Chris

[ricardojasso · Accepted solution]

That's correct. Wrong question...

[ricardojasso · Accepted solution]

"I'm using editmask control with MaskDataType as decimalmask!"

I'm curious why you are using an EditMask control instead of the DW control to input the value. The purpose of the SP Update DW should be to input values directly to the DW and issue an update which would execute the insert SP with the corresponding argument, shouldn't it?

So how it is that you pass the value from the EditMask control to the DW and execute the SP?

[Former Member · Accepted solution]

I just supply the value for the variable ded_amount

e.g.

ded_amount = dec(em_1.text)

then under a button clicked event, I execute the Insert SP. As long as the variable/s has value, it'll pass it in the insert SP.

[Former Member · Accepted solution]

So does it get rounded when inserted from script or datawindow or both?

[Former Member · Accepted solution]

From datawindow, Lars

TIA,

[ricardojasso · Accepted solution]

1) How is the variable ded_amount defined? It should be something like the following:

     Decimal{2} ded_amount

     ded_amount = dec(em_1.text)

2) How do you pass the variable to the sp? The correct procedure would be:

     declare InsertMfDed procedure for insert_mf_ded @ded_amount = :ded_amount

     using SQLCA;

     execute InsertMfDed;

Note that this mechanisms makes no use of the SP Insert DW...

[Former Member · Accepted solution]

Hi Ryan;

          FYI:   I just did the test over again using SQL Server (instead of ASE) and connected to SS via SNC. The test went perfectly!

  Can you try an SQL trace?

SQLCA.DBMS = "TRACE SNC"

Lets see what actually gets sent to SS!!!!  

Regards ... Chris

[arnd_schmidt · Accepted solution]

You are sending a bunch of strings (varchars) via ISQL that will be converted to their target datatypes.

This is different than the datawindow behavior where you are using the datatypes of the SP declaration.