Skip to main content

According to a requirement in which we need to fetch SubTeam ID from table TEAM using some field values from REQUEST,PART,ADDRESS tables. This SubTeam ID then should be reflected in one of the REQUEST fields.We have written the sql query to find the particular result and also its corresponding XML that will be used in the XML Business Rule.

 

SQL QUERY →

select subteam.team_id from request, place, place_address, address, team, subteam, part, request_unit
where 1 = 1
and request.place_id = place.place_id
and request.request_id = request_unit.request_id
and request_unit.part_id=part.part_id
and place.place_id = place_address.place_id
and place_address.address_id = address.address_id
and address.city = team.team_id
and team.team_id = subteam.parent_team_id
and subteam.user_def10=part.part_type
and subteam.user_def11=request.req_type
and subteam.user_def12= '0'
and request.request_id = '853'

 

XML →

<hierarchy_select>
<attrs>
<attr>subteam.team_id</attr>
</attrs>
<primary_table>subteam</primary_table>
<from>
<table>request</table>
<table>place</table>
<table>request_unit</table>
<table>part</table>
<table>place_address</table>
<table>address</table>
<table>team</table>
<table>subteam</table>
</from>
<where>
<join_constraint>
<constraint>
<left_operand>request.place_id</left_operand>
<operator>eq</operator>
<right_operand>place.place_id</right_operand>
</constraint>
<constraint>
<left_operand>request.request_id</left_operand>
<operator>eq</operator>
<right_operand>request_unit.request_id</right_operand>
</constraint>
<constraint>
<left_operand>place.place_id</left_operand>
<operator>eq</operator>
<right_operand>place_address.place_id</right_operand>
</constraint>
<constraint>
<left_operand>part.part_id</left_operand>
<operator>eq</operator>
<right_operand>request_unit.part_id</right_operand>
</constraint>
<constraint>
<left_operand>address.city</left_operand>
<operator>eq</operator>
<right_operand>team.team_id</right_operand>
</constraint>
<constraint>
<left_operand>team.team_id</left_operand>
<operator>eq</operator>
<right_operand>subteam.parent_team_id</right_operand>
</constraint>
<constraint>
<left_operand>subteam.user_def10</left_operand>
<operator>eq</operator>
<right_operand>part.part_type</right_operand>
</constraint>
<constraint>
<left_operand>subteam.user_def11</left_operand>
<operator>eq</operator>
<right_operand>request.req_type</right_operand>
</constraint>
</join_constraint>
<data_constraint>
<constraint>
<left_operand>request.request_id</left_operand>
<operator>eq</operator>
<right_operand>853</right_operand>
</constraint>
<constraint>
<left_operand>subteam.user_def12</left_operand>
<operator>eq</operator>
<right_operand>0</right_operand>
</constraint>
</data_constraint>
</where>
</hierarchy_select>

 

Running this XML on the XML Poster is throwing this error:

The objects "request_unit" and "request_unit" in the FROM clause have the same exposed names

Also this XML is working fine without any error on other Environment but showing this error on IFS FSM 6

 

 

Hi @sahilkumar ,

Would suggest to have either a sql view for this particular query or to have a separate alias table for request_unit table as you have joined the same table which should have different table alias names to distinguish the two join constraints. 

Best option would be to create a sql view for the same sql query and define the custom metadata with the given view name. Then you can simply get the result from the xml by directly calling the view.

 

Hi @Saranga Amaraweera Thank you for the reply. But the same XML is getting executed without any error in another FSM environment (version 6.12.7886.1000). But we are using version 6.14.7977.1000 where we are getting this error :

<subteam_hierarchy_select_result>
  <result type="Exception">
    <error>
      <system_error>
        <severity>ERROR</severity>
        <message>The database command failed and reported the following error: The objects "request_unit" and "request_unit" in the FROM clause have the same exposed names. Use correlation names to distinguish them.</message>
      </system_error>
    </error>
  </result>
</subteam_hierarchy_select_result>

 

I understand using an sql view is always an option but this requirement is a very simple one which can be easily achieved using multiple joins which I have seen working in the past so we want to refrain from using sql view.

 

Thanks,

Shamika Chatterjee

Hi @Shamika ,

I’m not quite sure how you have managed to get this working in the U12 version. What are the database versions that you have pointed for these two particular fsm instances (in U12 and U14)? Usually according to sql standards this error happens when you reference a table at least twice in the FROM clause and you did not specify a table alias to either table so that SQL Server can distinguish one from the other. In your case the request_unit table is called twice in the join constraint. If it is the case(if you do not wish to add a view), perhaps try to create an alias table through custom metadata for the request_unit table and use that in the join constraint.

Reply


Terms & ConditionsCookie settings