Sorry,

The code first subscribed is syntactically flawed (as well as logically.) Here is the version that is only syntactically correct, but still logically flawed:

SELECT a.[ID],
a.[RequestID],
a.[UserIDContact],
a.[IsRequired],
a.[RouteOrder],
c.[TypeIDContactStatus]
FROM ContactStatus_vw c,
AvailablePendingRoutePointUser_vw a
WHERE a.[TypeIDContactStatus] = c.[TypeIDContactStatus]
AND c.[Code] = 'PRIMARY'
UNION
SELECT a2.[ID],
a2.[RequestID],
a2.[UserIDContact],
a2.[IsRequired],
a2.[RouteOrder],
c2.[TypeIDContactStatus]
FROM ContactStatus_vw c2,
AvailablePendingRoutePointUser_vw a2
WHERE a2.[TypeIDContactStatus] = c2.[TypeIDContactStatus]
AND c2.[Code] = 'SECONDARY'
UNION
SELECT a3.[ID],
a3.[RequestID],
a3.[UserIDContact],
a3.[IsRequired],
a3.[RouteOrder],
c3.[TypeIDContactStatus]
FROM ContactStatus_vw c3,
AvailablePendingRoutePointUser_vw a3
WHERE a3.[TypeIDContactStatus] = c3.[TypeIDContactStatus]
AND c3.[Code] = 'SUBSTITUTE'