June 22, 2006 at 2:16 pm
I am creating a view and I have the situation where to get all the data that I need I would do a left outer join. However, even though I can create the view with the left outer join, I get an error when trying to retrieve data with the view. Can anyone help me with this?
June 22, 2006 at 2:42 pm
>>I get an error when trying to retrieve data with the view.
Doubtful anyone can help until you:
- Provide details on the error and
- Show us the SQL of the view definition
June 22, 2006 at 2:42 pm
What is the error message you are getting?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 22, 2006 at 2:51 pm
Below is the error when using the view and the syntax for the view:
select * from mdn_realtime_section_schedule_v
Server: Msg 301, Level 16, State 1, Line 1
Query contains an outer-join request that is not permitted.
View syntax:
CREATE VIEW mdn_realtime_section_schedule_v
( yr_cde,
trm_cde,
crs_cde,
division_cde,
institut_div_cde,
credit_hrs,
location_cde,
bldg_cde,
room_cde,
lead_instructor_id,
lead_instructor_last_name,
lead_instructor_first_name,
section_status,
dpt_head_id,
dpt_head_last_name,
dpt_head_first_name,
dpt_head_phone,
dpt_head_extension,
begin_date,
end_date,
section_note,
comment_text,
comment_1,
comment_2,
prereq_description,
begin_time,
end_time,
meeting_days,
full_course_title
)
AS
SELECT section_master_v.yr_cde,
section_master_v.trm_cde,
section_master_v.crs_cde,
section_master_v.division_cde,
section_master_v.institut_div_cde,
section_master_v.credit_hrs,
section_schedules.loc_cde,
section_schedules.bldg_cde,
section_schedules.room_cde,
name_master_a.id_num,
name_master_a.last_name,
name_master_a.first_name,
section_master_v.section_sts,
crscomp1_dept_mdn.dpt_head_id,
name_master_b.last_name,
name_master_b.first_name,
addr_all_person_work_addrs.phone,
addr_all_person_work_addrs.extension,
RIGHT(CONVERT(CHAR(10),SECTION_SCHEDULES.BEGIN_DTE,101),10)BEGIN_DATE,
RIGHT(CONVERT(CHAR(10),SECTION_SCHEDULES.END_DTE,101),10)END_DATE,
(section_master_v.section_note)SECTION_NOTE,
(section_schedules.comment_txt)COMMENT_TEXT,
(section_schedules.comment_1_cde)COMMENT_1,
(section_schedules.comment_2_cde)COMMENT_2,
(table_detail.table_desc)PREREQ_DESC,
LOWER(RIGHT(CONVERT(CHAR(25),SECTION_SCHEDULES.BEGIN_TIM,0),13))BEGIN_TIME,
LOWER(RIGHT(CONVERT(CHAR(25),SECTION_SCHEDULES.END_TIM,0),13))END_TIME,
(RTRIM(LTRIM(isnull(section_schedules.monday_cde, '') + isnull(section_schedules.tuesday_cde, '') + isnull(section_schedules.wednesday_cde, '') + isnull(section_schedules.thursday_cde, '') + isnull(section_schedules.friday_cde, '') + isnull(section_schedules.saturday_cde, '') + isnull(section_schedules.sunday_cde, ''))))MEETING_DAYS,
(section_master_v.crs_title + ' ' + section_master_v.crs_title_2)COURSE_TITLE
FROM section_schedules,
name_master name_master_a,
crscomp1_dept_mdn,
section_master_v,
name_master name_master_b,
addr_all_person_work_addrs,
table_detail
WHERE ( crscomp1_dept_mdn.dpt_head_id *= name_master_b.id_num ) and
( name_master_b.id_num *= addr_all_person_work_addrs.id_num ) and
( section_master_v.prereq_cde *= table_detail.table_value ) and
( section_master_v.yr_cde = section_schedules.yr_cde ) and
( section_master_v.trm_cde = section_schedules.trm_cde ) and
( section_master_v.crs_cde = section_schedules.crs_cde ) and
( section_master_v.lead_instructr_id = name_master_a.id_num ) and
( section_master_v.crs_comp1 = crscomp1_dept_mdn.crs_comp1 ) and
( table_detail.column_name = 'prereq_cde' )
June 22, 2006 at 3:05 pm
Don't use old-style non ANSI joins.
Get rid of the "*=" joins in the WHERE clause, and replace with LEFT JOIN in the FROM.
June 23, 2006 at 7:43 am
Thanks for your help. Your answer helped lead me to the solution. The problem was that a table with a left outer join was attached to another table with a left outer join. Bless you for your answer!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply