Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2008
    Posts
    21

    Unanswered: Using result of SQL statement

    Hi,

    What I would like to do is run a select statement, and then have the result of the select be used/run thru the same select again untill it hits a certain where clause.

    This is for a where used search in our BOM (Bill of Material) table.

    So for example

    BOM 1

    level 1 Item# 123 type Finished Good
    level 2 Item# 45 type subassembly
    level 3 Item# 56 type purchased item


    BOM 2

    level 1 Item# 678 type Finished Good
    level 2 Item# 78 type subassembly
    level 3 Item#56 type purchased item


    So my input into the select statement would be Item# 56, it will then first find Item# 45 and Item# 78, but those I am not interested in. I want the select to then take Item#45 and Item#78 and run it thru the same select so that it will find Item#123 and Item#678.

    The select will know to stop there since these are flagged as "Finished Good" in our system, so they will not be in any other BOM. This is what I would imagine being specified in the "where clause".

    I can write the first part, where the select will find Item #45 and Item#78, but don't know how to get past that point.

    Any help or suggestions are greatly appreciated!!

    Thanks,
    Michiel

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    is it the Levels that determine the "order" the records go in?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2008
    Posts
    21

    Order

    Hi,

    Thanks for your reply. The order of the records does not really matter to me. As long as it comes back with the result of item#123 and item# 678.

    Since the BOM table is a flat table, so once it finds the 2 subassemblies in the first run, it needs to take those values and rerun the same select again with the result of the first run. Untill it hits items that are flagged as "Finished Good" which I can put that in a where clause.

    Regards,
    Michiel

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are trying to traverse a hierarchy, which is a pretty common requirement in SQL databases.

    With SQL Server 2000, you can use this technique:
    http://sqlblindman.googlepages.com/r...ngchildrecords

    SQL Server 2005 provides a more efficient method using CTEs (Common Table Expressions).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What platform are you on?

    And please say it's at least SQL Server
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2008
    Posts
    21
    Hi,

    This is indeed for SQL server 2005.

    Thanks.
    Michiel

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Then read up on CTEs in books online, and pay particular attention to the examples regarding hierarchies and parent/child relationships.

    Take a stab at writing the SQL, and when it doesn't work or you get confused (this WILL happen), post the DDL for the table along with the code you tried and we can help you along the way.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jan 2008
    Posts
    21

    reverse

    Hi,

    Thanks for your replies, I have been searching the internet for examples of the traverse hierarchies, and most of them that I find are from the top down, I am trying to basically do a reverse, where you start at the bottom and move up.

    So should I put a "-1" instead of a "+1" as the HierarchyLevel?

    The table I have is very straightforward, only list ParentItem and Component. So the top item will never be listed in the column of Component, only in the ParentItem.

    So my input would be a certain component, and then the CTE needs to run thru the table untill it finds a ParentItem that does not exist in the Component column.

    Thanks,
    Michiel

  9. #9
    Join Date
    Jan 2008
    Posts
    21

    Table structure

    So based of my example in my initial post, the data in the table looks like this

    PARENTITEM/ COMPONENT
    123/ 45
    45/ 56
    678/ 78
    78/ 56


    So as you can see, PARENTITEM 123 and 678 do not exist in the COMPONENT column.

    So my input/whereclause would be COMPONENT 56, and then for my result I would like to get PARENTITEM 123 and 678. I am not interested in 45 and 78.

    Thanks,
    Michiel
    Last edited by mvanmeurs; 04-22-08 at 16:15.

  10. #10
    Join Date
    Jan 2008
    Posts
    21
    Sorry about the spacing in the example above, it does not seem to take my spacing.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by mvanmeurs
    So should I put a "-1" instead of a "+1" as the HierarchyLevel?
    This is just a label and has nothing to do with the direction of the scan. To do a bottom-up scan instead of a top-down scan you would reverse the tables instances and joins.

    Quote Originally Posted by mvanmeurs
    The table I have is very straightforward, only list ParentItem and Component. So the top item will never be listed in the column of Component, only in the ParentItem.
    This is a problem. If the top level is a component, then it should have its own record where it is listed as a component, with no parent. How can it "run thru the table untill it finds a ParentItem that does not exist in the Component column" if the ParentItem itself does not actually exist?

    Fix this data structure, and then your problem becomes "find the top-level component for any given component", or until it finds components that meet the "Finished Good" requirement you specified earlier.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Aug 2007
    Posts
    17
    I get your idea finally.you want to get the root nodes of a net,right?

    a suggestion:

    table:
    test(PARENTITEM,COMPONENT)
    PARENTITEM/ COMPONENT
    123/ 45
    45/ 56
    678/ 78
    78/ 56


    create proc pr_get_root_node(@COMPONENT int)
    as

    declare @table table(node int,parent_node int)

    insert into @table(node, parent_node)
    select COMPONENT ,PARENTITEM
    from test
    where COMPONENT = @COMPONENT

    while(1=1)
    begin
    insert into @table(node, parent_node)
    select COMPONENT ,PARENTITEM
    from test
    where COMPONENT in (select parent_node from @table)
    and COMPONENT not in (select node from @table)

    if (@@rowcount = 0)
    break
    end

    select parent_node
    from @table
    where parent_node not in (select node from @table)
    go


    exec pr_get_root_node 56
    ----------------
    123
    678

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ....which is the method I already posted in my link. But if he is using SQL Server 2005 then he should use Common Table Expressions rather than this looping algorithm.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jan 2008
    Posts
    21
    Thank you so much for your help. The above worked perfect!! Thanks again!

  15. #15
    Join Date
    Aug 2007
    Posts
    17
    It seems that most of you guys is using SQL SERVER 2005 ,right?
    but not in my company.

Posting Permissions

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