Oracle error when trying to execute a SQL script im a workflow step (273 Views)
Reply
Advisor
dasfweg
Posts: 31
Registered: ‎07-13-2011
Message 1 of 4 (273 Views)

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?

Please use plain text.
Honored Contributor
Erik Cole
Posts: 861
Registered: ‎02-11-2008
Message 2 of 4 (231 Views)

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

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

Please use plain text.
Advisor
dasfweg
Posts: 31
Registered: ‎07-13-2011
Message 3 of 4 (228 Views)

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.

Please use plain text.
Honored Contributor
Utkarsh_Mishra
Posts: 684
Registered: ‎07-13-2010
Message 4 of 4 (206 Views)

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)
Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation