formatting numbers in sap business one query manager

[Ammammata]

rif. Loading Image...

(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

[J.O. Aho]

Post by 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?

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.

[Ammammata]

Il giorno Mon 17 Aug 2020 01:28:43p, *J.O. Aho* ha inviato su

Post by J.O. Aho
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

[J.O. Aho]

Post by Ammammata
Il giorno Mon 17 Aug 2020 01:28:43p, *J.O. Aho* ha inviato su

Post by J.O. Aho
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.

[Jerry Stuckle]

Post by 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

What about TRUNCATE(x, 0); ?

[Ammammata]

Il giorno Mon 17 Aug 2020 08:10:08p, *Jerry Stuckle* ha inviato su

Post by Jerry Stuckle
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

[Jerry Stuckle]

Post by Ammammata
Il giorno Mon 17 Aug 2020 08:10:08p, *Jerry Stuckle* ha inviato su

Post by Jerry Stuckle
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

Post by Jerry Stuckle

Post by Ammammata
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]

Post by Ammammata
Il giorno Thu 20 Aug 2020 07:44:55p, *Jerry Stuckle* ha inviato su

Post by Jerry Stuckle

Post by Ammammata
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.