I have XML type Business Rule from where I am firing the xml:
<perform_batch>
<sequential_dependent>
<hierarchy_select result_name="select_task1">
<primary_table>task</primary_table>
<from>
<table>task</table>
<table>tdc_central</table>
</from>
<attrs>
<attr>task.task_id</attr>
<attr>task.user_def11</attr>
<attr>tdc_central.circuit</attr>
<attr>tdc_central.skill</attr>
</attrs>
<where>
<data_constraint>
<constraint>
<left_operand>task.task_id</left_operand>
<operator>eq</operator>
<right_operand>@task_id</right_operand>
</constraint>
<constraint>
<left_operand>task.task_type</left_operand>
<operator>eq</operator>
<right_operand>CABLE_FAULT</right_operand>
</constraint>
<constraint>
<left_operand>tdc_central.skill</left_operand>
<operator>ne</operator>
<right_operand>null</right_operand>
</constraint>
</data_constraint>
<join_constraint>
<constraint>
<left_operand>task.user_def11</left_operand>
<operator>equi</operator>
<right_operand>tdc_central.circuit</right_operand>
</constraint>
</join_constraint>
</where>
</hierarchy_select>
<update_task_skill>
<task_skill>
<task_id xpath_node="//task_hierarchy_select_resulte@result_name='select_task1']/task/task_id" />
<skill xpath_node="//task_hierarchy_select_resultb@result_name='select_task1']/task/tdc_central/skill" />
<insert />
</task_skill>
</update_task_skill>
</sequential_dependent>
</perform_batch>
In this xml query there is join constrant:
<join_constraint>
<constraint>
<left_operand>task.user_def11</left_operand>
<operator>equi</operator>
<right_operand>tdc_central.circuit</right_operand>
</constraint>
</join_constraint>
Here Task.user_def11 may return camel case value or lower case value, we are not sure about case combination but tdc_central.circuit will give always a UPPER CASE value. Is there any way we can ignore the case in this join constraint so that it works for all combination of value in the column Task.user_def11?
I believe that should work. You are using Oracle right? Any idea what the following is set to in web.config?
<appSettings
<!--True to enable case insensitve search in Oracle-->
<add key="OracleMixedCaseSearch" value="true"/>
</appSettings>
I tried joining Task Description with the value “COMMUNITY” with Place Name with the value “Community” and it worked.
<hierarchy_select result_name="select_task1">
<primary_table>task</primary_table>
<from>
<table>task</table>
<table>place</table>
</from>
<attrs>
<attr>task.task_id</attr>
<attr>task.description</attr>
<attr>place.name</attr>
</attrs>
<where>
<data_constraint>
<constraint>
<left_operand>task.task_id</left_operand>
<operator>eq</operator>
<right_operand>@task_id</right_operand>
</constraint>
</data_constraint>
<join_constraint>
<constraint>
<left_operand>task.description</left_operand>
<operator>equi</operator>
<right_operand>place.name</right_operand>
</constraint>
</join_constraint>
</where>
</hierarchy_select>
Returns the following.
<task_hierarchy_select_result result_name="select_task1">
<task>
<metrix_row_num>1</metrix_row_num>
<description>COMMUNITY</description>
<task_id>248</task_id>
<place>
<metrix_row_num>1</metrix_row_num>
<name>Community</name>
<place_id>MD102</place_id>
</place>
</task>
</task_hierarchy_select_result>
Thanks Anmise…
I checked in my web.config. It is false.
<add key="OracleMixedCaseSearch" value="false" />
But I am afraid, If I change this to true, it will not impact the FSM in bad way right?
Thanks Anmise…
I checked in my web.config. It is false.
<add key="OracleMixedCaseSearch" value="false" />
But I am afraid, If I change this to true, it will not impact the FSM in bad way right?
Not even sure it makes any difference in this case to be honest, but yes it can impact the overall performance, so I probably wouldn't touch it.
Is there a reason one of the values is forced upper in metadata and the other isn't? What is the result if you runt the hierarchy select in the XML poster, just no result?
It is not like that I have forcefully make the value off USER_DEF11 to UPPER case. In which case combination the value of USER_DEF11 will come from upstream system, we do not know. That is the reason I want to make both the column in same case (UPPER or LOWER case). But I have tried with below:
<perform_exec_db_edit>
<parameters>
<sql_command>insert into task_skill(task_id,skill) select t.task_id,tc.skill from task t,tdc_central tc
where UPPER(t.USER_DEF11)=UPPER(tc.circuit) and t.task_id='@expressionstask.task_id]' and t.task_type='CABLE_FAULT' and tc.skill is not null</sql_command>
</parameters>
</perform_exec_db_edit>
It is working as intended but I am not sure whether it will create any performance issue or not. Could you please advice?
It is not like that I have forcefully make the value off USER_DEF11 to UPPER case. In which case combination the value of USER_DEF11 will come from upstream system, we do not know. That is the reason I want to make both the column in same case (UPPER or LOWER case). But I have tried with below:
<perform_exec_db_edit>
<parameters>
<sql_command>insert into task_skill(task_id,skill) select t.task_id,tc.skill from task t,tdc_central tc
where UPPER(t.USER_DEF11)=UPPER(tc.circuit) and t.task_id='@expressionstask.task_id]' and t.task_type='CABLE_FAULT' and tc.skill is not null</sql_command>
</parameters>
</perform_exec_db_edit>
It is working as intended but I am not sure whether it will create any performance issue or not. Could you please advice?
Sorry, hard to say without knowing the detail of the use case, the volume etc. I suggest that you discuss that with an IFS resource who is more familiar with your solution.
If you just run the hierarchy select for one of the examples where the data has camel case characters, what does it return?
