IN clause not working as expected in tranformations

Hi @Amit Rane 

Since your query not having any syntax errors and is executed without throwing an error there could be several reasons for this behavior. I can suggest the below troubleshooting steps.

1. Ensure that the data you are trying to match exists in both tables by running below queries.
   SELECT columnName
   FROM tableName

   SELECT propertyName
   FROM rawDB.mapping

2. Check whether the columnName field in tabelName is having same data type as the propertyName field in rawDB.mapping. Go to Integrate → Prepare Data For Transformation → RAW explorer, then click on the tables and double click on the columns to check the data type.

3. Debug the query by ensuring the subquiery returns desired results.
   SELECT propertyName
   FROM rawDB.mapping
   WHERE columnName=Id

4. Try to use the JOIN clause if the above steps won't work.
    SELECT t.*
    FROM tableName t
    JOIN rawDB.mapping m ON t.columnName = m.propertyName
    WHERE m.columnName = 'Id';

Hope this will be helpful to you.
Best regards,
Sudil

Hi @Sudil Abeyagunasekera 

I have already tried the first 3 suggestion but still getting the 0 rows.

for more reference I am using this query:

SELECT
  concat(TO_ITEM_ID, ':', FROM_ITEM_ID) AS externalId,
  node_reference('PDM', TO_ITEM_ID) AS startNode,
  node_reference('PDM', FROM_ITEM_ID) AS endNode
FROM
  `src:001:avocet:db`.`ITEM_LINK` AS LINK,
  `src:001:avocet:db`.`avocet_001_pdm_structure_mapping` AS mapping,
  `src:001:avocet:db`.`ITEM` as ITEM
WHERE
  ITEM.ITEM_ID = LINK.FROM_ITEM_ID
  AND ITEM.`ITEM_TYPE` = mapping.`source_entity_type`
  AND LINK.`LINK_TYPE` IN (
    select
      concat(
        "'",
        replace(first(source_link_type), ",", "','"),
        "'"
      )
    from
      `src:001:avocet:db`.`avocet_001_pdm_structure_mapping` AS strtcure_mapping,
      `src:001:avocet:db`.`ITEM` AS structure_ITEM
    where
      structure_ITEM.`ITEM_TYPE` = strtcure_mapping.`source_entity_type`
  )

and if directly used 'first_result','second_result' in IN Clause its giving desired result.

Hi @Amit Rane 

I guess this is the reason.

If using 'first_result' and 'second_result' directly in the IN clause is giving the desired result, then it suggests that the source_link_type column in the avocet_001_pdm_structure_mapping table contains these exact values. In that case, using FIRST() in the subquery is not necessary, and you can directly specify the values as literals in the IN clause.

May be query modification as below will help to resolve the issue.

SELECT
  CONCAT(TO_ITEM_ID, ':', FROM_ITEM_ID) AS externalId,
  node_reference('PDM', TO_ITEM_ID) AS startNode,
  node_reference('PDM', FROM_ITEM_ID) AS endNode
FROM
  `src:001:avocet:db`.`ITEM_LINK` AS LINK,
  `src:001:avocet:db`.`avocet_001_pdm_structure_mapping` AS mapping,
  `src:001:avocet:db`.`ITEM` as ITEM
WHERE
  ITEM.ITEM_ID = LINK.FROM_ITEM_ID
  AND ITEM.`ITEM_TYPE` = mapping.`source_entity_type`
  AND LINK.`LINK_TYPE` IN ('first_result', 'second_result');

Hope this will be helpful to you.
Best regards,
Sudil

Hi @Sudil Abeyagunasekera 

The literals values may change based on condition that's why we wanted to get that result on runtime from the mapping.