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

greg_cAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
greg_cAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.