Inventory by LocationId (were locations are hierarchical)

[former_member1021736]

Hi,

I have a problem with an inventory system I'm designing.  It is an inventory system by LocationId.   The problem I have is that the locations are hierarchical.   By hierarchical I mean that a given location might belong to a next location.

For example given the following (simplified) schema of 3 tables (Locations, Articles and Inventory)

CREATE  TABLE Locations (

LocationId                 INTEGER NOT NULL,

ParentLocationId           INTEGER NULL,

CONSTRAINT w_Ub_LLAVE PRIMARY KEY (LocationId)

)

GO

INSERT INTO Locations (LocationId,ParentLocationId) VALUES (1,NULL);

INSERT INTO Locations (LocationId,ParentLocationId) VALUES (2,NULL);

INSERT INTO Locations (LocationId,ParentLocationId) VALUES (3,NULL);

INSERT INTO Locations (LocationId,ParentLocationId) VALUES (4,1);

INSERT INTO Locations (LocationId,ParentLocationId) VALUES (5,4);

INSERT INTO Locations (LocationId,ParentLocationId) VALUES (6,4);

INSERT INTO Locations (LocationId,ParentLocationId) VALUES (7,5);

CREATE TABLE Articles  (

ItemId                     INTEGER NOT NULL,

Description                VARCHAR(50),

CONSTRAINT ArticlePK PRIMARY KEY (ItemId)

)

GO

INSERT INTO Articles (ItemId,Description)  VALUES (100,'BREAD');

CREATE TABLE Inventory (

LocationId                 INTEGER NOT NULL,

ItemId                     INTEGER NOT NULL,

Qty                        DECIMAL(14,4) NULL,

CONSTRAINT InventoryPK PRIMARY KEY (LocationId,ItemId)

)

GO

INSERT INTO Inventory (LocationId,ItemId,Qty) VALUES (1,100,5);

INSERT INTO Inventory (LocationId,ItemId,Qty) VALUES (2,100,5);

INSERT INTO Inventory (LocationId,ItemId,Qty) VALUES (3,100,5);

INSERT INTO Inventory (LocationId,ItemId,Qty) VALUES (4,100,5);

INSERT INTO Inventory (LocationId,ItemId,Qty) VALUES (5,100,5);

INSERT INTO Inventory (LocationId,ItemId,Qty) VALUES (6,100,5);

INSERT INTO Inventory (LocationId,ItemId,Qty) VALUES (7,100,5);

I would like to list the contents by locationId.

For example, if I list LocationId=1 I should get

5 (in LocationId = 1) +

5 (in LocationId = 4 which belongs to 1) +

5 (in LocationId=5 which belongs to 4 which belongs to 1) +

5 (in LocationId=6 which belongs to 4 which belongs to 1) +

5 (in LocationId=7 which belongs to 5 which belongs to 4 which belongs to 1)

= 25

If I list LocationId=7 I would only get 5 (those in locationId=7)

If I list LocationId=5 I would bet 10 (those in LocationId=5 and those in LocationId=7).

Hopefully I was able to explain myself.   Does anybody know how this could be done?

Thanks,

Edgard

[jeff_albion · Accepted solution]

Hi Edgard,

The first part of your problem is a common recursive query problem. You will want to first create a list of related Location IDs based on an initial Location ID:

WITH RECURSIVE
  location_table ( LocationId, ParentLocationId, location_level ) AS
( ( SELECT LocationId, ParentLocationId, 0
    FROM Locations AS l
    WHERE ParentLocationId IS NULL )            -- initial query
  UNION ALL
  ( SELECT l.LocationId, l.ParentLocationId, lt.location_level + 1
    FROM Locations AS l
    JOIN location_table AS lt
      ON l.ParentLocationId = lt.LocationId
     AND l.ParentLocationId <> l.LocationId ) ) -- recursive query
SELECT * FROM location_table
ORDER BY location_level, LocationId;

The initial query supplies the rows to match for the recursive query. If you change the where clause to query for your initial target location you would like to match:

WITH RECURSIVE
  location_table ( LocationId, ParentLocationId, location_level ) AS
( ( SELECT LocationId, ParentLocationId, 0
    FROM Locations AS l
  WHERE LocationId = 1 ) -- initial query

You generate a list of matched locations:

LocationId	ParentLocationId	location_level
1	(NULL)	0
4	1	1
5	4	2
6	4	2
7	5	3

Once you have this list, you can then start matching your inventory values:

WITH RECURSIVE
  location_table ( LocationId, ParentLocationId) AS
( ( SELECT LocationId, ParentLocationId
    FROM Locations AS l
    WHERE LocationId = 1 )
  UNION ALL
  ( SELECT l.LocationId, l.ParentLocationId
    FROM Locations AS l
     JOIN location_table AS lt
       ON l.ParentLocationId = lt.LocationId
      AND l.ParentLocationId <> l.LocationId ) )
SELECT lt.LocationId, i.ItemId, i.qty
FROM location_table lt, Inventory i
WHERE lt.LocationId = i.LocationId;

LocationId	ItemId	qty
1	100	5
4	100	5
6	100	5
5	100	5
7	100	5

You can then sum as appropriate in a stored procedure:

CREATE OR REPLACE PROCEDURE get_item_quantity_by_location( IN @query_location_id INTEGER )
RESULT( location_id INTEGER, qty INTEGER )
BEGIN
    WITH RECURSIVE
      location_table ( LocationId, ParentLocationId ) AS
    ( ( SELECT LocationId, ParentLocationId
        FROM Locations AS l
        WHERE LocationId = @query_location_id )
      UNION ALL
 ( SELECT l.LocationId, l.ParentLocationId
        FROM Locations AS l
             JOIN location_table AS lt
               ON l.ParentLocationId = lt.LocationId
                AND l.ParentLocationId <> l.LocationId ) )
    SELECT @query_location_id, SUM(i.qty)
    FROM location_table lt, Inventory i
    WHERE lt.LocationId = i.LocationId;
END;

and then call the procedure with your location ID of interest:

SELECT * FROM get_item_quantity_by_location(1);

location_id	qty
1	25

SELECT * FROM get_item_quantity_by_location(5);

location_id	qty
5	10

Regards,

Jeff Albion

SAP Active Global Support

[former_member1021736 · Accepted solution]

Thanks Jeff!

Edgard

[JasonHinsperger]

Edgard,

    What you are talking about is a recursive query, which SQL Anywhere supports.  You can read more about it (including an example) here:

http://dcx.sybase.com/index.html#sa160/en/dbusage/recursive-table-expr-sqlug.html*d5e27679

--Jason