Using Oracle SQL as a matching engine

Question

I am not sure if what Im looking for is possible, but its certainly interesting to think about. My goal is to optimize the layout of a warehouse, but this problem is re applicable to other scenarios.

I have a list of locations, the distance to the door and the zone they are in (e.g. Zone A= Refrigerated, Zone B= Ambient):

Location    Zone    Distance
A1          A       1
A2          A       3
A3          A       5
A4          A       7
B1          B       2
B2          B       4
B3          B       6
B4          B       8

I also have a list of products, the amount of times they were picked and their zone (Zone A= Products that need to be refrigerated, Zone B= Products that need to be outside refrigeration, Zone A/B= Products with NO restriction)

Product     Zone    Pick per day
Milk        A       8
Lettuce     A/B     7
Bread       B       6
Chocolate   B       5
Tomatoes    A/B     4
Dry Pasta   B       3
Beef        A       2
Chicken     A       1

If I was only interested in optimizing the distance traveled to pick the product, I would put the most picked closest to the door. I have done this by joining both tables through their ROW_NUMBER and ORDER BY Distance and Pick.

HOWEVER, I really care about the product Zone, since I cant store my Bread in the refrigerated area (it could go soggy). Additionally, I know that Lettuce could be stored in the refrigerated OR non refrigerated area.

ADDITIONALLY, there should be a condition that ensures ALL products are allocated a space (8 spaces 8 products)

Doing it manually I would get.

       
Location    Zone    Distance    Product     Zone    Pick per day
A1          A       1           Milk        A       8
B1          B       2           Lettuce     A/B     7
A2          A       3           Tomatoes    A/B     4
B2          B       4           Bread       B       6
A3          A       5           Beef        A       2
B3          B       6           Chocolate   B       5
A4          A       7           Chicken     A       1
B4          B       8           Dry Pasta   B       3

I have looked at the SQL Pattern Matching but was unsuccessful. Also, I have programmed an iterative VBA function that uses "Reserved" spaces in zones, but this is to slow for a home PC.

Finally, Thanks! I have been reading posts in stackoverflow for all my problems, but this one I cant solve!!

Answer 1

a. start with this sql:

select * from product p left outer join location l on (INSTR(p.zone, l.zone)>0) order by p.pickperday desc, l.distance asc;

b. write a stored procedure where you

1. create a hashset
2. start loop over resultset
3. take current element and if it is not allready in hashset and location is also not allready in hashset insert it into hashset
4. return hashsetlist

how to wirte such procedures: http://docs.oracle.com/cd/B28359_01/java.111/b31225/cheight.htm#CHDCDHJD

for testing:

CREATE TABLE "PRODUCT" (    "PRODUCT" VARCHAR2(20), "ZONE" VARCHAR2(20), "PICKPERDAY" VARCHAR2(20));
CREATE TABLE "LOCATION" (   "LOCATION" VARCHAR2(20), "ZONE" VARCHAR2(20), "DISTANCE" VARCHAR2(20));

Insert into LOCATION (LOCATION,ZONE,DISTANCE) values ('A1','A','1');
Insert into LOCATION (LOCATION,ZONE,DISTANCE) values ('B1','B','2');
Insert into LOCATION (LOCATION,ZONE,DISTANCE) values ('A3','A','5');
Insert into LOCATION (LOCATION,ZONE,DISTANCE) values ('B2','B','4');
Insert into LOCATION (LOCATION,ZONE,DISTANCE) values ('A2','A','3');
Insert into LOCATION (LOCATION,ZONE,DISTANCE) values ('B3','B','6');
Insert into LOCATION (LOCATION,ZONE,DISTANCE) values ('B4','B','8');
Insert into LOCATION (LOCATION,ZONE,DISTANCE) values ('A4','B','2');

Insert into PRODUCT (PRODUCT,ZONE,PICKPERDAY) values ('Milk','A','8');
Insert into PRODUCT (PRODUCT,ZONE,PICKPERDAY) values ('Tomatos','A/B','4');
Insert into PRODUCT (PRODUCT,ZONE,PICKPERDAY) values ('Bread','B','6');
Insert into PRODUCT (PRODUCT,ZONE,PICKPERDAY) values ('Dry Pasta','B','3');
Insert into PRODUCT (PRODUCT,ZONE,PICKPERDAY) values ('Lettuce','A/B','7');
Insert into PRODUCT (PRODUCT,ZONE,PICKPERDAY) values ('Beef','A','2');
Insert into PRODUCT (PRODUCT,ZONE,PICKPERDAY) values ('Chocolate','B','5');
Insert into PRODUCT (PRODUCT,ZONE,PICKPERDAY) values ('Chicken','A','1');