Impact 2024: The Industrial Data and AI Conference for and by Users | Nominate Speakers Now for a Ch...
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 endNodeFROM `src:001:avocet:db`.`ITEM_LINK` AS LINKJOIN `src:001:avocet:db`.`avocet_001_pdm_structure_mapping` AS mapping ON ITEM.ITEM_TYPE = mapping.source_entity_typeJOIN `src:001:avocet:db`.`ITEM` AS ITEM ON ITEM.ITEM_ID = LINK.FROM_ITEM_IDWHERE 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
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 endNodeFROM `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 ITEMWHERE 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 @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.mapping2. 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=Id4. 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,
I restarted the environment and tried, and it executed without an error.Thank You for the guidance.
It gives false as it should be to occur the error. In the github repo. I think it is importing from wrong folder. (Not sure though). This may work.
I am using latest version of the Cognite sdk I guess. If you have any workaround, please let me know.
Already have an account? Login
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.
Sorry, our virus scanner detected that this file isn't safe to download.
Check the
documentation
Ask the
Community
Take a look
at
Academy
Cognite
Status
Page
Contact
Cognite Support
