sql reporting problem from oracle database

From: Martin Young <MARTINJAMESYOUNG_at_HOTMAIL.COM>
Date: 19 Sep 2003 07:13:22 -0700
Message-ID: <cf938d26.0309190613.fce14a2@posting.google.com>

Hi

I'm new to sql / plsql and i'm trying to produce a report that can output results to an excel worksheet.

Our occupational health department use an application called OPAS. the back end of this is an oracle database.

I need to produce a report that will identify all valid restrictions from the restrictions view.

the problem being that the database creates a new record if a restriction is lifted see below for example;

 Name                             Event type
 martin young      Restriction Applied: No Pushing/Pulling
 martin young      Restriction Lifted: No Pushing/Pulling
 martin young      Restriction Applied: Bending and Twisting
 martin young      Restriction Applied: Limited Use of Upper Limb
 martin young      Restriction Applied: Lifting and Handling
 martin young      Restriction Applied: Overhead Work
 martin young      Restriction Lifted: Limited Use of Upper Limb

I have created the following code to return all of the restrictions how can i modify it to just display applied restrictions?

SELECT DISTINCT

  A.PIN,
  A.FULLNAME AS "NAME",
  A.COSTCENTRE AS "COST CENTRE",
  A.D_S__ATTENDANCE_DATE AS "RESTRICTION APPLIED ON",
  A.D_S__DUE_DATE AS "RECALL DATE",
  A.S__EVENT_TYPE AS"RESTRICTION TYPE",

 a.P__EMPLOYMENT_STATUS
FROM
  cass.opasrestrictions_view A
WHERE

  A.PIN <>'1'   AND
  a.S__EVENT_TYPE LIKE 'Restriction%' AND
  a.P__EMPLOYMENT_STATUS NOT LIKE 'Terminated%'

Ideally this then could automatically e-mail to a specified number of people in scheduled tasks.

Any help at all is greatly appreciated

Martin Received on Fri Sep 19 2003 - 09:13:22 CDT