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.
| Author |
Topic |
|
rohan.rishi.2007
Starting Member
8 Posts |
 Posted - 2010-09-27 : 06:43:50
|
| Select pp.*,ex.*,c.*,s.*,pt.* from PaperPattern pp,Class c,SubSubject s,Exam ex ,(select distinct ptypeno,ptypename from papertype Order by ptypeno,ptypename) as pt where pp.classno = c.classno and pp.SubSubno = s.SubSubno and pp.examno= ex.examno and pp.ptypeno = pt.ptypeno Order by c.Class,c.ClassName,s.subno,s.Srno,s.elect,s.SubName,ex.ExSeqNoWhen i m trying 2 execute this query it gives error that----Msg 1033, Level 15, State 1, Line 3The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.But this query perfectly work in M.S.AccessSo guys Please help me ......... |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-09-27 : 07:03:15
|
The problem is in redSelect pp.*,ex.*,c.*,s.*,pt.* from PaperPattern pp ,Class c ,SubSubject s ,Exam ex , ( select distinct ptypeno , ptypename from papertype Order by ptypeno ,ptypename ) as pt where pp.classno = c.classno and pp.SubSubno = s.SubSubno and pp.examno= ex.examno and pp.ptypeno = pt.ptypeno Order by c.Class ,c.ClassName ,s.subno ,s.Srno ,s.elect ,s.SubName ,ex.ExSeqNo The order by inside this subquery is meaningless. Access doesn't care to check for it.Also -- Please learn to use JOINS. They will make your life so much easier in the long run.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-09-27 : 07:06:35
|
This(select distinct ptypeno,ptypename from papertype Order by ptypeno,ptypename) as pt should return ONE value as a column in the outer select so you have to use top 1.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-09-27 : 07:07:07
|
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-09-27 : 07:44:30
|
quote:
Originally posted by webfred
This(select distinct ptypeno,ptypename from papertype Order by ptypeno,ptypename) as pt should return ONE value as a column in the outer select so you have to use top 1.
No, you're never too old to Yak'n'Roll if you're too young to die.
I don't think so. It looks to be a derived table (joined on pt.ptypeno ) . I think OP just needs to remove the ORDER BY clause from it instead.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-09-27 : 09:34:04
|
quote:
Originally posted by Transact Charlie
quote:
Originally posted by webfred
This(select distinct ptypeno,ptypename from papertype Order by ptypeno,ptypename) as pt should return ONE value as a column in the outer select so you have to use top 1.
No, you're never too old to Yak'n'Roll if you're too young to die.
I don't think so. It looks to be a derived table (joined on pt.ptypeno ) . I think OP just needs to remove the ORDER BY clause from it instead.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Yes you are right!I have not seen it is the FROM clause.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|
|
Error Number 1033 in Query