Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2013
    Posts
    4

    Unanswered: Selecting and replacing query

    Hi there, thought i'd start of with introducing myself.. I am a risk analyst for a bank, occiationally I work with software delevopment for our bank, from sweden and pleased to be here! Hopefully i'll be able to help you out sometimes in return

    Okay here goes...

    I work at a bank and currently we are having some trouble with a few values.. So what I need to do is replace this with some other values from the same table.

    What I have:

    I have 3 colums. ex:

    Maturity------Moneyness---------Value
    1d...................1.0.......................2
    2d...................1.0.......................2.1
    1d....................2.0......................14
    2d....................2.0.......................16

    What I want to do is make the query replace the values of moneyness 2.0 to the same values as the moneyness 1.0 and selecting them while still keeping rest of the values..

    so I want it to look like this:

    Maturity------Moneyness---------Value
    1d...................1.0.......................2
    2d...................1.0.......................2.1
    1d....................2.0......................2 (used to be 14)
    2d....................2.0.......................2. 1 (used to be 16)

    Right now it looks like this cause im inserting into a python script, but its the same code tho for selecting.

    Code:
     sql_q = "SELECT maturity,moneyness,value "
        sql_q+= "FROM swapOptionVolatilities "
        sql_q+= "WHERE date = '%s' " % uploadDate.string('%Y-%m-%d')
        sql_q+= " AND Currency = '%s' " % Currency

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Completely untested air-code (so be careful!)
    Code:
    UPDATE x2
    SET    value = x1.value
    FROM   your_table As x1
     INNER
      JOIN your_table As x2
        ON x2.maturiy = x.maturity
       AND x2.moneyness = '2.0'
       AND x1.moneyness = '1.0'
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2013
    Posts
    4
    Quote Originally Posted by gvee View Post
    Completely untested air-code (so be careful!)
    Code:
    UPDATE x2
    SET    value = x1.value
    FROM   your_table As x1
     INNER
      JOIN your_table As x2
        ON x2.maturiy = x.maturity
       AND x2.moneyness = '2.0'
       AND x1.moneyness = '1.0'
    Thank you I had something like this is mind aswell after googling my ass off
    What I think might work that is similiar to your query is this..

    Code:
    select q1.*, q2.moneyness, q2.value
    
    from dbo.swapOptionVolatilities q1, dbo.swapOptionVolatilities q2
    
    where q1.date = q2.date
    
               and q1.expiry = q2.expiry
    
               and q1.maturity = q2.maturity
    
               and q1.moneyness = 1.0
    
               and q2.moneyness = 2.0

  4. #4
    Join Date
    Sep 2013
    Posts
    4
    Quote Originally Posted by gvee View Post
    Completely untested air-code (so be careful!)
    Code:
    UPDATE x2
    SET    value = x1.value
    FROM   your_table As x1
     INNER
      JOIN your_table As x2
        ON x2.maturiy = x.maturity
       AND x2.moneyness = '2.0'
       AND x1.moneyness = '1.0'
    btw, about your query.. Why can't I just select x2?
    Many thanks tho

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sorry, I mis-interpreted your question and thought you were looking to update the values, not just select them!
    George
    Home | Blog

  6. #6
    Join Date
    Sep 2013
    Posts
    4
    Quote Originally Posted by gvee View Post
    Sorry, I mis-interpreted your question and thought you were looking to update the values, not just select them!
    I am selecting them for a python program.. So that's why, else I could just update the database..

    But when I run this select, it changes the value but it changes the moneyness aswell to 1.0 :/

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The SELECT statement you provided will return two columns with the name "moneyness":
    Code:
    select q1.*, q2.moneyness, q2.value
    The first part highlighted in red above will return all columns from q1, which includes a moneyness column.

    Check if you have 2 different values in these columns.

    Consider changing your query and using aliases:
    Code:
    SELECT ...
         , q1.moneyness As q1_moneyness
         , q2.moneyness As q2_moneyness
         , q2.value As q2_value
           ...
    George
    Home | Blog

Posting Permissions

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