AnsweredAssumed Answered

Query Database in PAM

Question asked by HemalathaShanmugam75351634 on Sep 7, 2016
Latest reply on Sep 7, 2016 by milan.ziga

Hi Team,

 

We are using CA Process Automation 4.2 SP01.

 

I am using the below query in PAM in Query Database component. When executed in SQL managerment studio, I am getting the desired results. But when used in PAM, process gets executed without any errors but the rowcount is 0.

 

Please check and let me know what went wrong.

 

Process.SQL_Query = "select u.Request_id, u.Created_date,urd.item_text as Request_Name,u.req_by_user_id as User_ID,    substring(u1.form_elem_value,1,datalength(u1.form_elem_value)/4) as User_Name,  substring(u2.form_elem_value,1,datalength(u2.form_elem_value)/4) as Employee_No,    substring(u3.form_elem_value,1,datalength(u3.form_elem_value)/4) as Email_ID,   substring(u4.form_elem_value,1,datalength(u4.form_elem_value)/4) as Department,   substring(u5.form_elem_value,1,datalength(u5.form_elem_value)/4) as Location  from  usm_request_item_form u1 ,usm_request_item_form u2 ,   usm_request_item_form u3,   usm_request_item_form u4,usm_request_item_form u5,   usm_subscription_detail usd, usm_rate_definition urd,usm_request u where u1.subscription_detail_id in  (select max(subscription_detail_id)+1   from usm_request_status where request_id in   (select request_id from usm_request    where name ='US Ops Zone AS4755' and created_date>'2016-08-01' and created_date<'2016-08-03')   group by request_id)    and u1.form_elem_name='user_name' and u.request_id in   (select request_id from usm_request_status  where   subscription_detail_id+1 = u1.subscription_detail_id)  and  u2.form_elem_name='employee_id' and    u1.subscription_detail_id=u2.subscription_detail_id   and u3.form_elem_name='email' and    u2.subscription_detail_id=u3.subscription_detail_id   and u4.form_elem_name='department' and    u3.subscription_detail_id=u4.subscription_detail_id   and u5.form_elem_name='location' and u4.subscription_detail_id=u5.subscription_detail_id  and u.request_id=usd.request_id and usd.item_id=urd.item_id and urd.rate_col=0 ";

 

Thanks,

Hemalatha S

Outcomes