• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 464
  • Last Modified:

Missing From

Hi
I am using the attached code, which was written by someone else.  Its asking for 'From', but I can't see where the from is missing.  Could someone take a look, and let me know where it is missing.

I'm new to the PL/SQL, Siebel environment.

Thanks

Greg

Select 
category
, incident_type
, count (distinct incident_id) as incident

select count (distinct incident_id)
from
(
Select 
REGIONA as Region
, SERVICE_PROVIDER as "Service_Provider"
, X_COST_CENTRE	   as "Cost Centre"
, X_UNIT_FUNCT_TYPE	  as "Unit Function Type"
, INCIDENT_ID	  
--, CREAT_DATE		  as "Report Date"
--, INCIDENT_DATE		  as "Incident Date"
--, EVT_STAT_CD as "Incident status"
, PERSON_UID  as incid_per
--, LAST_NAME	  
--, FST_NAME
--, EMP_FLG 	  as "Employee Flg"
, incident_type
, Category
, ROLE		  as "Role in incident"
, INCIDENT_TYPE	 as "Incident Type"
, (CASE
WHEN incident_type in ('Allege physical/sexual assault', 'Alleged assault on client', 'Alleged Assault on Staff',
                        'Assault without injury', 'Attempted suicide', 'Client adverse behaviour', 'Client adverse behaviour',
						'Client missing - high risk beh', 'Client unauthorised absence' , 'Damage to equipment',
						'Harassment of client' , 'Harassment of staff', 'Property damage unfit for use', 'Required referral to police', 
						'Sexual harassment - client', 'Sexual harassment - staff', 'Sexual harassment - staff', 'Other Incidents eg Near Miss') 
						Then Incident_id
						END) as Beh_incident	
, (CASE
WHEN incident_type in ('Allege physical/sexual assault', 'Alleged assault on client', 'Alleged Assault on Staff',
                        'Assault without injury', 'Attempted suicide', 'Client adverse behaviour', 'Client adverse behaviour',
						'Client missing - high risk beh', 'Client unauthorised absence' , 'Damage to equipment',
						'Harassment of client' , 'Harassment of staff', 'Property damage unfit for use', 'Required referral to police', 
						'Sexual harassment - client', 'Sexual harassment - staff', 'Sexual harassment - staff', 'Other Incidents eg Near Miss') 
						Then Person_uid
						END) as Beh_person	
, (CASE
WHEN category = 'Category 1'
						Then incident_id
						END) as Cat1						
, (CASE
WHEN category = 'Category 2'
						Then incident_id
						END) as Cat2	
, (CASE
WHEN category = 'Category 3'
						Then incident_id
						END) as Cat3	
, (CASE
WHEN category = 'Category 4'
						Then incident_id
						END) as Cat4	
, (CASE
WHEN category is null
						Then incident_id
						END) as Catnull	
, (CASE
WHEN incident_type = 'Medication error'
						Then incident_id
						END) as medicalerror							
, (CASE
WHEN incident_type in ('Adverse impact - limited', 'Adverse impact to DADHC', 'Major fire/property damage', 'Minor interuption to service', 'Property damage unfit for use')
						Then incident_id
						END) as Serv_interruption
, (CASE
WHEN incident_type in ('Alleged assault on client', 'Assault without injury', 'Harassment of client', 'Sexual harassment - client')
						Then incident_id
						END) as Action_ag_clients
, (CASE
WHEN incident_type in ('Alleged Assault on Staff', 'Harassment of staff', 'Staff injury medical treatmnt')
						Then incident_id
						END) as Action_ag_staff	
, (CASE
WHEN incident_type in ( 'Attempted suicide', 'Client adverse behaviour', 'Client high risk behaviour', 'Client hospitalised for injury', 'Client injury first aid only',
	 			   	    'Client missing - high risk beh', 'Client unauthorised absence', 'DoCS Report', 'Minor financial loss')
						Then incident_id
						END) as Client_related	
, (CASE
WHEN incident_type in ('Death of client', 'Death of client - anticipated')
						Then incident_id
						END) as client_death	
, (CASE
WHEN incident_type in ('Exposure to bodily fluids', 'Life threatening [eg bomb]', 'Visitor first aid treatment')
						Then incident_id
						END) as OHS	
, (CASE
WHEN incident_type in ('Other Incident - Category 1')
						Then incident_id
						END) as Other_cat1
, (CASE
WHEN incident_type = 'Other Incident - Category 2'
						Then incident_id
						END) as Other_cat2	
, (CASE
WHEN incident_type = 'Other Incident - Category 3'
						Then incident_id
						END) as Other_cat3	
, (CASE
WHEN incident_type in ('Other Incident - Category 4', 'Other Incidents eg Near Miss')
						Then incident_id
						END) as Other_cat4																																																													
, (CASE
WHEN incident_type = 'Damage to equipment'
						Then incident_id
						END) as damage2equip											
, (CASE
WHEN incident_type is null
						Then incident_id
						END) as no_type_inc																	
from
(
SELECT *
FROM   
(
SELECT	soe.X_Cost_Centre
		 	   ,soe.Name 	   				AS service_provider
		 	   ,soe.X_UNIT_FUNCT_TYPE
		 	   ,soe.X_bus_stream
		 	   ,sea.Created		 			AS created	
			   ,sea.Name 					AS incident_ID
			   ,sea.X_unit_id
			   ,sea.Target_OU_ID
			   ,sea.Row_id
			   ,sea.Todo_cd
			   ,soe.X_Region 				AS regiona
			   ,soe.X_bus_stream 			AS Bus
			   ,sea.X_unit
			   ,sea.TARGET_OU_ID 	   		AS service_prov_id
			   ,sea.evt_stat_cd 
			   ,sea.X_status_dt
			   ,sea.DOC_RCVD_DT
			   ,sea.TODO_DUE_DT
			   ,sea.X_COMPLAINT_FLG
			   ,sea.Todo_due_dt					   	  			  		 		 	  				   	  		AS date_r
			   ,CAST((FROM_TZ(CAST(sea.Todo_due_dt AS TIMESTAMP), 'GMT') AT TIME ZONE 'Australia/NSW') AS DATE) AS incident_date   	                              
			   ,CAST((FROM_TZ(CAST(sea.CREATED AS TIMESTAMP), 'GMT') AT TIME ZONE 'Australia/NSW') AS DATE) 	AS creat_date 
			   ,sea. Activity_UID 			   	  			  		 		 	  				   	  			AS act_id
		FROM	Siebel.S_evt_act@dacisp01 sea,
				Siebel.S_org_ext@dacisp01 soe
		WHERE 	sea.Target_OU_ID=soe.Row_id
		--AND		soe.X_bus_stream in ('Accommodation & Respite', 'Accomodation & Direct Services', 'Planning & Access')
		AND		sea.x_complaint_flg  = 'N'
		--and 	soe.X_Region = 'Central Office'
	--	AND 	sea.Name = '1-34808980'
		--and 	sea.Name = '1-136231393'
			) A,
		(
select 
 Siebel.S_act_contact. X_ACT_ROLE as role
, Siebel.S_act_contact. X_INC_FORM_ID as form
, Siebel.S_act_contact. X_STATUS as status
, Siebel.S_act_contact. X_Injury_Sustained as injury_flg
, Siebel.S_act_contact. X_first_aid as fstaid_flg
, Siebel.S_act_contact. Con_id
, Siebel.S_act_contact. activity_id 
--select * 
from Siebel.S_act_contact@dacisp01
)B
Where A. act_id = B.activity_id(+)
)KK,
(
select 
Siebel.CX_S_EVT_ACT_XM. Row_id
, Siebel.CX_S_EVT_ACT_XM. Par_row_id
, Siebel.CX_S_EVT_ACT_XM. CREATED
, Siebel.CX_S_EVT_ACT_XM. Name
, Siebel.CX_S_EVT_ACT_XM. Type
, Siebel.CX_S_EVT_ACT_XM. X_attrib_01 as Incident_type
, Siebel.CX_S_EVT_ACT_XM. X_attrib_08 as Category
, Siebel.CX_S_EVT_ACT_XM. X_con_id
 from Siebel.CX_S_EVT_ACT_XM@dacisp01
 where Type = 'Incidents'
 )CC,
( 	  
select 
	   Row_id
	   , Person_UID
	   , Last_name			   
	   , fst_name
	   , Emp_flg
from Siebel.s_contact@dacisp01
)DD
Where KK.con_id=CC.X_con_id (+)
and KK.activity_id=CC.Par_row_id(+)
and KK.con_id = DD.row_id (+)
and	 	incident_date BETWEEN CAST((FROM_TZ(CAST(TO_DATE((:DSD||'/'||:DSM||'/'||:DSY), 'DD/MM/YYYY') AS TIMESTAMP), 
			   			'GMT') AT TIME ZONE 'Australia/NSW') AS DATE) AND 
						CAST((FROM_TZ(CAST(TO_DATE((:DED||'/'||:DEM||'/'||:DEY), 'DD/MM/YYYY')+1 AS TIMESTAMP), 
			   			'GMT') AT TIME ZONE 'Australia/NSW') AS DATE)
and role in ('Subject', 'Alleged offender')						
)

Group by category, incident_type

Open in new window

0
greg_c
Asked:
greg_c
2 Solutions
 
ishandoCommented:
Wouldn't

Select  category, incident_type, count (distinct incident_id) as incident
select count (distinct incident_id)
from
(
be just

Select  category, incident_type, count (distinct incident_id) as  incident
from
(
0
 
sdstuberCommented:
try this...

you were missing a "FROM (" before the second select and ")" before the last group by
  SELECT category, incident_type, COUNT(DISTINCT incident_id) AS incident
    FROM (SELECT COUNT(DISTINCT incident_id)
            FROM (SELECT regiona AS region,
                         service_provider AS "Service_Provider",
                         x_cost_centre AS "Cost Centre",
                         x_unit_funct_type AS "Unit Function Type",
                         incident_id --, CREAT_DATE            as "Report Date"
                                     --, INCIDENT_DATE                 as "Incident Date"
                                     --, EVT_STAT_CD as "Incident status"
                         ,
                         person_uid AS incid_per --, LAST_NAME
                                                 --, FST_NAME
                                                 --, EMP_FLG       as "Employee Flg"
                         ,
                         incident_type,
                         category,
                         role AS "Role in incident",
                         incident_type AS "Incident Type",
                         (CASE
                              WHEN incident_type IN
                                       ('Allege physical/sexual assault',
                                        'Alleged assault on client',
                                        'Alleged Assault on Staff',
                                        'Assault without injury',
                                        'Attempted suicide',
                                        'Client adverse behaviour',
                                        'Client adverse behaviour',
                                        'Client missing - high risk beh',
                                        'Client unauthorised absence',
                                        'Damage to equipment',
                                        'Harassment of client',
                                        'Harassment of staff',
                                        'Property damage unfit for use',
                                        'Required referral to police',
                                        'Sexual harassment - client',
                                        'Sexual harassment - staff',
                                        'Sexual harassment - staff',
                                        'Other Incidents eg Near Miss')
                              THEN
                                  incident_id
                          END)
                             AS beh_incident,
                         (CASE
                              WHEN incident_type IN
                                       ('Allege physical/sexual assault',
                                        'Alleged assault on client',
                                        'Alleged Assault on Staff',
                                        'Assault without injury',
                                        'Attempted suicide',
                                        'Client adverse behaviour',
                                        'Client adverse behaviour',
                                        'Client missing - high risk beh',
                                        'Client unauthorised absence',
                                        'Damage to equipment',
                                        'Harassment of client',
                                        'Harassment of staff',
                                        'Property damage unfit for use',
                                        'Required referral to police',
                                        'Sexual harassment - client',
                                        'Sexual harassment - staff',
                                        'Sexual harassment - staff',
                                        'Other Incidents eg Near Miss')
                              THEN
                                  person_uid
                          END)
                             AS beh_person,
                         (CASE
                              WHEN category = 'Category 1' THEN incident_id
                          END)
                             AS cat1,
                         (CASE
                              WHEN category = 'Category 2' THEN incident_id
                          END)
                             AS cat2,
                         (CASE
                              WHEN category = 'Category 3' THEN incident_id
                          END)
                             AS cat3,
                         (CASE
                              WHEN category = 'Category 4' THEN incident_id
                          END)
                             AS cat4,
                         (CASE WHEN category IS NULL THEN incident_id END)
                             AS catnull,
                         (CASE
                              WHEN incident_type = 'Medication error'
                              THEN
                                  incident_id
                          END)
                             AS medicalerror,
                         (CASE
                              WHEN incident_type IN
                                       ('Adverse impact - limited',
                                        'Adverse impact to DADHC',
                                        'Major fire/property damage',
                                        'Minor interuption to service',
                                        'Property damage unfit for use')
                              THEN
                                  incident_id
                          END)
                             AS serv_interruption,
                         (CASE
                              WHEN incident_type IN
                                       ('Alleged assault on client',
                                        'Assault without injury',
                                        'Harassment of client',
                                        'Sexual harassment - client')
                              THEN
                                  incident_id
                          END)
                             AS action_ag_clients,
                         (CASE
                              WHEN incident_type IN
                                       ('Alleged Assault on Staff',
                                        'Harassment of staff',
                                        'Staff injury medical treatmnt')
                              THEN
                                  incident_id
                          END)
                             AS action_ag_staff,
                         (CASE
                              WHEN incident_type IN
                                       ('Attempted suicide',
                                        'Client adverse behaviour',
                                        'Client high risk behaviour',
                                        'Client hospitalised for injury',
                                        'Client injury first aid only',
                                        'Client missing - high risk beh',
                                        'Client unauthorised absence',
                                        'DoCS Report',
                                        'Minor financial loss')
                              THEN
                                  incident_id
                          END)
                             AS client_related,
                         (CASE
                              WHEN incident_type IN
                                       ('Death of client',
                                        'Death of client - anticipated')
                              THEN
                                  incident_id
                          END)
                             AS client_death,
                         (CASE
                              WHEN incident_type IN
                                       ('Exposure to bodily fluids',
                                        'Life threatening [eg bomb]',
                                        'Visitor first aid treatment')
                              THEN
                                  incident_id
                          END)
                             AS ohs,
                         (CASE
                              WHEN incident_type IN
                                       ('Other Incident - Category 1')
                              THEN
                                  incident_id
                          END)
                             AS other_cat1,
                         (CASE
                              WHEN incident_type =
                                       'Other Incident - Category 2'
                              THEN
                                  incident_id
                          END)
                             AS other_cat2,
                         (CASE
                              WHEN incident_type =
                                       'Other Incident - Category 3'
                              THEN
                                  incident_id
                          END)
                             AS other_cat3,
                         (CASE
                              WHEN incident_type IN
                                       ('Other Incident - Category 4',
                                        'Other Incidents eg Near Miss')
                              THEN
                                  incident_id
                          END)
                             AS other_cat4,
                         (CASE
                              WHEN incident_type = 'Damage to equipment'
                              THEN
                                  incident_id
                          END)
                             AS damage2equip,
                         (CASE WHEN incident_type IS NULL THEN incident_id END)
                             AS no_type_inc
                    FROM (SELECT *
                            FROM (SELECT soe.x_cost_centre,
                                         soe.name AS service_provider,
                                         soe.x_unit_funct_type,
                                         soe.x_bus_stream,
                                         sea.created AS created,
                                         sea.name AS incident_id,
                                         sea.x_unit_id,
                                         sea.target_ou_id,
                                         sea.row_id,
                                         sea.todo_cd,
                                         soe.x_region AS regiona,
                                         soe.x_bus_stream AS bus,
                                         sea.x_unit,
                                         sea.target_ou_id AS service_prov_id,
                                         sea.evt_stat_cd,
                                         sea.x_status_dt,
                                         sea.doc_rcvd_dt,
                                         sea.todo_due_dt,
                                         sea.x_complaint_flg,
                                         sea.todo_due_dt AS date_r,
                                         CAST(
                                             (FROM_TZ(
                                                  CAST(
                                                      sea.todo_due_dt AS TIMESTAMP
                                                  ),
                                                  'GMT'
                                              )
                                                  AT TIME ZONE 'Australia/NSW') AS DATE
                                         )
                                             AS incident_date,
                                         CAST(
                                             (FROM_TZ(
                                                  CAST(
                                                      sea.created AS TIMESTAMP
                                                  ),
                                                  'GMT'
                                              )
                                                  AT TIME ZONE 'Australia/NSW') AS DATE
                                         )
                                             AS creat_date,
                                         sea.activity_uid AS act_id
                                    FROM siebel.s_evt_act@dacisp01 sea,
                                         siebel.s_org_ext@dacisp01 soe
                                   WHERE sea.target_ou_id = soe.row_id --AND           soe.X_bus_stream in ('Accommodation & Respite', 'Accomodation & Direct Services', 'Planning & Access')
                                     AND sea.x_complaint_flg = 'N' --and   soe.X_Region = 'Central Office'
                                                                   --      AND     sea.Name = '1-34808980'
                                                                   --and   sea.Name = '1-136231393'
                                 ) a,
                                 (SELECT siebel.s_act_contact.x_act_role
                                             AS role,
                                         siebel.s_act_contact.x_inc_form_id
                                             AS form,
                                         siebel.s_act_contact.x_status
                                             AS status,
                                         siebel.s_act_contact.
                                         x_injury_sustained
                                             AS injury_flg,
                                         siebel.s_act_contact.x_first_aid
                                             AS fstaid_flg,
                                         siebel.s_act_contact.con_id,
                                         siebel.s_act_contact.activity_id
                                    --select *
                                    FROM siebel.s_act_contact@dacisp01) b
                           WHERE a.act_id = b.activity_id(+)) kk,
                         (SELECT siebel.cx_s_evt_act_xm.row_id,
                                 siebel.cx_s_evt_act_xm.par_row_id,
                                 siebel.cx_s_evt_act_xm.created,
                                 siebel.cx_s_evt_act_xm.name,
                                 siebel.cx_s_evt_act_xm.TYPE,
                                 siebel.cx_s_evt_act_xm.x_attrib_01
                                     AS incident_type,
                                 siebel.cx_s_evt_act_xm.x_attrib_08 AS category,
                                 siebel.cx_s_evt_act_xm.x_con_id
                            FROM siebel.cx_s_evt_act_xm@dacisp01
                           WHERE TYPE = 'Incidents') cc,
                         (SELECT row_id,
                                 person_uid,
                                 last_name,
                                 fst_name,
                                 emp_flg
                            FROM siebel.s_contact@dacisp01) dd
                   WHERE kk.con_id = cc.x_con_id(+)
                     AND kk.activity_id = cc.par_row_id(+)
                     AND kk.con_id = dd.row_id(+)
                     AND incident_date BETWEEN CAST(
                                                   (FROM_TZ(
                                                        CAST(
                                                            TO_DATE(
                                                                (   :dsd
                                                                 || '/'
                                                                 || :dsm
                                                                 || '/'
                                                                 || :dsy),
                                                                'DD/MM/YYYY'
                                                            ) AS TIMESTAMP
                                                        ),
                                                        'GMT'
                                                    )
                                                        AT TIME ZONE 'Australia/NSW') AS DATE
                                               )
                                           AND CAST(
                                                   (FROM_TZ(
                                                        CAST(
                                                            TO_DATE(
                                                                (   :ded
                                                                 || '/'
                                                                 || :dem
                                                                 || '/'
                                                                 || :dey),
                                                                'DD/MM/YYYY'
                                                            )
                                                            + 1 AS TIMESTAMP
                                                        ),
                                                        'GMT'
                                                    )
                                                        AT TIME ZONE 'Australia/NSW') AS DATE
                                               )
                     AND role IN ('Subject', 'Alleged offender')))
GROUP BY category, incident_type

Open in new window

0
 
greg_cAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now