Order problem in select statement with Union

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Order problem in select statement with Union

Author

Topic

jaskiew
Starting Member

4 Posts

Posted - 2007-11-13 : 11:37:11

Hi,

I am selecting a string as follows and was able to order by column vendor until I added a UNION statment. Now the order by will not work.

Here is the query:

select 'FM'+
(right(replicate(' ',6) + RIGHT(RTRIM(INVOICE),6),6)) +
' ' + '1' + LEFT(VENDOR,6) + (REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '')) + ' ' + '0112662260'+
(right(replicate(' ',10) + cast((convert(integer, (round(AMOUNT,2)* 100))) as varchar(10)),10))+ CD +
CONVERT(CHAR(6),po_num)+ ' ' + 'QPR' + ' ' + (REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '')) + '25' as column1
FROM DBO.MAC_PR_UPLOAD
UNION
select 'FM'+
(right(replicate(' ',6) + RIGHT(RTRIM(INVOICE),6),6)) +
' ' + '1' + LEFT(VENDOR,6) + (REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '')) + ' ' + '0112122127'+
(right(replicate(' ',10) + cast((convert(integer, (round(GST,2)* 100))) as varchar(10)),10))+ CD +
CONVERT(CHAR(6),po_num)+ ' ' + 'QPR' +
' ' +
(REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '')) + '25' as column1
FROM DBO.MAC_PR_UPLOAD

Sample result:

FM 34605 1CA6680111307 0112662260 4398D34605 QPR 11130725
FM 77058 1SE4611111307 0112122127 1423D34450 QPR 11130725
FM 89845 1YO2488111307 0112122127 2603D34371 QPR 11130725

and i want to order the string by characters 11 through 16.

If this is possible can someone please help me out?
Thanks.
Linda

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-13 : 11:53:12

select * from
(your query
) as t
order by cast(substring(column1,11,16) as bigint)

Madhivanan

Failing to plan is Planning to fail