Sybase Views

[Ortiz]

Can one perform outer joins on two views?

The existing two views already have a join within the select statement of the create view statement - I'm not sure if that could be the problem.

View A is as follows:
create view ptt_maindocs as
select doc_id, A.team_id, team_name, proj_name, category, function, other_classification, logno,raised_by, action_by, raised_date, due_date, resolved_date, priority, rag, likelihood, steering_grp,last_update, last_author, description, detail_desc
from Main_Docs A, ptt_security B
where A.team_id = B.team_id
and name = suser_name()

View B is as follows:
create view ptt_respdocs as
select doc_id, resp_id, A.team_id, team_name, proj_name, category, function, other_classification,
last_update, last_author, response
from Resp_Docs A, ptt_security B
where A.team_id = B.team_id
and name = suser_name()

My ultimate query is
select A.team_id, A.team_name, description, B.response
from ptt_maindocs A, ptt_respdocs B
where A.doc_id *= B.doc_id
order by A.team_id

But I get this error:
This query cannot be processed because it contains TEXT/IMAGE columns and references a view defined with a subquery, an outer join, or a distinct clause.

Please note, that both base tables have text fields.

Thanks and any help would be greatly appreciated.

 

[JMCraig]

You could try this: strip down your two views to contain only the key info and then add the text/image columns to the final query itself.

Also, one other way to get this type of info out is to use a stored procedure to assemble a temp table that contains the key info, and then do one query that returns the columns you want by joining to the temp table (that wouldn't be an outer join, but a natural join).

Good luck!

John
nsjmcraig@netscape.net