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 ";
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.
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.