August 28th, 2015, 11:08 AM
Order by - decimals help
wo.num starts with 1000:001, 1001:001, 1002:001......999:001, 998:001, 99:001
select sum(woitem.qtytarget) as total, wo.num, sysuser.username
ON wo.id = woitem.woid
on moitem.id = woitem.moitemid
ON mo.id = moitem.moid
LEFT JOIN SYSUSER ON mo.userid = sysuser.id
group by sysuser.username, num
order by wo.num Asc
But I need an order with displays 1:001, 2:001, 3:001,.....999:001, 1000:001, 1001:001, 1002:001
August 28th, 2015, 04:59 PM
The below suggestion is purely just an idea, I haven't work with the firebird database.
ORDER BY CAST(LEFT(wo.num, POSITION(':' in wo.num)) as integer) ASC
August 28th, 2015, 05:24 PM
It didnt work. Well, below written code worked.
ORDER BY CAST( REPLACE( wo.num, ':', '.') as decimal(12, 3))
Comments on this post
August 30th, 2015, 05:01 PM
review your GROUP BY order
- GROUP BY num, name
- 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.
August 30th, 2015, 05:07 PM
nfield + NULLfield = NULLresulted
see COALESCE funcion
August 31st, 2015, 11:52 AM
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)
September 1st, 2015, 07:45 AM
you can create one domain as
numMoney(18,2) then all resulted with this domain will be rounded to 2 decimals
to use CAST() function - see the help to converter one type to another
or another source
Tip: create one variable type domain above with initial value = 0.00 can work too.
when divide (in percentual resulted) do:
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.