Derive ItmsGrpCod by looking up Group Name in Integration Hub
Answered
Hi,
We would are importing Item Master Data via the Integration Hub and would like to use a SQL Conversion on the Item Group field to read in the Item Group Name (ItmsGrpNam) from the source file and convert it to the corresponding Item Group Code (ItmsGrpCod).
It looks like in the Source - Target - Link page you can define a Conversion Formula. Can I specify the table value (item group name in this case) as part of the formula.
I.E. I am trying to resolve the code using the following SQL but am not sure of the syntax to reference the value in the uploaded item group name column.
select "OITB"."ItmsGrpCod" from "OITB" WHERE "OITB"."ItmsGrpNam" = [@Table Value]
Cheers,
Sean
-
Ok looked at the example screenshot properly and are now determining the correct substitution value.
But need help returning the SQL value rather than the SQL Command. I have setup the following conversion formula which is substituting in the correct value for Item Group Name but is not executing the SQL Command and returning the result.
setvar=value=sqlexecute=SELECT ItmsGrpCod FROM OITB WHERE ItmsGrpNam = '<import.ItemGroup>'
-
Hi,
correct is
sql=SELECT "ItmsGrpCod" into "Value" FROM "OITB" WHERE "ItmsGrpNam" = '<import.ItemGroup>'
-
Hi Martin,
Thank you for the reply. It has led me to a working solution.
I received the following error when trying to set the "Value" directly in SQL.
() Error in sql statement: (-1) SQLSTATE = S0001 [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'Value' in the database./2714: SELECT "ItmsGrpCod" into "Value" FROM "OITB" WHERE "ItmsGrpNam" = 'Boats'
But I found the following did work...
Cheers,
Sean
Please sign in to leave a comment.
Comments
3 comments