5 Replies Latest reply: May 7, 2012 12:46 AM by devang sanghavi RSS

    Multiple lookup

      Hi Please help me on below.


      I have two tables and i need to limit the second table based on first table on 2 fileds.


      Table A

      Employee Code

      Employee Name


      Table B

      Order Number

      Sales_responsible_Employee code

      Delivery_responsible_Employee code




      What I need is that table B should only load the rows where either sales responsible or delivery responsible is within a given list of employee names from table A (say 'Marc', 'Peter', 'Mike').


      Is it possible to make such a script ? If yes, kindly giude me on how can it be done ?


      Many thanks in advance,



        • Multiple lookup

          You could try the exists function.

          For instance when loading the second table you could use:

          where exists(Employee Code,Sales_responsible_Employee code)

          • Multiple lookup
            Goran Korsgren

            and if you want to check either table:


            where exists(Employee Code,Sales_responsible_Employee code)

            or exists(Employee Code,Delivery_responsible_Employee code)

            • Re: Multiple lookup

              Exist and OR may slow down the script performance. You can use right and outer join to load the required data.



              Load EmpCode, EmpName

              from <tablename>;



              rightjoin(Emp) load

              Order Number ,

              Sales_responsible_Employee_code as EmpCode,

              Sales_responsible_Employee_code as SREC1,

              Delivery_responsible_Employee_code as DREC1


              from <Ordertable>;






              Order Number ,

              Sales_responsible_Employee_code as SREC2,

              Delivery_responsible_Employee_code as EmpCode,

              Delivery_responsible_Employee_code as DREC2


              from <Ordertable>;


              //Outer Join on Order Number Column


              outer join(Order1) load Order Number, SREC2, EmpCode as EmpCode2, DREC2  from Order2;



              You can check the performance of both of the script if you data is very huge and then decide.


              Hope this help,

              • Multiple lookup

                Thanks guys for all your contributions. I think i will try Gandalf and Anosh's solution and that will do the trick. Once again thanks a ton buddies, you rock :-)