formatting numbers in sap business one query manager

[Ammammata]

rif. https://i.imgur.com/Mvp40nW.png

(ref. 4, Apr and following)
the standard output is good, but it displays two un-needed decimals (it's a
quantity)

(ref. 1, Prev & Jan)
I tried convert(int, x): decimals disappear, zeroes disappear, but the
number is left aligned

(ref. 2, Feb)
I tried format(x, '0'): decimals disappear, zeroes are visible, but - as
above - the number is left aligned

(ref. 3, Mar)
I tried format(x, 'n'): decimals are back, zeroes are visible, but - again
- the number is left aligned

how can I set the standard output to no-decimals for this query only?

TIA

--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

[J.O. Aho]

On 17/08/2020 11.33, Ammammata wrote:
> rif. https://i.imgur.com/Mvp40nW.png
>
> (ref. 4, Apr and following)
> the standard output is good, but it displays two un-needed decimals (it's a
> quantity)
>
> (ref. 1, Prev & Jan)
> I tried convert(int, x): decimals disappear, zeroes disappear, but the
> number is left aligned
>
> (ref. 2, Feb)
> I tried format(x, '0'): decimals disappear, zeroes are visible, but - as
> above - the number is left aligned
>
> (ref. 3, Mar)
> I tried format(x, 'n'): decimals are back, zeroes are visible, but - again
> - the number is left aligned
>
> how can I set the standard output to no-decimals for this query only?

you could try FLOOR(x), this will still be a numeric value while
FORMAT() returns a string, which makes your alignment to look wrong in
the program you use to display the data in.

--

//Aho

[Ammammata]

Il giorno Mon 17 Aug 2020 01:28:43p, *J.O. Aho* ha inviato su
comp.databases.mysql il messaggio news:hpv7vb...@mid.individual.net.
Vediamo cosa ha scritto:

> you could try FLOOR(x), this will still be a numeric value

yes, I gave it a try, but it keeps the decimals

SQL Server FLOOR() Function
https://www.w3schools.com/sql/func_sqlserver_floor.asp

The FLOOR() function returns the largest integer value that is smaller than
or equal to a number.

There is an system-wide option to set the number of decimals in the
queries, but I don't want it as global, just for a few of them

[Jerry Stuckle]

On 8/17/2020 5:33 AM, Ammammata wrote:
> rif. https://i.imgur.com/Mvp40nW.png
>
> (ref. 4, Apr and following)
> the standard output is good, but it displays two un-needed decimals (it's a
> quantity)
>
> (ref. 1, Prev & Jan)
> I tried convert(int, x): decimals disappear, zeroes disappear, but the
> number is left aligned
>
> (ref. 2, Feb)
> I tried format(x, '0'): decimals disappear, zeroes are visible, but - as
> above - the number is left aligned
>
> (ref. 3, Mar)
> I tried format(x, 'n'): decimals are back, zeroes are visible, but - again
> - the number is left aligned
>
> how can I set the standard output to no-decimals for this query only?
>
> TIA
>

What about TRUNCATE(x, 0); ?

--
==================
Remove the "x" from my email address
Jerry Stuckle
jstu...@attglobal.net
==================

[Ammammata]

Il giorno Mon 17 Aug 2020 08:10:08p, *Jerry Stuckle* ha inviato su
comp.databases.mysql il messaggio news:rheh6k$jqs$1...@jstuckle.eternal-
september.org. Vediamo cosa ha scritto:

>
> What about TRUNCATE(x, 0); ?
>
>

I tried it: SAP B1 gives an error so I used round(x, 0) but the result
didn't change

[J.O. Aho]

On 17/08/2020 14.08, Ammammata wrote:
> Il giorno Mon 17 Aug 2020 01:28:43p, *J.O. Aho* ha inviato su
> comp.databases.mysql il messaggio news:hpv7vb...@mid.individual.net.
> Vediamo cosa ha scritto:
>
>> you could try FLOOR(x), this will still be a numeric value
>
> yes, I gave it a try, but it keeps the decimals
>
> SQL Server FLOOR() Function
> https://www.w3schools.com/sql/func_sqlserver_floor.asp
>
> The FLOOR() function returns the largest integer value that is smaller than
> or equal to a number.

Which means you would get the value you looking for, as 11.00000 would
become 11, this avoids you to change a system wide value while you run
the query and get the issues it can give.

--

//Aho

[Jerry Stuckle]

On 8/20/2020 3:10 AM, Ammammata wrote:
> Il giorno Mon 17 Aug 2020 08:10:08p, *Jerry Stuckle* ha inviato su
> comp.databases.mysql il messaggio news:rheh6k$jqs$1...@jstuckle.eternal-
> september.org. Vediamo cosa ha scritto:
>
>>
>> What about TRUNCATE(x, 0); ?
>>
>>
>
> I tried it: SAP B1 gives an error so I used round(x, 0) but the result
> didn't change
>

What error does it give?

[Ammammata]

Il giorno Thu 20 Aug 2020 07:44:55p, *Jerry Stuckle* ha inviato su
comp.databases.mysql il messaggio news:rhmcqf$dej$1...@jstuckle.eternal-

september.org. Vediamo cosa ha scritto:

>> I tried it: SAP B1 gives an error so I used round(x, 0) but the result
>> didn't change
>>
>
> What error does it give?
>
>

syntax error, truncate does not exist in the SLQ I'm using, that's NOT
MySql but MS Sql

Msg 156, Level 15, State 1, Line 43
Incorrect syntax near the keyword 'truncate'.

My hope was to find a solution that worked on both systems

[Jerry Stuckle]

On 8/21/2020 3:09 AM, Ammammata wrote:
> Il giorno Thu 20 Aug 2020 07:44:55p, *Jerry Stuckle* ha inviato su
> comp.databases.mysql il messaggio news:rhmcqf$dej$1...@jstuckle.eternal-
> september.org. Vediamo cosa ha scritto:
>
>>> I tried it: SAP B1 gives an error so I used round(x, 0) but the result
>>> didn't change
>>>
>>
>> What error does it give?
>>
>>
>
> syntax error, truncate does not exist in the SLQ I'm using, that's NOT
> MySql but MS Sql
>
> Msg 156, Level 15, State 1, Line 43
> Incorrect syntax near the keyword 'truncate'.
>
> My hope was to find a solution that worked on both systems
>

You should be asking this in an MS SQL newsgroup. MySQL has more
extensions and is a bit laxer in allowing violations of the standard
than MS SQL is.

I'm not familiar with MS SQL so I can't help you there. But you may not
be able to find a good answer and have to do it in software.