Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2008
    Posts
    19

    Unanswered: avoid union all in select

    Hi Can anyone help me in rewriting the query,probably a case statement might avoid a table scan twice (AND o.orders_id NOT IN/AND o.orders_id IN ). Not getting the syntax rgt. Thanks in advance.

    SELECT * FROM (SELECT
    o.orders_id order_id, MIN (i.lastcreate) AS create_date,o.timeplaced AS submit_date,o.totalproduct AS amount,
    o.totaladjustment AS adjustment, Sum (i.quantity) AS units,o.status AS status,u.field1 AS ucn,u.field3 AS bcoe,
    'T' parent_teacher_flag

    FROM app2.orders o
    JOIN app2.orderitems i ON o.orders_id = i.orders_id
    JOIN app2.users u ON o.member_id = u.users_id

    WHERE u.registertype = 'R' AND o.status = 'C' AND o.storeent_id = 11151 AND (i.price + i.totaladjustment) > 0

    AND u.field1 NOT IN ('112','114','117', '118','234')

    AND o.orders_id NOT IN (SELECT orders_id FROM app2.orderitems it, app2.xorderitemsav xit WHERE it.orders_id = o.orders_id
    AND it.orderitems_id = xit.orderitems_id AND xit.xorderitemsa_id = 1378)

    GROUP BY o.orders_id,o.timeplaced,o.totalproduct,o.totaladj ustment,o.status,u.field1, u.field3

    UNION ALL

    SELECT o.orders_id order_id, MIN (i.lastcreate) AS create_date,o.timeplaced AS submit_date,o.totalproduct AS amount,
    o.totaladjustment AS adjustment,Sum (i.quantity) AS units, o.status AS status, u.field1 AS ucn, u.field3 AS bcoe, 'P' parent_teacher_flag

    FROM app2.orders o
    JOIN app2.orderitems i ON o.orders_id = i.orders_id
    JOIN app2.users u ON o.member_id = u.users_id

    WHERE u.registertype = 'R' AND o.status IN ('C') AND o.storeent_id = 11151 AND (i.price + i.totaladjustment) > 0

    AND u.field1 NOT IN ('112','114','117', '118','234')
    AND o.orders_id IN (SELECT orders_id FROM app2.orderitems it, app2.xorderitemsav xit
    WHERE it.orders_id = o.orders_id
    AND it.orderitems_id = xit.orderitems_id
    AND xit.xorderitemsa_id = 1378)

    GROUP BY o.orders_id, o.timeplaced,o.totalproduct,o.totaladjustment,o.st atus, u.field1,u.field3 ORDER BY 1, 2) t;

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    looks like on one side of the union you are getting all where the xordeitemsa doesn't = 1378 and on the other side getting all where it does =. and all you are doing is putting a p or a t as the flag. If the above is true, then this should work:

    Code:
    SELECT o.orders_id order_id, MIN (i.lastcreate) AS create_date,o.timeplaced AS submit_date,o.totalproduct AS amount,
    o.totaladjustment AS adjustment,Sum (i.quantity) AS units, o.status AS status, u.field1 AS ucn, u.field3 AS bcoe, 
    --now you determine the flag in a case statement/pretty standard
    case when xit.xorderitemsa_id = 1378 then 'P' else 'T' end as parent_teacher_flag
    --********
    FROM app2.orders o 
    JOIN app2.orderitems i ON o.orders_id = i.orders_id 
    JOIN app2.users u ON o.member_id = u.users_id 
    
    -- no longer a NOT IN/IN condition
    JOIN app2.xorderitemsav xit ON it.orderitems_id = xit.orderitems_id
    --***************
    WHERE u.registertype = 'R' AND o.status IN ('C') AND o.storeent_id = 11151 AND (i.price + i.totaladjustment) > 0 
    
    AND u.field1 NOT IN ('112','114','117', '118','234')
    GROUP BY o.orders_id, o.timeplaced,o.totalproduct,o.totaladjustment,o.st atus, u.field1,u.field3 ORDER BY 1, 2) t;
    Completely untested/just cut and pasted.

    Dave

  3. #3
    Join Date
    Sep 2008
    Posts
    19
    Thanks a lot Dave.

Posting Permissions

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