The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Execution Command output for Table Component fields

SOLVED
Go to solution
Highlighted
Raj Ghimire
Occasional Visitor

Execution Command output for Table Component fields

Hey Experts,
I am just getting Stuck regarding one of the requirements.
Can anyone Please Guide me how to proceed regarding this Execution Command related to Table Component fields.
I just need to be able to output "YES" or "NO" depending upon the output of the following Execution Step query in Workflow Step:

select te.parameter4
from kcrt_table_entries te, knta_parameter_set_fields psf
where request_id = 31775
and te.parameter_set_field_id = psf.parameter_set_field_id
and psf.parameter_token = 'REQ_ACCESS_DETAILS'

Currently, there are three different outputs as there are 3 rows in Table Component:
PARAMETER4
----------
NO_ACCESS_REQ
USER
CHANGE_ADMIN

I need to be able to display YES, if one of the output values is Anything other than NO_ACCESS_REQ , otherwise NO.

Can anyone Please Help.
Thanks.
-Raj
5 REPLIES
Mahen M
Honored Contributor

Re: Execution Command output for Table Component fields

SELECT
CASE WHEN NVL('[REQD.P.BAC_MONITORING]','No') = 'Yes' AND NVL('[REQD.P.BAC_ASSESS]','No') <> 'Yes' THEN 'SUCCESS'
ELSE 'FAILURE' END
FROM DUAL


Please see whether the above sample SQL helps you.

Regards,
Mahendran M
Raj Ghimire
Occasional Visitor

Re: Execution Command output for Table Component fields

Sorry Mahem,
Not Sure I was able to exactly get your suggestion.
I was trying to see if I would be able to do this without having to write a long PL/SQL function, which isn't exactly my strong point :-(
I have included screenshots for WF and Table Component to better explain what I am trying to achieve.
Would really appreciate if you or anyone else can help me out on this.
Thanks.
-Raj
Erik Cole_1
Esteemed Contributor
Solution

Re: Execution Command output for Table Component fields

Hi Raj,

Try this. It's asking "how many rows are there with a value other than NO_ACCESS_REQ" and simply morphing the result such that a zero result is a NO and any non-zero result is a YES.

select decode(count(*) ,0,'NO','YES')
from kcrt_table_entries te, knta_parameter_set_fields psf
where request_id = 31775
and te.parameter_set_field_id = psf.parameter_set_field_id
and psf.parameter_token = 'REQ_ACCESS_DETAILS'
AND te.parameter4 != 'NO_ACCESS_REQ'
Raj Ghimire
Occasional Visitor

Re: Execution Command output for Table Component fields

GREAT !!!
Thank You VERY MUCH Eric !!
This works perfectly !!

I was playing with distinct and decode forever on this but never thought of using count :-)

Thanks a Lot !
-Raj
Raj Ghimire
Occasional Visitor

Re: Execution Command output for Table Component fields

The Problem was Re-solved with the help from Erik Cole. Please see above for details.
Thanks.
-Raj
//Add this to "OnDomLoad" event