decimal places in Query generator

[Former Member]

Hi All,

I am observing a weird behavior in Query generator's execution of a simple sql query. the query is :

Select 0.002834

now, in the general settings - display - amounts , I put the decimal value as 4 or 6, then only it prints 0.0028 or 0.002834.

else it prints 0.00 (if the decimal place is 2). it should ideally have nothing to do with the display of amount. any idea?

(the effect is shown only after you change the decimal display, update it and close and reopen the SAP application.

Thanks,

Binita

Edited by: Binita Joshi on Apr 12, 2010 4:03 PM

[Former Member · Accepted solution]

Dear Binita,

You can try converting your sql result to char like this:

Select CAST(0.002834 AS CHAR)

Regards,

Nghia

[Former Member · Accepted solution]

Binita

in 2007 version there is an option for decimal places in query result in the same place where the decimals can be configured by the rounding engine of SAP B1.

If it is not fit, you can use cast/convert functions to display the result as chars,

By the way, only the matrix items is supports this settings.

Regards,

J

[Former Member · Accepted solution]

Thanks for the reply to all. I have tried converting it to numeric(19,4) and numeric(19,6) but still the result was same. I am transferring this value through an FMS query to a measurement type column of matrix. if i type down 0.00234, it displays correct.but if the same thing is getting evaluated in query generator, it depends on the display setting of decimal of 'Amount' in general settings. i dont understand why. Janos, can you elaborate more?

Thank you.

[Former Member · Accepted solution]

Hello Binita,

>I have tried converting it to numeric(19,4) and numeric(19,6) but still the result was same.

Check your decimal places settings in

\Administration\System Initialization\General Settings\Display tab

Check the following values:

- Units (For meaurement)

- Decimals in Query

Now let' s say,

- Unit set to 6 decimal places

- Decimals in Query set to 2

run the following query in query generator:

select cast(0.000245 as decimal(19,6)) 
result will be
0.00

run the same query as FMS on item master data in any measurement field (lenght)

select cast(0.000245 as decimal(19,6)) 
result will be
0.000245

Now set the - Decimals in Query set to 6 on \Administration\System Initialization\General Settings\Display tab

run the following query in query generator:

select cast(0.000245 as decimal(19,6)) 
result will be
0.000245

This is a normal habit of SAP B1 rounding engine / displaying engine.

Regards

J.

[Former Member · Accepted solution]

Hi,

If you convert the results to a string it will always return 6dp irregardless of the decimal setting:

SELECT STR(0.000234,19,6)

However, if the field you are trying to apply the formatted search to is set to display a different level of DP then you may have a problem. If its set to 6 then it shouldn't be a problem.

Regards,

Adrian

[former_member631450 · Accepted solution]

Hi,

Update 2021, it really works to me, thank you so much!!

cast(T1.Price as char) as 'Price' --Now it shows 5 places after comma.

[matheus-silva · Accepted solution]

The client of the company I work for wanted to display an exchange rate column with 4 decimal places, I even thought about changing the SAP query manager settings to always have 4 decimal places, but for the value columns it wouldn't work, so I used your suggestion and some more hana sql functions, I did it like this: REPLACE(SUBSTRING(CAST(ORTT."Rate" AS CHAR),0,6),',',','), I used REPLACE because I live in Brazil, and here the decimal point is indicated by the comma

[Former Member]

Hi Binita,

This is by B1 definition. Query generator will only show the decimal places based on your setting.

Thanks,

Gordon