Project and Portfolio Management Practitioners Forum
Showing results for 
Search instead for 
Do you mean 

Oracle error when trying to execute a SQL script im a workflow step

Advisor

Oracle error when trying to execute a SQL script im a workflow step

Good afternoon my PPM family,

I need help from you SQL heads out there.

Here is the script im trying to run:

 

select decode(count(a1.status),0, 'NO', 'YES'), decode(count(a1.status),0, 'No', 'Yes')
from (  select to_char(count(kstt.status)) status from kcrt_requests req
inner join knta_references ref on req.request_id = ref.source_id
inner join kwfl_step_transactions kstt on ref.parameter1 = kstt.top_instance_source_set_id
and kstt.workflow_step_id in ('39708', '39668', '39857') and kstt.result_value in ('APPROVED', 'EXEMPT')
and req.request_type_id in ( '30696', '31172' ) and req.request_id = [REQ.REQUEST_ID]
union
select kstt.status status from kcrt_requests req
inner join knta_references ref on req.request_id = ref.source_id
inner join kwfl_step_transactions kstt on ref.parameter1 = kstt.top_instance_source_set_id
and kstt.workflow_step_id in ('39708', '39668', '39857') and kstt.result_value  in ('APPROVED', 'EXEMPT')
and req.request_type_id in ( '30696', '31172' ) and req.request_id = [REQ.REQUEST_ID]  ) a1
where a1.status = 'COMPLETE'

 

And im getting this error:

 

ORA-06550: line1, column 18

PLS-))103: Encountered the symbol "SELECT" when expecting one of the following:

(-+ case mod new not null <an identifier>

<a double-quoted delimiter-identifer> <ablind variable>

continue avg count

 

The line and column count is throwing me off because it points to the count parm.

Any suggestions?

3 REPLIES
Honored Contributor Honored Contributor

Re: Oracle error when trying to execute a SQL script im a workflow step

I pasted your sql and it runs fine for me...

Advisor

Re: Oracle error when trying to execute a SQL script im a workflow step

That is the odd part. We use this script in other workflows and it works with no problem. All i did was add a workflow step to an existing workflow with this as the source exec sql. Im currently googling the error to see what comes up.

Highlighted
Honored Contributor

Re: Oracle error when trying to execute a SQL script im a workflow step

Try this

 

select

* from(

select

decode(count(a1.status),0,'NO','YES'),decode(count(a1.status),0,'No','Yes')

from

(selectto_char(count(kstt.status)) status from kcrt_requests req

inner

join knta_references refon req.request_id =ref.source_id

inner

join kwfl_step_transactions kstt onref.parameter1 = kstt.top_instance_source_set_id

and

kstt.workflow_step_idin('39708','39668','39857')and kstt.result_value in('APPROVED','EXEMPT')

and

req.request_type_id in('30696','31172')and req.request_id ='[REQ.REQUEST_ID]'

union

select

kstt.status status from kcrt_requests req

inner

join knta_references refon req.request_id =ref.source_id

inner

join kwfl_step_transactions kstt onref.parameter1 = kstt.top_instance_source_set_id

and

kstt.workflow_step_id in('39708','39668','39857')and kstt.result_value in('APPROVED','EXEMPT')

and

req.request_type_id in('30696','31172')and req.request_id ='[REQ.REQUEST_ID]') a1

where

a1.status ='COMPLETE'

)

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)