Join two views using server aliases

Hello everybody,
I'm working for a hospital and for this job, I created two views using the
server aliases (linked servers).
Both these views are working correctly.
The design of the first view is:
SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
SPECIALTY_DESCRIPTION AS NAME
FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
ORDER BY SPECIALTY_DESCRIPTION
And for the second view is:
SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE AS
SPECIALTY_CODE
FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
ORDER BY NAME
The problem appaers when I try to join both these views with, for example,
this query:
select c.ID
from VW_CONSULTANTS c
left outer join VW_SPECIALTIES s
on c.SPECIALTY_CODE=s.CODE
I have these error messages:
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ORDER'.
Server: Msg 1033, Level 15, State 1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived tables,
and subqueries, unless TOP is also specified.
For me, the problem is because we try to join two views using server aliases
and the sql server is not successfull in creating the temporary table to put
the results of my select...
But I don't know what I have to do to fix the problem.
Can you help me please?
StF"StF" <StF@.discussions.microsoft.com> wrote in message
news:760671EB-8EC8-4464-8972-D570E0FB2F86@.microsoft.com...
> Hello everybody,
> I'm working for a hospital and for this job, I created two views using the
> server aliases (linked servers).
> Both these views are working correctly.
> The design of the first view is:
> SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
> SPECIALTY_DESCRIPTION AS NAME
> FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
> ORDER BY SPECIALTY_DESCRIPTION
> And for the second view is:
> SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE
> AS
> SPECIALTY_CODE
> FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
> WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
> ORDER BY NAME
> The problem appaers when I try to join both these views with, for example,
> this query:
> select c.ID
> from VW_CONSULTANTS c
> left outer join VW_SPECIALTIES s
> on c.SPECIALTY_CODE=s.CODE
>
I don't know exactly what's happening, but I know how to fix it.
Don't use TOP 100 PERCENT in a view. It serves absolutely no purpose.
In SQL 2000 this would cause queries against the view to be sorted. But
this behavior was never guaranteed, and it doesn't always happen in 2005.
David|||Thank you very much.
In fact, we use SQL Server 2000 and that's why I used 'TOP 100 PERCENT'.
So, I removed this thing and the 'ORDER BY' and now my select is working.
Thank you again.
StF
"David Browne" wrote:
> "StF" <StF@.discussions.microsoft.com> wrote in message
> news:760671EB-8EC8-4464-8972-D570E0FB2F86@.microsoft.com...
> > Hello everybody,
> >
> > I'm working for a hospital and for this job, I created two views using the
> > server aliases (linked servers).
> > Both these views are working correctly.
> >
> > The design of the first view is:
> > SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
> > SPECIALTY_DESCRIPTION AS NAME
> > FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
> > ORDER BY SPECIALTY_DESCRIPTION
> >
> > And for the second view is:
> > SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE
> > AS
> > SPECIALTY_CODE
> > FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
> > WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
> > ORDER BY NAME
> >
> > The problem appaers when I try to join both these views with, for example,
> > this query:
> > select c.ID
> > from VW_CONSULTANTS c
> > left outer join VW_SPECIALTIES s
> > on c.SPECIALTY_CODE=s.CODE
> >
> I don't know exactly what's happening, but I know how to fix it.
> Don't use TOP 100 PERCENT in a view. It serves absolutely no purpose.
> In SQL 2000 this would cause queries against the view to be sorted. But
> this behavior was never guaranteed, and it doesn't always happen in 2005.
> David
>
>