on 10-29-2007 10:32 PM
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
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,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
109 | |
15 | |
10 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.