Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    48

    Unanswered: Which query is better

    Hi!, i have to run this on multiple table. Here i am writing example of 3 tables. selection criteria for OrdNo can be a range, also OrdNo is unique in TabHead1

    TabHead1
    OrdNo customer
    100 cust1
    101 cust1
    101 cust2

    TabDet1
    OrdNo upc Qty
    100 upc1 100
    100 upc2 110
    101 upc1 200

    TabDet2
    OrdNo upc cart Qty
    100 upc1 cart1 10
    100 upc1 cart2 11
    100 upc1 cart3 15
    100 upc2 cart4 5


    Result expected for Summary version
    OrdNo customer Qty sh_qty
    100 cust1 210 41

    Query1

    Select TabHead1.OrdNo , TabHead1.customer ,
    sum(TabDet1.Qty) Qty, Sum(TabDet2.Qty) sh_qty from TabHead1
    Left join TabDet1 on TabDet1.OrdNo = TabHead1.OrdNo
    Left join TabDet2 on TabDet2.OrdNo = TabDet1.OrdNo and TabDet1.upc = TabDet2.upc group by TabHead1.OrdNo , TabHead1.customer where TabHead1.OrdNo = 100

    Or Query2

    Select TabHead1.OrdNo , TabHead1.customer , sum(TabDet1.Qty) Qty,
    (Select Sum(TabDet2.Qty) sh_qty from TabDet2 where
    TabDet2. OrdNo = TabDet1.OrdNo and TabDet1.upc = TabDet2.upc)
    from from TabHead1
    Left join TabDet1 on TabDet1.OrdNo = TabHead1.OrdNo
    Group by TabHead1.OrdNo , TabHead1.customer where TabHead1.OrdNo = 100

    Or Query3
    Select TabHead1.OrdNo , TabHead1.customer ,
    (select sum(TabDet1.Qty) Qty from TabDet1 Where TabDet1.OrdNo = TabHead1.OrdNo ),
    (Select Sum(TabDet2.Qty) sh_qty from TabDet2 where TabDet2.OrdNo = TabDet1.OrdNo)
    from TabHead1
    Group by TabHead1.OrdNo , TabHead1.customer where TabHead1.OrdNo = 100

    Which query (1,2,3) is better or is there a better way. In detailed version i have to group by OrdNo, customer, upc. Thanks in advance

    Result expected for Detail version
    OrdNo customer upc Qty sh_qty
    100 cust1 upc1 100 36
    100 cust1 upc2 110 5

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    Try to generate the Visual Explain plan for each of these queries and you'll have a fair enough idea on the best query to be used
    IBM Certified Database Associate, DB2 9 for LUW

  3. #3
    Join Date
    Nov 2008
    Posts
    48
    Thank you nick, one more question :-- is there a function to concate a column during grouping e.g
    Mytable
    SNo col1 qty
    A00 A1 10
    A00 A2 20
    A00 A3 30
    A01 A1 5
    A01 A2 1

    Result expected:

    A00 A1,A2,A3 60
    A01 A1,A2,A3 6


    Select SNo, Somefunction(col1), Sum(qty) from Mytable group by SNo

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That's an FAQ. Search for string concatenation. You can use XMLAGG or a recursive query for that.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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