May 27, 2008 at 5:38 am
hi!
I have a sql-query I need some help with.
I'm building an application (search form) that is going to be used to find articles (at top level) composed of other articles specified. I have attached an url to .a pdf http://www.stefanj.se/articletreenew.pdf illustrating an article tree.
In the database there is one table, 'TN_ARTICLES', which holds all articles in the system. The OBJECT_ID field in this table gives all articles unique ids.
In the database there also is one table, 'TDM_LINKS_00676'. In this table one can find the fields OBJECT_ID, CLASS_ID, OBJECT_ID1, CLASS_ID1, OBJECT_ID2, CLASS_ID2 and more. OBJECT_ID just gives an item in this table an id. OBJECT_ID1 is an articles id, and also OBJECT_ID2 is an articles id.
The article tree (BOM) is created by object_id1 describing a parentnode, and object_id2 a child node to this parentnode. The database structure can't be altered.
In the examples below I have chosen to check for two articles, but it shall also be possible to select an arbritary number of articles.
Example 1:
What articles are composed of article 1180 SV and article MB0040?
Argument: ObjectId 2, ObjectId 4
Result: ObjectId 1
Example 2:
What articles are composed of article FE0067 and AA2250?
Argument: ObjectId 8, ObjectId 11
Result: null, no articles
Example 3:
What articles are composed of article AA2250 and AA2251?
Argument: ObjectId 11, ObjectId 12
Result: ObjectId 10
Example 4:
What articles are composed of article FE0067 and 1180?
Argument: ObjectId 8, ObjectId 2
Result: ObjectId 9, ObjectId 1
Any good ideas?
May 29, 2008 at 12:31 pm
I’m going to clarify your terminology a bit to make it easier to understand.
Lets call ObjectId1 as the Parent and ObjectId2 as the Child and your Top Level Article as the Product.
What you will need to do is create a recursive program where every returned Parent becomes the Child argument for the next process until the return is NULL in which case you have reached the Product.
Using your Example one.
Child is ‘MB0040’ and it returns ‘DE0258’. Now ‘DE0258’ becomes the Child and it returns ‘MT53H7A’. Now in turn, ‘MT53H7A’ becomes the Child it it will return NULL which is your Product.
HOWEVER! Think of a big Oak tree where the trunk is your initial Child argument and the tree’s leaves are the Products. You will have to go through each and every branch, sub branch, twig etc to get to each and every leaf. That takes a huge amount of processing time!
To get around this, what I’ve done is to break out each and every relationship into another table and then use it for querying. This way you can do the big recursive portion of the process, say at night, and then have your user’s query this table (If you are comfortable with using a static table where latest data is not critical).
TBL_RelationshipAll would look something like this.
BaseItemChildParent
MB0040MB0040DE0258
MB0040DE0258MT53H7A
MB0040MT53H7ANULL
You can now create a simple Query:
SELECT Child
FROM TBL_RelationshipAll
WHERE BaseItem = @Argument AND Parent IS NULL
To create the table with it’s data:
ALTER PROCEDURE [dbo].[PROC_BUILD_GLOBAL_WU]
AS
BEGIN
DECLARE @intRecordCount BIGINT
DECLARE @intLevel INT
SET @intRecordCount = 1 --Allows WHERE to conduct first loop
SET @intLevel = 2 --There has to be at least 2 levels
@intRecordCount
-- Create new table...
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBL_PARENT_CHILD]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[TBL_PARENT_CHILD]
END
CREATE TABLE [WHERE_USED].[dbo].[TBL_PARENT_CHILD]
(BOMMST_ID nvarchar(30) NULL,
ParentID nvarchar(5) NULL,
ParentItemNbr nvarchar(30) NULL,
ChildID nvarchar(5) NOT NULL,
ChildItemNbr nvarchar(30) NOT NULL,
BaseItemID nvarchar(5) NOT NULL,
BaseItemNbr nvarchar(30) NOT NULL,
LevelFromBase int NOT NULL)
CREATE INDEX INDX_WU_BASEITEM
ON TBL_PARENT_CHILD (BaseItemID)
-- ...and add data from BOMMSTR_NF_STATIC to create level 1 ParentChild group
INSERT[dbo].[TBL_PARENT_CHILD]
SELECT DISTINCT BOMMSTR.BOMMST_ID, BOMMSTR.CPN AS ParentID, BOMMSTR.ITEM_NBR AS ParentItemNbr, BOMMSTR.CP_CPN AS ChildID,
BOMMSTR.CP_ITEM_NBR AS ChildItemNbr, BOMMSTR.CP_CPN AS BaseItemID, BOMMSTR.CP_ITEM_NBR AS BaseItemNbr,
1 AS LevelFromBase
FROM[BOMMSTR_NF_STATIC] AS BOMMSTR
--Create level two ParentChild group - Use all Parents from TBL_PARENT_CHILD but only those that match from BOMMSTR
INSERT TBL_PARENT_CHILD
(BOMMST_ID, ParentID, ParentItemNbr, ChildID, ChildItemNbr, BaseItemID, BaseItemNbr, LevelFromBase)
SELECT DISTINCT BOMMSTR_NF_STATIC_1.BOMMST_ID AS BOMMSTR_ID, BOMMSTR_NF_STATIC_1.CPN AS ParentID,
BOMMSTR_NF_STATIC_1.ITEM_NBR AS ParentItemNbr, TBL_PARENT_CHILD_1.ParentID AS ChildID,
TBL_PARENT_CHILD_1.ParentItemNbr AS ChildItemNbr, TBL_PARENT_CHILD_1.BaseItemID, TBL_PARENT_CHILD_1.BaseItemNbr,
2 AS LevelFromBase
FROM TBL_PARENT_CHILD AS TBL_PARENT_CHILD_1 LEFT OUTER JOIN
dbo.BOMMSTR_NF_STATIC AS BOMMSTR_NF_STATIC_1 ON
TBL_PARENT_CHILD_1.ParentID = BOMMSTR_NF_STATIC_1.CP_CPN
--Create subsequent Parent/Child groups until there are no more parents that are children - Parents from TBL_PARENT_CHILDmatch BOMMSTR 1 to 1
WHILE @intRecordCount > 0
BEGIN
INSERT TBL_PARENT_CHILD
(BOMMST_ID, ParentID, ParentItemNbr, ChildID, ChildItemNbr, BaseItemID, BaseItemNbr, LevelFromBase)
SELECT DISTINCT BOMMSTR_NF_STATIC_1.BOMMST_ID AS BOMMSTR_ID, BOMMSTR_NF_STATIC_1.CPN AS ParentID,
BOMMSTR_NF_STATIC_1.ITEM_NBR AS ParentItemNbr, TBL_PARENT_CHILD_1.ParentID AS ChildID,
TBL_PARENT_CHILD_1.ParentItemNbr AS ChildItemNbr, TBL_PARENT_CHILD_1.BaseItemID, TBL_PARENT_CHILD_1.BaseItemNbr,
(@intLevel + 1) AS LevelFromBase
FROM TBL_PARENT_CHILD AS TBL_PARENT_CHILD_1 INNER JOIN
[XBC-dbo.BOMMSTR_NF_STATIC AS BOMMSTR_NF_STATIC_1 ON
TBL_PARENT_CHILD_1.ParentID = BOMMSTR_NF_STATIC_1.CP_CPN
WHERE (TBL_PARENT_CHILD_1.LevelFromBase = @intLevel)
--Set level to next level
SET @intLevel = @intLevel + 1
--Set Record count to next level of record count
SET @intRecordCount = (SELECT COUNT(*)
FROM TBL_PARENT_CHILD ASTBL_PARENT_CHILD_1 INNER JOIN [XBC-dbo.BOMMSTR_NF_STATIC
AS BOMMSTR_NF_STATIC_1 ON TBL_PARENT_CHILD_1.ParentID = BOMMSTR_NF_STATIC_1.CP_CPN
WHERE TBL_PARENT_CHILD_1.LevelFromBase = @intLevel)
--Prints progress of loop
EXECUTE PROC_PRINT_PROGRESS
@intLevel, @intRecordCount
END
Good luck!
Carlo
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply