IT Process Automation

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

Process Automation uses the jtds JDBC driver to connect to Sql Server.  There may be parts of your sql statement that are not supported by this driver.  You can read more about this driver here:  jTDS JDBC Driver 

Otherwise, I would recommend sharing the sql statement with your dba to see if they have an idea of why you might be getting different results.

Hi Hemalatha,

this example is from my environment. This works without problem.

If part of your query is not supported as wrote Andrew than you can try to write a sql function and call the function from PAM.

Regards Milan