So I have these tables:
SQL> select * from project;
PNAME PNUMBER PLOCATION DNUM
--------------- ---------- --------------- ----------
Virtualization 40 Houston 5
CRM 50 Stafford 4
Bladeservers 60 Houston 1
ProductX 1 Bellaire 5
ProductY 2 Sugarland 5
ProductZ 3 Houston 5
Computerization 10 Stafford 4
Reorganization 20 Houston 1
Newbenefits 30 Bellaire 4
9 rows selected.
SQL> select * from dept_locations;
DNUMBER DLOCATION
---------- ---------------
1 Houston
4 Stafford
5 Bellaire
5 Houston
5 Sugarland
30 Norfolk
30 Suffolk
7 rows selected.
I need to make a new column ptype that checks the plocation and the dlocation and sets ptype to LOCAL if any department location matched for the dnum in the project, otherwise the result should be REMOTE. Any clues on how to do this? Thanks,