#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2015
    Posts
    9
    Rep Power
    0

    Order by - decimals help


    Code:
    select  sum(woitem.qtytarget) as total, wo.num, sysuser.username
    from woitem
    join wo
    ON wo.id = woitem.woid
    Join moitem
    on moitem.id = woitem.moitemid
    Join mo
    ON mo.id = moitem.moid
    LEFT JOIN SYSUSER ON mo.userid = sysuser.id
    group by sysuser.username, num
    order by wo.num Asc
    wo.num starts with 1000:001, 1001:001, 1002:001......999:001, 998:001, 99:001

    But I need an order with displays 1:001, 2:001, 3:001,.....999:001, 1000:001, 1001:001, 1002:001
  2. #2
  3. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,109
    Rep Power
    2010
    The below suggestion is purely just an idea, I haven't work with the firebird database.
    Code:
    ORDER BY CAST(LEFT(wo.num, POSITION(':' in wo.num)) as integer) ASC
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2015
    Posts
    9
    Rep Power
    0
    It didnt work. Well, below written code worked.

    ORDER BY CAST( REPLACE( wo.num, ':', '.') as decimal(12, 3))

    Comments on this post

    • MrFujin agrees : As long you got it to work, then all is fine. :)
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    74
    Rep Power
    9
    review your GROUP BY order

    - GROUP BY num, name
    not
    - GROUP BY name, num

    ORDER BY accept this way

    -ORDER BY fields1, fields2, ....
    -ODER BY 1, 2 ....

    my advice first do it and see resulted each select and use the JOIN in each resulted in the end implement the final code sql

    search in firebirdsql.orrg FAQ and see how to do subselect or "CTE" - Common Table Expression
    Last edited by emailx45; August 30th, 2015 at 05:18 PM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    74
    Rep Power
    9
    dont forget:

    nfield + NULLfield = NULLresulted

    see COALESCE funcion
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2015
    Posts
    9
    Rep Power
    0
    The total quantity in the result set is showing more than 6 numbers after the decimal point (example:442.2565485). How can I limit it to 2. (example:442.25)
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    74
    Rep Power
    9
    you can create one domain as
    numMoney(18,2) then all resulted with this domain will be rounded to 2 decimals


    or

    to use CAST() function - see the help to converter one type to another

    FIREBIRDSQL.ORG FAQs
    or another source

    Tip: create one variable type domain above with initial value = 0.00 can work too.

    when divide (in percentual resulted) do:
    example

    nValue = 0.00 -> numeric(18,2)
    nPercentual = 0.00 -> numeric(5,2)

    1560 - 15% = ?

    nValue - ( nValue * (nPercentual / 100))
    Last edited by emailx45; September 1st, 2015 at 08:01 AM.

IMN logo majestic logo threadwatch logo seochat tools logo