Results 1 to 5 of 5

Thread: ansi SQL help

  1. #1
    Join Date
    Sep 2003
    Posts
    21

    Unanswered: ansi SQL help

    Hello,

    I have 3 tables.

    orders
    -----
    orderID PK
    symbol
    price
    accountNumber
    matchStatus
    orderDate
    volume
    side

    executions
    -----
    executionID
    symbol
    price
    mcName
    orderDate
    orderTime
    volume
    side

    accounts
    ------------
    accountID
    accountNumber
    mcName


    What I have so far.

    select o.orderid, abs(o.volume), sum(e.volume)
    from orders o, executions e, accounts a
    where a.accountNumber = o.accountNumber and e.mcName = a.mcName
    and o.matchStatus IS NULL and e.orderID IS NULL
    and left(e.side, 1) = left(o.side, 1)
    and o.orderDate = e.executionDate
    and o.symbol = e.symbol
    group by o.orderid, e.volume, o.volume
    having sum(e.volume) = abs(o.volume)

    ************************************************** 8
    I put this in the select statement as a debug statementm abs
    (o.volume), sum(e.volume).

    What I need to do is this.

    I need to get the orderID from the orders table for records where all
    the where statement clauses match up and the abs value of o.volume
    equals the sum of v.volume for all records that match the the where
    clause. It will always be one record in the orders table and will be 1 or
    more records in the execution table that will match up.

    How do I do this?

    Right now I get 0 records because the group by isn't working to well.

    here is a sample return set if I don't have the HAVING statement.

    ORDERID ABS SUM
    2219 434 271
    2219 434 500
    4125 2000 400
    4125 2000 700
    4125 2000 900
    4129 2000 300
    4129 2000 400

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: ansi SQL help

    Take e.volume out of the GROUP BY clause and you should get:

    select o.orderid, abs(o.volume), sum(e.volume)
    from orders o, executions e, accounts a
    where a.accountNumber = o.accountNumber and e.mcName = a.mcName
    and o.matchStatus IS NULL and e.orderID IS NULL
    and left(e.side, 1) = left(o.side, 1)
    and o.orderDate = e.executionDate
    and o.symbol = e.symbol
    group by o.orderid, abs(o.volume);

    ORDERID ABS SUM
    2219 434 771
    4125 2000 2700

    Then put your HAVING back in.

  3. #3
    Join Date
    Sep 2003
    Posts
    21

    Re: ansi SQL help

    Thanks worked like a charm.
    Do you mind if I ask you another question?

    How about if I want to to do an update statement on the returned recordset.

    Something like
    Code:
    update executions 
    set orderID = 
    (
    select o.orderid
    from orders o, executions e, accounts a 
    where a.accountNumber = o.accountNumber and e.mcName = a.mcName
    	and o.matchStatus IS NULL and e.orderID IS NULL 
    	and left(e.side, 1) = left(o.side, 1) 
    	and o.orderDate = e.executionDate
    	and o.symbol = e.symbol
    group by o.orderid, abs(o.volume)
    having sum(e.volume) = abs(o.volume)
    )
    WHERE orderID IS NULL
    Unfortunately my subselect returns multiple records.
    What is the best way around this?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: ansi SQL help

    I don't really understand what you want to do.

  5. #5
    Join Date
    Sep 2003
    Posts
    21
    I want to update executions.orderid to orders.orderid where the conditions are met.

    I found my old code and it went like this.

    UPDATE executions
    SET orderID =
    (
    SELECT O.orderID
    FROM orders AS O JOIN accounts AS A ON O.accountnumber = A.accountnumber
    WHERE O.symbol = executions.symbol AND A.mcname = executions.mcname
    AND LEFT(o.side, 1) = LEFT(executions.side, 1) AND O.orderDate = executions.executionDate
    AND O.orderID in
    (
    select o2.orderID
    from orders o2, executions ee2, accounts aa
    where LEFT(o2.side, 1) = LEFT(ee2.side, 1) and o2.symbol = ee2.symbol and
    aa.MCName = ee2.MCName AND aa.accountNumber = o2.accountNumber AND o2.orderDate = ee2.executionDate
    AND ee2.orderID IS NULL and o2.matchStatus IS NULL
    group by o2.orderID, o2.volume
    HAVING SUM(ee2.volume) = ABS(o2.volume)
    )
    )
    WHERE orderID IS NULL

    update orders
    set matchStatus = 1
    where orders.matchStatus IS NULL AND orders.orderID IN
    (
    select executions.orderID
    from executions
    where executions.orderID IS NOT NULL
    )

Posting Permissions

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