Solved

IN clause not working as expected in tranformations

  • 19 July 2023
  • 5 replies
  • 77 views
A
Rank
Userlevel 1

I am running a transformation where in IN Clause I am giving values by selecting from mapping file in raw (which stores value as Item1, Item2)

but not getting any desired results (giving 0 rows but not giving any error)

e.g

SELECT * from tableName

where columnName IN (SELECT propertyName from rawDB.mapping where columnName=Id)

 

 

icon

Best answer by Sudil Abeyagunasekera 25 July 2023, 20:03

View original

5 replies

S
Rank
Badge +1

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

A
Rank
Userlevel 1

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.

S
Rank
Badge +1

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

A
Rank
Userlevel 1

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.

S
Rank
Badge +1

Hi @Amit Rane 

 

Could you please modify your query as below and give a try. Since the FIRST() always returning a single value I guess it is causing the issue. So I modified the query with COLLECT_LIST().

 

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
JOIN
  `src:001:avocet:db`.`avocet_001_pdm_structure_mapping` AS mapping
  ON ITEM.ITEM_TYPE = mapping.source_entity_type
JOIN
  `src:001:avocet:db`.`ITEM` AS ITEM
  ON ITEM.ITEM_ID = LINK.FROM_ITEM_ID
WHERE
  LINK.LINK_TYPE IN (
    SELECT
      CONCAT("'", REPLACE(CONCAT_WS(",", COLLECT_LIST(source_link_type)), ",", "','"), "'")
    FROM
      `src:001:avocet:db`.`avocet_001_pdm_structure_mapping` AS structure_mapping
      JOIN
      `src:001:avocet:db`.`ITEM` AS structure_ITEM
      ON structure_ITEM.ITEM_TYPE = structure_mapping.source_entity_type
    GROUP BY structure_mapping.source_entity_type
  );

 

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

Reply


Terms & ConditionsCookie Settings