August 28, 2015 at 10:17 am
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:exclamation:
August 28, 2015 at 10:21 am
chindilog (8/28/2015)
select sum(woitem.qtytarget) as total, wo.num, sysuser.usernamefrom 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:exclamation:
I'm guessing wo.num is of a non-numeric data type. Make it numeric and your order by should work as expected.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 28, 2015 at 10:31 am
Of course, you need some tweaking to make it numeric.
CREATE TABLE #SampleData( num varchar(100));
INSERT INTO #SampleData VALUES
('1000:001'),
('1001:001'),
('1002:001'),
('999:001'),
('998:001'),
('99:001'),
('1:001'),
('2:001'),
('3:001');
--This won't work correctly
SELECT * FROM #SampleData ORDER BY num
--This should work for you
SELECT * FROM #SampleData
ORDER BY CAST( LEFT( num, CHARINDEX( ':', num + ':') - 1) as int),
SUBSTRING( num, CHARINDEX( ':', num + ':') + 1, 8000)
--Or maybe this
SELECT * FROM #SampleData
ORDER BY CAST( REPLACE( num, ':', '.') as decimal(12, 3))
GO
DROP TABLE #SampleData
August 28, 2015 at 10:35 am
Its Varchar. Dont know how to change it
August 28, 2015 at 10:46 am
Luis Cazares (8/28/2015)
Of course, you need some tweaking to make it numeric.
CREATE TABLE #SampleData( num varchar(100));
INSERT INTO #SampleData VALUES
('1000:001'),
('1001:001'),
('1002:001'),
('999:001'),
('998:001'),
('99:001'),
('1:001'),
('2:001'),
('3:001');
--This won't work correctly
SELECT * FROM #SampleData ORDER BY num
--This should work for you
SELECT * FROM #SampleData
ORDER BY CAST( LEFT( num, CHARINDEX( ':', num + ':') - 1) as int),
SUBSTRING( num, CHARINDEX( ':', num + ':') + 1, 8000)
--Or maybe this
SELECT * FROM #SampleData
ORDER BY CAST( REPLACE( num, ':', '.') as decimal(12, 3))
GO
DROP TABLE #SampleData
OOPS!!! I misread the : as .
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 28, 2015 at 1:25 pm
The problem here is, I am not using any tool. I am working on inventory tool, which through JDBC connects to database server. Database is called fishbowl database. Once I run query and get output, I can use the same code in designing jasper. So, this software uses firebird sql which is same as sql.
August 28, 2015 at 1:41 pm
chindilog (8/28/2015)
The problem here is, I am not using any tool. I am working on inventory tool, which through JDBC connects to database server. Database is called fishbowl database. Once I run query and get output, I can use the same code in designing jasper. So, this software uses firebird sql which is same as sql.
As it has been noted in other threads, Firebird SQL is not the same as T-SQL. Both share similarities due to the SQL standard, but each of them have different features and limitations.
Now, I posted 2 possible solutions to order as you described. I'm not sure if they worked for you. I'm not sure if Firebird uses the same functions and syntax. I won't be researching how to code for Firebird and won't test my code against that database.
August 28, 2015 at 2:56 pm
SELECT * FROM #SampleData
ORDER BY CAST( REPLACE( num, ':', '.') as decimal(12, 3))
This worked perfect. Thanks SO much.
August 31, 2015 at 10:56 am
The total quantity(sum(woitem.qtytarget) as total) 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)
select wo.num, sysuser.username,sum(woitem.qtytarget) as total
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 CAST( REPLACE( wo.num, ':', '.') as decimal(12, 3))
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply