Stump the SQL Expert? I'm stumped already!

Results 1 to 2 of 2

Thread: Stump the SQL Expert? I'm stumped already!

  1. #1
    Cable Guest

    Default Stump the SQL Expert? I'm stumped already!

    SELECT DISTINCT pe.event_uno, event_date, end_date, <BR>pe.start_time, pe.end_time, subject, descrptn, <BR>pe.location, priority, event_type, pe.assigned_by, <BR>date_tent, pe.resp_user, re.employee_name as resp_employee_name, <BR>c.client_number, m.matter_number, parent_uno, c.client_name, <BR>m.matter_name <BR><BR>FROM cmsopen.cmsadm.pmm_event pe, <BR>cmsopen.cmsadm.pma_calndr_notf pcn,<BR>cmsopen.cmsadm.hbm_persnl re, <BR>cmsopen.cmsadm.hbm_persnl no, <BR>cmsopen.cmsadm.hbm_client c, <BR>cmsopen.cmsadm.hbm_matter m <BR><BR>WHERE pe.client_uno = c.client_uno<BR>AND pe.matter_uno = m.matter_uno<BR>AND pe.resp_user = re.empl_uno <BR>AND pe.event_uno *= pcn.event_uno <BR>AND pcn.notfd_user_uno = no.empl_uno <BR>AND event_date BETWEEN &#039 8/01/1999&#039 AND &#039 8/31/1999&#039 <BR>AND event_date is not null <BR>AND event_date NOT In(&#039&#039,&#039 &#039) <BR>AND pcn.Entry_Type in (&#039A&#039,&#039E&#039) <BR>AND (re.login = &#039LFINE&#039 OR no.login = &#039LFINE&#039) <BR><BR>ORDER BY event_date, event_type, pe.start_time <BR><BR>I get the error:<BR><BR>Msg 303, Level 16, State 1<BR>The table &#039cmsopen.cmsadm.pma_calndr_notf&#039 is an inner member of an outer-join clause. <BR>This is not allowed if the table also participates in a regular join clause.<BR><BR>I want to be able to show the event even if it does not have an entry in pma_clandr_notf<BR><BR>These are CMSOpen Data Tables. I am told by our SQL_Server Expert that there is no relationship between the tables. <BR>

  2. #2
    Cable Guest

    Default RE: Stump the SQL Expert? I'm stumped already!

    I see no SQL Expert here has responded.<BR><BR>I created this stored procedure, which also deals with Activity codes if one is entered. It seems to work:<BR><BR>CREATE PROCEDURE tc_pais_calendar_user_act (@StartPeriodDate varchar(20), @EndPeriodDate varchar(20), @UserID varchar(10), @Actvty_CDE varchar(10) ) AS<BR><BR>/* Used in PA Calendar application */<BR><BR>/* Developed on 8/23/1999 by NGK */<BR><BR>/* Fourth parameter can be null or blank, if so it does all activity codes. <BR> Else it shows the Activity Code records passed to the SP. */<BR><BR>/* This version does the Appointments and Events */<BR><BR>if (@actvty_cde is null) or (@actvty_cde = &#039&#039) or (@actvty_cde = &#039 &#039)<BR><BR>SELECT DISTINCT pe.event_uno, event_date, end_date, <BR>pe.start_time, pe.end_time, subject, descrptn, <BR>pe.location, priority, event_type, pe.assigned_by, pe.actvty_cde,<BR>date_tent, pe.resp_user, re.employee_name as resp_employee_name, <BR>c.client_number, m.matter_number, parent_uno, c.client_name, <BR>m.matter_name <BR><BR>FROM <BR>cmsopen.cmsadm.pmm_event pe <BR>LEFT OUTER Join cmsopen.cmsadm.pma_calndr_notf pcn <BR>ON pe.event_uno = pcn.event_uno<BR>LEFT OUTER Join cmsopen.cmsadm.hbm_persnl re<BR>ON pe.resp_user = re.empl_uno <BR>LEFT OUTER Join cmsopen.cmsadm.hbm_persnl no<BR>ON pcn.notfd_user_uno = no.empl_uno <BR>INNER Join cmsopen.cmsadm.hbm_client c<BR>ON pe.client_uno = c.client_uno<BR>INNER Join cmsopen.cmsadm.hbm_matter m <BR>ON pe.matter_uno = m.matter_uno<BR><BR>WHERE <BR>event_date BETWEEN @StartPeriodDate AND @EndPeriodDate <BR><BR>AND event_date is not null <BR>AND event_date NOT In(&#039&#039,&#039 &#039) <BR>AND pcn.Entry_Type in (&#039A&#039,&#039E&#039, NULL)<BR><BR>AND (re.login = @UserID OR no.login = @UserID)<BR><BR>ORDER BY event_date, event_type, pe.start_time<BR><BR><BR><BR>else<BR><BR>SELECT DISTINCT pe.event_uno, event_date, end_date, <BR><BR><BR>pe.start_time, pe.end_time, subject, descrptn, <BR>pe.location, priority, event_type, pe.assigned_by, pe.actvty_cde,<BR><BR>date_tent, pe.resp_user, re.employee_name as resp_employee_name, <BR>c.client_number, m.matter_number, parent_uno, c.client_name, <BR>m.matter_name <BR><BR>FROM <BR>cmsopen.cmsadm.pmm_event pe <BR>LEFT OUTER Join cmsopen.cmsadm.pma_calndr_notf pcn <BR>ON pe.event_uno = pcn.event_uno<BR>LEFT OUTER Join cmsopen.cmsadm.hbm_persnl re<BR>ON pe.resp_user = re.empl_uno <BR>LEFT OUTER Join cmsopen.cmsadm.hbm_persnl no<BR>ON pcn.notfd_user_uno = no.empl_uno <BR>INNER Join cmsopen.cmsadm.hbm_client c<BR>ON pe.client_uno = c.client_uno<BR>INNER Join cmsopen.cmsadm.hbm_matter m <BR>ON pe.matter_uno = m.matter_uno<BR><BR>WHERE <BR>event_date BETWEEN @StartPeriodDate AND @EndPeriodDate <BR><BR>AND event_date is not null <BR>AND event_date NOT In(&#039&#039,&#039 &#039) <BR>AND (re.login = @UserID OR no.login = @UserID) <BR><BR>AND pcn.Entry_Type in (&#039A&#039,&#039E&#039, NULL)<BR><BR>AND pe.actvty_cde = @actvty_cde<BR><BR><BR>ORDER BY event_date, event_type, pe.start_time<BR>GO<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •