4 Replies Latest reply: Apr 24, 2017 10:23 AM by Magnus Rydberg RSS

    Using Map-function (Applymap) in a sql statement

    Magnus Rydberg

      Hi,

       

      I'm trying to use "Applymap" in a SQL-statement but I don't manage to get it work. Maybe it isn't possible?

      Code exemple:

       

      MapItemnumbertoItemGroup:

      MAPPING LOAD  ItemNumber as CompanyItemNumber,

             ItemGroup as CompanyItemGroup;

             SQL SELECT * FROM "MyL_PROD_220".OTSDATA.CompanyItem;

       

      LOAD LONumber,

          Network,

          OrderType,

          LOType,

          TransportCompany as Company,

          TransportFacility as Facility;

        

      SQL SELECT *

      FROM "MyL_PROD_220".OTSDATA.LOHeader where Network='Interlink' and Status='90';

       

      Left Join

      LOAD

          LONumber,

          ApplyMap('MapItemnumbertoItemGroup', ItemNumber, null()) as MaterialItemGroup,

          ReportedQty as MaterialReportedQty,

          ItemNumber as MaterialItemNumber;

       

      SQL SELECT * FROM "MyL_PROD_220".OTSDATA.LOMaterial where (ApplyMap('MapItemnumbertoItemGroup', ItemNumber, null())='Tid') ;

       

      Is there any other alternative solution?

      My original problem is really that I want to do a select of records from LOMaterial depending on a field (ItemGroup) in CompanyItem.

       

      Thanks in advance!

      // Magnus