Select Statement - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Select Statement

Author

Topic

jamilahmed
Starting Member

18 Posts

Posted - 2009-04-07 : 07:56:41

Hallo,

I have the following select statement and trying to order it by oracle_no. However, when I execute the query it doesnt order by oracle_no. any ideas??

SELECT TOP (100) PERCENT gprdsql.TblQuarPay.prac_no, gprdsql.TblQuarPay.prac_eid, gprdsql.TblQuarPay.AmounttobePaid,
gprdsql.TblPracDetails.pay_method, gprdsql.TblPracDetails.pay_acc_num, gprdsql.TblPracDetails.pay_sort_code, gprdsql.TblPracDetails.post_code,
'GPRD' + ' ' + gprdsql.TblPracDetails.pay_method AS Paygroup, gprdsql.TblOracleNos.Oracle_no, 'GPRD' AS Source, 'GBP' AS Currency,
'0' AS Tax_Percentage, 'abc' AS Liability_account, 'xyz' AS Expense_acc,
'GPRD' + ' ' + 'Qtr' + ' ' + 'Payment ' + '0' + RIGHT(STR(dbo.Qrymthyr.Month_no), 1) + RIGHT(STR(dbo.Qrymthyr.Year_no), 2) AS Description
FROM gprdsql.TblQuarPay LEFT OUTER JOIN
gprdsql.TblPracDetails ON gprdsql.TblQuarPay.prac_no = gprdsql.TblPracDetails.prac_no AND
gprdsql.TblQuarPay.prac_eid = gprdsql.TblPracDetails.prac_eid LEFT OUTER JOIN
gprdsql.TblOracleNos ON gprdsql.TblPracDetails.pay_method = gprdsql.TblOracleNos.Pay_method AND
gprdsql.TblPracDetails.prac_eid = gprdsql.TblOracleNos.Prac_eid CROSS JOIN
dbo.Qrymthyr
ORDER BY gprdsql.TblOracleNos.Oracle_no

Thanks for any help

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-07 : 07:59:08

whats the output ?

jamilahmed
Starting Member

18 Posts

Posted - 2009-04-07 : 08:12:15

It orders by gprdsql.TblQuarPay.prac_no instead of gprdsql.TblOracleNos.Oracle_no.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 08:12:45

Works for me

SELECT		TOP (100) PERCENT
		qp.prac_no,
		qp.prac_eid,
		qp.AmounttobePaid,
		pd.pay_method,
		pd.pay_acc_num,
		pd.pay_sort_code,
		pd.post_code,
		'GPRD' + ' ' + pd.pay_method AS Paygroup,
		[on].Oracle_no,
		'GPRD' AS Source,
		'GBP' AS Currency,
		'0' AS Tax_Percentage,
		'abc' AS Liability_account,
		'xyz' AS Expense_acc,
		'GPRD Qtr Payment 0' + RIGHT(STR(q.Month_no), 1) + RIGHT(STR(q.Year_no), 2) AS [Description]
FROM		gprdsql.TblQuarPay AS qp
CROSS JOIN	dbo.Qrymthyr AS q
LEFT JOIN	gprdsql.TblPracDetails AS pd ON pd.prac_no = qp.prac_no
			AND pd.prac_eid = qp.prac_eid
LEFT JOIN	gprdsql.TblOracleNos AS [on] ON [on].Pay_method = pd.pay_method
			AND [on].Prac_eid = pd.prac_eid
ORDER BY	[on].Oracle_no

E 12°55'05.63"
N 56°04'39.26"

jamilahmed
Starting Member

18 Posts

Posted - 2009-04-07 : 08:22:34

whats the problem then?

any ideas what might causing the problem. Settings or anything particular with the view?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 08:24:56

quote:
Originally posted by jamilahmed

whats the problem then?

We can't tell since we have no access to source data.

E 12°55'05.63"
N 56°04'39.26"

jamilahmed
Starting Member

18 Posts

Posted - 2009-04-07 : 10:19:59

Ok, this is how it sorts it

127
160
171
1773
1774
1775
1776
1777
1778
1779
1780
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1795
1796
1797
184
1845
1847

As you noticed it reads 127, 160, 171 then 1773 instead of 184. Also note, oracle_no has been set as varchar. This might highlight something

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-07 : 10:32:34

order by convert(int,gprdsql.TblOracleNos.Oracle_no)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-07 : 10:50:09

this is what I have used. it helped out a lot in the situation you are mentioning

http://oreilly.com/catalog/transqlcook/chapter/ch08.html

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

jamilahmed
Starting Member

18 Posts

Posted - 2009-04-07 : 10:56:47

Thank you very much. Done the magic

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 11:22:16

why is Oracle_no varchar in first place if it stores only numeric data?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 13:13:29

ORDER BY LEN([on].Oracle_no), [on].Oracle_no