View Single Post
 
Old April 21st, 2009, 11:20 AM
lryckman lryckman is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: Connersville, Indiana, USA.
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default

Ok...getting closer!
I tried creating a view using the top example and it was automatically changed (formatted) to this:
Code:
SELECT DISTINCT s1.CCNNumber, STUFF
                          ((SELECT DISTINCT TOP 100 PERCENT ',' + s2.PartNumber
                              FROM         dbo.view_tbl2009_PartNumbers AS s2
                              WHERE     s2.CCNNumber = s1.CCNNumber
                              ORDER BY ',' + s2.PartNumber FOR XML PATH('')), 1, 1, '') AS PartNumbers
FROM         dbo.view_tbl2009_PartNumbers AS s1
ORDER BY s1.CCNNumber
Now...When I execute the query I get an SQL Syntax Error stating:
"Error in ORDER BY clause
Unable to parse query text."

The results will still show, but I cannot save the view. When I try to save the view, I get this error message:
"The ORDER BY clause is invalid in view, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

Now, if we can get this to save as a view, I will be all set...I can link it to Access from there.
__________________
Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!