1. Registered User
Join Date
Sep 2005
Posts
220

I have a situation where I need to use the following logic. If an item is non-stocking at LOC1, the system checks LOC4 first to see if it is stocking at that location, LOC2 second, LOC3 third, etc. If no stocking LOC is found, the records remain unmodified.

Code:
```		     Stocking LOC (in order of precedence)
Non-Stocking LOC            1         2           3         4         5

LOC1                LOC4    LOC2     LOC3     LOC5
LOC2                LOC4    LOC1     LOC2     LOC5
LOC3                LOC5     LOC4    LOC1     LOC2
LOC4                LOC1    LOC3     LOC2     LOC5
LOC5	           LOC3    LOC4     LOC1     LOC2
LOC6                LOC4     LOC2    LOC1      LOC5     LOC3```
Right now we have a whole series of IF statements to work through this logic. When we add a location, a lot of code needs to be changed. Is there a way to build this into a table and be able to do the lookup more effectively, and that would make it easier to add a location?

2. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
>Is there a way to build this into a table and be able to do the lookup more effectively,
I say there is an easier way.
I suspect a simple lookup table & a recursive PL/SQL procedure would solve it

3. Drunkard
Join Date
Nov 2002
Location
Desk, slightly south of keyboard
Posts
697
Code:
```create table stock (location varchar2(4), quantity number(3));
create table location_priority (desired varchar2(4), dorder number(1), possible varchar2(4));
insert into stock values ('LOC1',0);
insert into stock values ('LOC2',1);
insert into stock values ('LOC3',3);
insert into stock values ('LOC4',7);
insert into stock values ('LOC5',0);
insert into location_priority values ('LOC1',1,'LOC1');
insert into location_priority values ('LOC1',2,'LOC4');
insert into location_priority values ('LOC1',3,'LOC2');
insert into location_priority values ('LOC1',4,'LOC3');
insert into location_priority values ('LOC1',5,'LOC5');
insert into location_priority values ('LOC2',1,'LOC2');
insert into location_priority values ('LOC2',2,'LOC4');
insert into location_priority values ('LOC2',3,'LOC1');
insert into location_priority values ('LOC2',4,'LOC2');
insert into location_priority values ('LOC2',5,'LOC5');

-- obtain locations with stock in order of preference for LOC1
select location,
quantity
from   stock s,
location_priority l
where  s.location = l.possible and
l.desired  = 'LOC1' and
s.quantity > 0
order  by l.dorder

LOCATION QUANTITY
======== ========
LOC4     7
LOC2     1
LOC3     3```
Adding more locations is simply a case of adding new rows into location_priority.

Hth
Bill

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•