Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: Old (One to Many) vs New (One to Many)

    Here's a fun little problem:

    I have a product configuration system that creates a "narrative" to describe the custom product. This narrative consists of 1 to n lines of details (text fields) generated by the front end - each product could have 1 or more lines of text describing it.

    At the moment, n is 117, and the table holding the data has about 1/2 million records (it has about 47,000 products in it, with the majority of them having less than 20 lines, but a few with as many as 78).

    Here's the fun part:
    A quote get configured by Sales, and when it gets sold, the configuration gets modified by Engineering (most of the time, they are adding additional parameters, sometimes they are fixing mistakes, sometimes they are making mistakes). When Engineering does their part, a second set of details is generated (by the same front end).

    I want to compare the keyed text fields in the Sales version to the keyed text fields in the Engineering version and only show what is different.

    This is really easy for cases where the same detail line exists in both versions, but a lot trickier when the detail line exists in one but not the other.

    I have several ways of accomplishing this, but they are each very poor performers (especially since the target front end is Crystal reports).

    Any ideas?

    Here's a simplified example of what I want to do:
    Code:
    RecID  ProductID   LineNum   DetailNum  Detail
    10001  1050        1         1          Green Apples
    10002  1050        1         2          Flour Pie Crust
    10003  1050        1         3          Lattice Top
    10004  1050        1         4          Foil Baking Pan
    10005  1051        1         1          Red Apples
    10006  1051        1         2          Flour Pie Crust
    10007  1051        1         3          Crumb Top
    10008  1051        1         4          Foil Baking Pan
    
    Result of View:
    Comparing ProductID 1050 (line 1 of the Estimate) to ProductID 1051 (Line 1 of the Sales Order)
    
    LineNum   DetailNum  Detail_1             Detail_2
    1         1          Green Apples         Red Apples
    1         3          Lattice Top          Crumb Top
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    select 
        COALESCE(o.LineNum, n.LineNum) as LineNum,
        COALESCE(o.DetailNum, n.DetailNum) as DetailNum,
        o.Detail as Detail1,
        n.Detail as Detail2
    from MyTable as o
    full join MyTable as n 
        on n.LineNum = o.LineNum and 
           n.DetailNum = o.DetailNum and
           n.RecID > o.RecID
    where o.Detail <> n.Detail
    Hope this helps.

  3. #3
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    elegant.


    .

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Yes - a lot more elegant that what I was doing. However, it doesn't solve the problem: what to do when a line exists in one record set, but not the other.

    I should have presented my example as:
    Code:
    RecID  ProductID   LineNum   DetailNum  Detail
    10001  1050        1         1          Green Apples
    10002  1050        1         2          Flour Pie Crust
    10003  1050        1         3          Lattice Top
    10004  1050        1         4          Foil Baking Pan
    10005  1050        1         5          Bake at 375F
    10006  1051        1         1          Red Apples
    10007  1051        1         2          Flour Pie Crust
    10008  1051        1         4          Foil Baking Pan
    10009  1051        1         5          Bake at 350F
    10010  1051        1         6          Let sit for 10 minutes
    
    Result of View:
    Comparing ProductID 1050 (line 1 of the Estimate) to ProductID 1051 (Line 1 of the Sales Order)
    
    LineNum   DetailNum  Detail_1             Detail_2
    1         1          Green Apples         Red Apples
    1         3          Lattice Top           
    1         5          Bake at 375F         Bake at 350F
    1         6                               Let sit for 10 minutes
    Notice that Line 3 is missing from Detail_2 and Line 6 is missing from Detail_1.

    I did find a fairly efficient way to do it:
    I used 3 views. One to find all the lines in the Estimate and not the Sales Order, a second to find all the lines in the Sales Order and not the Estimate and the third compare the data on lines that exist in both. Then, a quick union to put the 3 together. I suppose I could actually nest the 3 views into one union, but is that any more efficient from a processing standpoint?
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  5. #5
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Wouldn't a full-join include those, from both sides, regardless of match?

    Also; why use Views rather than Sub-Queries?

  6. #6
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    with 
        CTE_O as
        (
            select LineNum, DetailNum, Detail 
            from MyTable 
            where ProductID = 1050
        ),
        
        CTE_N as
        (
            select LineNum, DetailNum, Detail 
            from MyTable 
            where ProductID = 1051
        )
    
    select 
        COALESCE(o.LineNum, n.LineNum) as LineNum,
        COALESCE(o.DetailNum, n.DetailNum) as DetailNum,
        o.Detail as Detail1,
        n.Detail as Detail2
    from CTE_O as o
    full join CTE_N as n 
        on n.LineNum = o.LineNum and 
           n.DetailNum = o.DetailNum 
    where o.Detail <> n.Detail or 
          o.Detail is null or 
          n.Detail is null
    order by LineNum, DetailNum
    or

    Code:
    with CTE as
    (
        select distinct LineNum, DetailNum 
        from MyTable
        where ProductID between 1050 and 1051
    )
    
    select 
        c.LineNum,
        c.DetailNum,
        o.Detail as Detail1,
        n.Detail as Detail2
    from CTE as c
    left join MyTable as o 
        on o.ProductID = 1050 and 
           o.LineNum = c.LineNum and 
           o.DetailNum = c.DetailNum
    left join MyTable as n 
        on n.ProductID = 1051 and 
           n.LineNum = c.LineNum and 
           n.DetailNum = c.DetailNum
    where o.Detail <> n.Detail or 
          o.Detail is null or 
          n.Detail is null
    Hope this helps.

Posting Permissions

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