Geek Answers Handbook

Using Oracle SQL as an appropriate mechanism

I'm not sure what I'm looking for is possible, but its certainly interesting to think about. My goal is to optimize the repository layout, but this problem applies to other scenarios as well.

I have a list of locations, the distance to the door, and the zone in which they are located (for example, zone A = refrigerator, zone B = environment):

  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 time that they have been selected, and their zone (zone A = products that need to be cooled, zone B = products that should be outside the refrigeration equipment, zone A / B = products with NO)

  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 select a product, I would put it closest to the door. I did this by joining both tables via ROW_NUMBER and ORDER BY Distance and Pick.

HOWEVER, I really care about the grocery area, since I cannot store my Bread in a chilled area (this can become raw). In addition, I know that salad can be stored in a chilled or uncooled area.

Additionally, there must be a condition guaranteeing that all products are allocated space (8 spaces 8 products)

By doing this 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 looked at SQL pattern matching, but was unsuccessful. In addition, I programmed an iterative VBA function that uses "reserved" spaces in zones, but it slows down for a home PC.

Finally, thanks! I read the posts on stackoverflow for all my problems, but this one I cannot solve !!

+6
source share
1 answer

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

  • create hashset
  • start a set of results
  • take the current element and if it is not hasready in hashset and the location is also not allready in hashset insert it in hashset
  • return hashsetlist

how to perform 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');
+2
source

More articles:


All Articles