Rounding in Query Generator

[Former Member]

Hey guys,

I'm quite stumped by this one. SAP is making rounding decisions on calculated fields in the query I have no control over. SAP is set to round quantities to 4 decimals. However, if I run this query, column 1 is rounded to 4 decimals, column 2 is rounded to 2 decimals!

SELECT T0.[Quantity], T0.Quantity*1 FROM INV1 T0

How do I get column 2 to round to 4 decimals just like column 1?

Thanks,

Kevin

[former_member186095 · Accepted solution]

Hello Kevin,

Based on SAP B1 pdf file about query generator, it stated that rounding for the column that isnot part of the table field can only be 2 decimals. So, I suggest to use crystal report or another reporting tools if you think it is very important to you.

I've tested your query and although I simulated decimal in the general settings --> tab display, it never gives result as you want.

Rgds,

[Former Member · Accepted solution]

Yikes! So if I want any calculated field to read more than 2 decimals, it simply can't be done! It even works in Query Analyzer but not in B1. How frustrating!

I've replicated the problem in the demo database now with a different version as well. I added an invoice with quantity 1.1111. Quantity returns 1.1111 but Quantity*1 returns 1.11.

The client is running on 2005A SP01 Patch 16 and I tested again on 2005A SP01 Patch 22.

[former_member186095 · Accepted solution]

Hello,

As an info, I heard SAP B1 2007 query generator can perform your requirement but I am not sure yet but you can try. I have tried in PL 36 but fails too.

Rgds,

[former_member204969 · Accepted solution]

You can try to convert it to text:

SELECT T0.[Quantity], str(T0.Quantity*1,19,4) FROM INV1 T0

(But the result will be left justified.)

[Former Member · Accepted solution]

I have tried your suggestion and it still returned the same result however it prompted me to try casting the result of a calculation and this overcame the problem.

Example as follows:

declare @qty varchar(10)

set @qty = (cast(Quantity * 1.1) as char)

select @qty

When the query is run as a formatted search, the result is initially displayed at maximum precision but as the field is exited, it reverts to the precision as defined in the General settings. I have used the above code to obtain 3 decimal places for a formatted search on a BOM Quantity field.

[Former Member]

I know it's an old thread, but this is what I did to get 4 decimals: trick the system to think it's a string and then it will go through with 4 decimals.

STR(( T1.Quantity *1), 10, 4)

[former_member583013]

Kevin,

By any chance are your quantities round numbers or are they quantities with decimals

Can you give some example of your result.

I tested you quert on SBO DEMO database and it came out just fine. I got the same decimal places for both columns.

Suda

[former_member583013]

Is your SQL query the same as you have mentioned in the message.

Actually if the columns are the same / same type it should not happen.

to get 4 decimal you can use CAST(T0.Quantity AS DECIMAL(8,4)) but the problem is how the results are displayed within SAP. SAP actually uses the decimal definitions you have set in the General Settings.

If you run the same query in SQL Server > Query Manager the results would be probably different.

Suda

[Former Member]

Yea, that's the problem, this exact query was placed in the query window and the result I got was rounding to 2 decimals on column 2.

It doesn't seem to matter how I cast it and I've checked the settings in General Settings. Quantities are set to 4 decimals, everything else is set to 2 decimals. So I guess my main question is, why does SAP think that since I've added some logic to this column, the type is not the same.

Also, how do I trick SAP into thinking that the type is the same?

Thanks,

Kevin