1. Forum
  2. Usenet
  3. COMP.DATABASES.MYSQL

  • formatting numbers in sap business one query manager

    From Ammammata@21:1/5 to All on Mon Aug 17 09:33:02 2020
    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 ] ...........

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From J.O. Aho@21:1/5 to Ammammata on Mon Aug 17 13:28:43 2020
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ammammata@21:1/5 to All on Mon Aug 17 12:08:30 2020
    Il giorno Mon 17 Aug 2020 01:28:43p, *J.O. Aho* ha inviato su comp.databases.mysql il messaggio news:hpv7vbFhh8mU1@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


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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jerry Stuckle@21:1/5 to Ammammata on Mon Aug 17 14:10:08 2020
    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
    jstucklex@attglobal.net
    ==================

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ammammata@21:1/5 to All on Thu Aug 20 07:10:33 2020
    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

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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From J.O. Aho@21:1/5 to Ammammata on Thu Aug 20 09:24:30 2020
    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:hpv7vbFhh8mU1@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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jerry Stuckle@21:1/5 to Ammammata on Thu Aug 20 13:44:55 2020
    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?

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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ammammata@21:1/5 to All on Fri Aug 21 07:09:11 2020
    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

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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jerry Stuckle@21:1/5 to Ammammata on Fri Aug 21 15:19:10 2020
    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.

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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)