Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
amount
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,
Devang.
You could try the exists function.
For instance when loading the second table you could use:
where exists(Employee Code,Sales_responsible_Employee code)
and if you want to check either table:
where exists(Employee Code,Sales_responsible_Employee code)
or exists(Employee Code,Delivery_responsible_Employee code)
Exist and OR may slow down the script performance. You can use right and outer join to load the required data.
Emp:
Load EmpCode, EmpName
from <tablename>;
Order1:
rightjoin(Emp) load
Order Number ,
Sales_responsible_Employee_code as EmpCode,
Sales_responsible_Employee_code as SREC1,
Delivery_responsible_Employee_code as DREC1
amount
from <Ordertable>;
Order2:
noconcatenate(Order1)
rightjoin(Emp)
load
Order Number ,
Sales_responsible_Employee_code as SREC2,
Delivery_responsible_Employee_code as EmpCode,
Delivery_responsible_Employee_code as DREC2
amount
from <Ordertable>;
//Outer Join on Order Number Column
Order:
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,
Anosh
You can also use lookup function
where lookup('Emp Code', 'Emp Name','Sales_responsible_Employee code') is not null
or lookup('Emp Code', 'Emp Name','Delivery_responsible_Employee code') is not null
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 🙂