Project and Portfolio Management Practitioners Forum
cancel

Execution Command output for Table Component fields

SOLVED
Go to solution
Highlighted
Raj Ghimire
Outstanding Contributor.

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
Acclaimed 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
Outstanding Contributor.

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
Outstanding 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
Outstanding Contributor.

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
Outstanding Contributor.

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