Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2008
    Posts
    52

    Unanswered: Compute parent status from child statuses?

    Take the example of a master-detail/parent-child set of tables, say, for example, an order table and order details. The order details table has bit field called "IsShipped". I want the overall order status to be shipped=true when all of the order details have their IsShipped column true. Let's say this is in a stored procedure that returns a result set of orders and whether or not they are completely shipped. How could I achieve this?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    there are plenty of methods.

    but first can the details ship separately? if not, remove the status from details.

    but if so...

    1) the stored procedure that updates the order detail status can poll the other details and update the parent accordingly.

    or

    2) you can do the same thing in a trigger on the order detail table.
    “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
    Mar 2008
    Posts
    52
    Quote Originally Posted by Thrasymachus
    but first can the details ship separately?
    yes.

    Quote Originally Posted by Thrasymachus
    1) the stored procedure that updates the order detail status can poll the other details and update the parent accordingly.
    This is the part I'm trying to understand. What do you need to do to poll the other details?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    one of these should do.
    “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.

  5. #5
    Join Date
    Mar 2008
    Posts
    52
    I agree that is the way to do it. But what would the select statement look like? I'm drawing a blank. It seems I need to compare the count of all orderdetail records to the count of all orderdetail records where the IsShipped column is true. If they're equal then the parent is completely shipped. Does that sound right?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT Count(*) AS all_lines
    ,  Sum(CASE WHEN 1 = isShipped THEN 1 END) AS shipped_lines
       FROM invoice_detail
       WHERE  12345 = invoiceId
    -PatP

  7. #7
    Join Date
    Mar 2008
    Posts
    52
    Thanks that helps. Two things:

    1. Would you recommend storing the result of this computation in a column in the parent table and updating that column whenever updating the shipping status of an order detail? Or would you compute this value on the fly when requesting order records?

    2. I noticed you write "WHERE 12345 = invoiceId", where you put the number on the opposite side of the expression from what I am used to. What is the motivation for that?

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This will lead to the entire order to be marked as shipped, if at least 1 detail item is shipped. To ensure that only completly shipped detail set results in the master record to be reported as shipped, you need to change it to:

    select im.*, IsShipped = case when exists (select * from invoice_detail i_d where i_d.invoiceId = 123456 and im.invoiceId = i_d.invoiceId and i_d.isshipped = 0) then 0 else 1 end from invoice_master im

    If an update of a parent is needed, then it's even simpler. Inside the procedure, have a conditional logic. Better will be to have a separate procedure that just updates the master record's status based on passed invoice number, but the concept is the same:

    if not exists (select * from invoice_detail where invoiceId = @invoiceId and IsShipped = 0)
    exec dbo.sp__updateInvoiceStatus @invoiceId

    Inside dbo.sp__updateInvoiceStatus, you will have:

    update invoice_master set IsShipped = 1 where invoiceId = @invoiceId
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Mar 2008
    Posts
    52
    Thanks I like that combination of using exists and case.

    So do you think it's better to store the computed value in the master table or to calculate it every time you request the master records?

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I prefer to calculate the value only when needed in OLTP situations (such as order entry), and to calculate the value when creating the row in OLAP situations (such as data warehousing or reporting servers). Different uses require different approaches.

    I put the constant on the left side of the equal sign for historical reasons... Many languages use the equal sign for both comparison and assignment. Only one language that I know of allowed values to be assigned to constants, and that was an abomination, so that any case where the code "went south" and interpreted my comparison for an assignment should cause a syntax error instead of a hard to track bug. Old habits die hard!

    -PatP

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by tmpuzer
    Thanks I like that combination of using exists and case.

    So do you think it's better to store the computed value in the master table or to calculate it every time you request the master records?
    If you choose the latter, you'll be forced to hit detail records every time you need to know what orders are shipped and what aren't. If you make it a rule to review the value on master every time you update detail, - then you'll be doing just that. So it depends on whether read activity against detail table hinders its availability for creating new order details and updating existing ones, or not.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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