The ORDER BY clause is invalid in views, inline fu

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

Transact-SQL (2008)

The ORDER BY clause is invalid in views, inline fu

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2009-07-28 : 08:11:39

For this query

SELECT identifierID,RD.RecordID,(select ProviderName+ ', '+Prov_City+', '+Prov_State as ProviderName from RS_Provider where ProviderId=RD.ProviderId) as ProviderName
FROM RS_Identifier ID,RS_RecordDetails RD--,RS_VisitSummary VS
WHERE PageTypeID = 27
AND ID.RecordID=RD.RecordID
and IdentifierID IN (SELECT IdentifierID FROM RS_VisitSummary where RecordID=RD.RecordID order by VisitDate)

i got the error
The 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 anyhow i want the sequence as by visit date

if i make join of all three tables.
i wont get exact output
pls suggest

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-07-28 : 08:16:10

you need to put the order by in the main select rather than the subquery. what makes you think you won't get the same result by doing a join?

Em

Swati Jain
Posting Yak Master

139 Posts

Posted - 2009-07-28 : 08:20:44

quote:
Originally posted by elancaster

you need to put the order by in the main select rather than the subquery. what makes you think you won't get the same result by doing a join?

Em

pls suggest me in details

before joining ii get output--109 rows
after joining subquery table --209 rows
which is incorrect

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-07-28 : 08:24:14

something like...


SELECT	 ID.identifierID
	,RD.RecordID
	,RS.ProviderName+ ', '+RS.Prov_City+', '+RS.Prov_State as ProviderName 
FROM RS_Identifier ID
	 join RS_RecordDetails RD on ID.RecordID=RD.RecordID
	 join RS_VisitSummary VS on VS.RecordID=RD.RecordID and ID.IdentifierID = VS.IdentifierID
	 join RS_Provider RS on RS.ProviderId=RD.ProviderId 
WHERE PageTypeID = 27
order by ....

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-28 : 08:24:41

I think you can rewrite your query like this:


SELECT
	identifierID
	, RD.RecordID
	, RS.ProviderName + ', ' + RS.Prov_City + ', ' + RS.Prov_State as ProviderName
FROM
	RS_Identifier ID
	JOIN RS_RecordDetails RD ON RD.[RecordID] = ID.[recordID]

	JOIN RS_VisitSummary VIS ON
		VIS.[RecordId] = RD.[RecordID]
		AND VIS.[IdentifierID] = ID.[identifierID]

	LEFT JOIN RS_Provider RS ON RS.[ProviderID] = RD.[ProviderId]


	--,RS_VisitSummary VS
WHERE
	PageTypeID = 27
ORDER BY
	VIS.VisitDate

If that doesn't work for you then you'll have to give us some sample data.

All the best.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-28 : 08:27:11

Elencaster:

I think the subquery in the SELECT clause needs to be a LEFT JOIN. it could possibly return a NULL after all.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-28 : 08:32:03

Elencaster:

LOL -- we are making (and then correcting) exactly the same mistakes!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources