Results 1 to 3 of 3

Thread: Lookup table

  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Lookup table

    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?

    Thanks in advance.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    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
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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