Re: Trying to run SQL/CASE statement to get value then populate field (634 Views)
Reply
Advisor
Frankie_Kings
Posts: 26
Registered: ‎04-08-2010
Message 1 of 9 (798 Views)
Accepted Solution

Trying to run SQL/CASE statement to get value then populate field

Hello Team, 

We have a business need to calulate a custom value/result between delivery dates in a work plan from a project. 

We are pulling the dates/tables needed directly from the database so the only token being used is the 'Project Name' in the SQL. 

We have configured the SQL with CASE statements since the resulting value requires examination of dates, working days between, etc. so it is fairly complex. 

We can run the SQL successfully thru the workbench to derive the value ("Variance"). 

 

What we would like to do is automate this SQL either thru an 'execution' step or request type rule to run the calculation. 

If thru an execution step, then store the result in a field on the request. 

 

Is this possible to do with the way we have configured the SQL?  Can you review to determine if we have functions or parameters or the groupings correct to be used in PPM? 

 

Is the best way to attempt to run this thru an Execution Step or is there another process we should check. 

 

This is our first time working with CASE STATEMENTS in SQL's so any advice/help from the SQL/PPM experts is appreciated. 

Thank you!

SELECT 
 CASE
WHEN bts.sched_finish_date is NULL or ts.sched_finish_date is NULL
   THEN NULL
WHEN bts.sched_finish_date = ts.sched_finish_date
   THEN 0
WHEN bts.sched_finish_date < ts.sched_finish_date
   THEN (select count(*) from kdrv_working_days
        where calendar_id = 25000
          and working_day_date between bts.sched_finish_date and ts.sched_finish_date
          and working_day_flag = 'Y')
WHEN bts.sched_finish_date > ts.sched_finish_date
   THEN (select count(*) from kdrv_working_days
        where calendar_id = 25000
          and working_day_date between ts.sched_finish_date and bts.sched_finish_date
          and working_day_flag = 'Y') * -1
END as "Delivery" 
  
FROM wp_task_schedule bts, wp_tasks bta, pm_work_plans bwp, wp_task_info bti, kcrt_fg_pfm_project fg,
     wp_task_schedule ts, wp_tasks ta, pm_work_plans wp, wp_task_info ti
WHERE fg.project_name = '[REQD.VP.PROJECT]'   <this would be our project name token
  and bwp.project_id = fg.prj_project_id
  and wp.project_id = fg.prj_project_id
  and bwp.entity_type='BASE_LINE'
  and wp.entity_type='WORK_PLAN'
  and bwp.is_active_flag='Y'
  and wp.is_active_flag='Y'
  and bta.work_plan_id= bwp.work_plan_id
  and ta.work_plan_id= wp.work_plan_id
  and bti.task_info_id=bta.task_info_id 
  and ti.task_info_id=ta.task_info_id 
  and (bti.name='MM: Planning & Analysis Complete' or bti.name = 'MM: Implementation Complete') 
  and ROWNUM<2 
  and (ti.name='MM: Planning & Analysis Complete' or ti.name = 'MM: Implementation Complete') 
  and ROWNUM<2 
  and bta.task_schedule_id = bts.task_schedule_id
  and ta.task_schedule_id = ts.task_schedule_id



RESULTING VALUE IS......
Delivery 
-------- 
41       

 

 

Honored Contributor
AlexSavencu
Posts: 595
Registered: ‎04-30-2010
Message 2 of 9 (768 Views)

Re: Trying to run SQL/CASE statement to get value then populate field

Hi,

For this kind of requirement we encapsulate the logic of the SQL statement on an Oracle function and we call it in a PPM execution step. Then, we route the workflow based on the returned value.

For updating the fields, we use direct database update, mostly because our requirements are quite complex.


Cheers
Alex

--remember to kudos people who helped solve your problem
Honored Contributor
Utkarsh_Mishra
Posts: 713
Registered: ‎07-13-2010
Message 3 of 9 (757 Views)

Re: Trying to run SQL/CASE statement to get value then populate field

Yes, using function would be good, you can use it in RULES as well as in Execution step. Secondly if any modifcation is required in future then you just need to only modify the function.

 

In SQL it will be better to use [REQ.REQUEST_ID] instead of '[REQD.VP.PROJECT]'  i.e. fg.request_id

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
Advisor
Frankie_Kings
Posts: 26
Registered: ‎04-08-2010
Message 4 of 9 (738 Views)

Re: Trying to run SQL/CASE statement to get value then populate field

Without having much experience with the ‘oracle function’ process, I gather that I would get our PPM tech administrator or DBA administrator to create the Oracle function with our SQL and name it (GET_DEL_VAR, for example).

 

Once that is defined, would I then use the following syntax in the  execution step to call the function..…

 

GET_DEL_VAR([REQ.REQUEST_ID])

 

REQ.REQUEST_ID being the token I need to pass from the request/workflow to the function?

 

Thank you so much for the help and any suggestions.

Frank

Honored Contributor
Erik Cole
Posts: 871
Registered: ‎02-11-2008
Message 5 of 9 (732 Views)

Re: Trying to run SQL/CASE statement to get value then populate field

Hi,

Both execution step and request rules can store the value on the request. If you use a request rule, I'd agree with wrapping it in a function because you're limited to how long your sql statement can be. Keep in mind that anything you do needs to return two values (even though they'r ethe same); one for hidden and one for visible parameter of the field:

select GET_DEL_VAR([REQ.REQUEST_ID]),GET_DEL_VAR([REQ.REQUEST_ID])
from dual

Also agree with Utkarsh that you want to use request_id in the sql.

Not sure what you're trying to do with the rownum<2 in there...that's a potential source of errors. Could a project have completed both 'MM: Planning & Analysis Complete' and 'MM: Implementation Complete' and if so, which are you asking for?

Honored Contributor
AlexSavencu
Posts: 595
Registered: ‎04-30-2010
Message 6 of 9 (703 Views)

Re: Trying to run SQL/CASE statement to get value then populate field

Hi,

 

adding to comments from Utkarsh and Erik, it is a must that all possible results of the function to be not null (your first branch returns NULL), AND they should be the very same as the hidden values of the validation linke to the step source.

 

cheers

alex


--remember to kudos people who helped solve your problem
Advisor
Frankie_Kings
Posts: 26
Registered: ‎04-08-2010
Message 7 of 9 (674 Views)

Re: Trying to run SQL/CASE statement to get value then populate field

Alex, thank you for the advice.  We are currently trying to review the 'null' values on the SQL we came up with originally so once we do that we will try this.  But would it still be possible that I can use the full SQL within an execution step (without utilizing the oracle function route)?   If I can get a value returned in the the execution step, can I still route the workflow on a  success or failure result?  Just checking into possibilities.  Appreciate your help.

Honored Contributor
AlexSavencu
Posts: 595
Registered: ‎04-30-2010
Message 8 of 9 (662 Views)

Re: Trying to run SQL/CASE statement to get value then populate field

Yes, you can.

 

You have to make sure of the following:

- all possible outcomes of the SQL must be treated, such as null values AND empty rowsets. You should make sure that your query retrieves 1 row exactly.

- all possible outcomes must match exactly all enabled transitions of the execution step.

 

cheers

alex


--remember to kudos people who helped solve your problem
Advisor
Frankie_Kings
Posts: 26
Registered: ‎04-08-2010
Message 9 of 9 (634 Views)

Re: Trying to run SQL/CASE statement to get value then populate field

thank you Alex and all for your advice. We are still investigating and testing these solutions and will see where that takes us.  Appreciate the help.

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.