Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: better performace with query

    its k i got it
    Last edited by STUCK1234; 03-15-07 at 08:47.

  2. #2
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    24
    Remove RTrim because sql server does it automatically and you should repleace NOT IN with NOT EXISTS and also replace the subquery to corelated query.

    These changes will enhance query performace.

    Riaz

    Quote Originally Posted by STUCK1234
    I am comparing 2 tables and trying to retrieve data on any amendments made to the qty in one table and not in the other:

    SELECT TRANSACTIONS.order_no, product, whse, unit, Sum(qty)
    from TRANSACTIONS
    INNER JOIN ORDERS on
    ORDERS.order_no = TRANSACTIONS.order_no
    where status = '6'
    group by product, whse, TRANSACTIONS.order_no, unit,date_required
    having
    RTrim(TRANSACTIONS.order_no)+RTrim(product)+RTrim( whse)+RTrim(unit)+CAST(Sum(qty)AS varchar) not in
    (select RTrim(d.order_no)+RTrim(product)+RTrim(d.warehouse )+RTrim(dbo.toProperCase(unit_of_sale))+CAST(Sum(a llocated_qty) AS varchar)
    from det d
    INNER JOIN head h on
    h.order_no = d.order_no
    where h.status = '6'
    group by product, d.warehouse, d.order_no, d.unit_of_sale)

    this SEEMS 2 wrk, but is there a quicker and better way of doin the above query?

  3. #3
    Join Date
    Feb 2005
    Posts
    76
    thanks I didnt they made a difference!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Please do not edit out your original posts!
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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