Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Lookup table help

    I am trying to replace the following code with code that would allow me to use a lookup table that I have built, but I don't know how to. As we add locations, these IF statements get very large, so I'm trying to re-write the code to facilitate the addition of new locations, simply by adding a row(s) to the lookup table.

    Here is some of the code I want to replace:

    Code:
    IF sku_rec.loc = 'ATL' THEN
    	IF 	roc_stocking IN ( 'S', 'M' ) THEN
    		INSERT INTO stsc.temp_q_sku2
    				SELECT *
    				   FROM stsc.sku
    				WHERE item 	= sku_rec.item
    				      AND loc 	= 'ROC' ;
    	ELSIF	lan_stocking IN ( 'S', 'M' ) THEN
    		INSERT INTO stsc.temp_q_sku2
    				SELECT *
    				    FROM stsc.sku
    				WHERE item 	= sku_rec.item
    				      AND loc 	= 'LAN' ;
    	ELSIF	new_stocking IN ( 'S', 'M' ) THEN
    		INSERT INTO stsc.temp_q_sku2
    				SELECT *
    				    FROM stsc.sku
    				 WHERE item 	= sku_rec.item
    				       AND loc 	= 'NEW' ;
    	ELSIF	sea_stocking IN ( 'S', 'M' ) THEN
    		INSERT INTO stsc.temp_q_sku2
    				SELECT *
    				   FROM stsc.sku
    				WHERE item 	= sku_rec.item
    				      AND loc 	= 'SEA' ;
    	ELSE
    		DELETE FROM 	stsc.temp_q_sku1
    				WHERE CURRENT OF sku_cur;
    END IF;
    And here's the lookup table:


    Code:
    NON_STOCKING_LOC    STOCKING_LOC   PRECEDENCE
    ATL                    ROC             1     
    ATL                    LAN             2     
    ATL                    NEW             3     
    ATL                    SEA             4     
    LAN                    ROC             1     
    LAN                    ATL             2     
    LAN                    NEW             3     
    LAN                    SEA             4     
    NEW                    SEA             1     
    NEW                    ROC             2     
    NEW                    ATL             3     
    NEW                    LAN             4     
    ROC                    ATL             1     
    ROC                    NEW             2     
    ROC                    LAN             3     
    ROC                    SEA             4     
    SEA                    NEW             1     
    SEA                    ROC             2     
    SEA                    ATL             3     
    SEA                    LAN             4     
    TOR                    ROC             1     
    TOR                    LAN             2     
    TOR                    ATL             3     
    TOR                    SEA             4     
    TOR                    NEW             5
    That is, if an item is non-stocking at ATL, the system checks ROC first to see if it is stocking at that location, LAN second, NEW third, etc. If no stocking location is found, the records remain unmodified.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    The design of your lookup table is incomplete/incorrect because you have these location columns which drive the sequence (precedence):

    IF roc_stocking IN ( 'S', 'M' ) THEN
    IF lan_stocking IN ( 'S', 'M' ) THEN
    IF new_stocking IN ( 'S', 'M' ) THEN
    IF sea_stocking IN ( 'S', 'M' ) THEN

    You may need somehow to "add" the "IN ( 'S', 'M' )" values to a column in the lookup table or join to some other table to query this value.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    Thanks for the feedback. I'm not sure I understand your advice. The lookup table is static and is used merely to assertain what order to do the lookup. The "roc_stocking" information is available off another table.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by ssmith001
    Thanks for the feedback. I'm not sure I understand your advice. The lookup table is static and is used merely to assertain what order to do the lookup. The "roc_stocking" information is available off another table.
    That is what I mean, the column "roc_stocking" refers to the ROC location -- therefore when you use this column, you are already setting the order of precedence of location "ROC".
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Sep 2005
    Posts
    220
    Do you think it is possible to replace the large IF statement, or am I waisting my time? Every time we add a location this statement gets larger and larger, and it gets very messy to read. I was just hoping I could somehow do the lookup with a table.

  6. #6
    Join Date
    Sep 2005
    Posts
    220
    This is an axample of what I need to do. I am trying to rollup the quantities based on the stock lookup table:

    These are the quantities before the rollup:

    Code:
    ITEM             LOCATION             QTY      STOCKIND
    00041189              TOR                     6               S
    Now, go look at the lookup table, see that TOR rolls up to LAN, now add the qty of 6 to LAN location's qty of 10, so you get 1 row as follows:

    Code:
    ITEM             LOCATION             QTY      STOCKIND
    00041189              LAN                    16               S

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down


    How did you decide that TOR rolls-up to LAN?
    According to your lookup table TOR rolls-up to everything:
    Code:
    ...
    TOR                    ROC             1     
    TOR                    LAN             2     
    TOR                    ATL             3     
    TOR                    SEA             4     
    TOR                    NEW             5
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Sep 2005
    Posts
    220
    TOR rolls to ROC first, but ROC is non-stocking, so we then go the the second location of LAN.

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    Quote Originally Posted by ssmith001
    TOR rolls to ROC first, but ROC is non-stocking, so we then go the the second location of LAN.
    And, to know if ROC in "non-stocking" you have to query the ROC_STOCKING column in some other table.

    The only way of getting "rid" of the IF statements is to re-design your tables such as to get rid of the columns which specify a location name (like these):

    roc_stocking, lan_stocking, new_stocking, sea_stocking, etc...

    You may need a "location" lookup table which allows to know the location properties:

    LOCATIONS (LOC_ID, LOC_NAME, LOC_TYPE?);


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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