Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Location
    Gandhinagar India
    Posts
    22

    Unanswered: Convert Oracle Query in MySql

    desc player_portfolio

    +-----------------+--------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-----------------+--------------+------+-----+---------+-------+
    | player_ID | mediumint(6) | | PRI | 0 | |
    | p_date | mediumint(1) | | PRI | 0 | |
    | script_ID | mediumint(3) | | PRI | 0 | |
    | amount_invested | double(7,2) | YES | | NULL | |
    +-----------------+--------------+------+-----+---------+-------+

    desc script_price;
    +------------+--------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +------------+--------------+------+-----+---------+-------+
    | script_ID | mediumint(3) | | PRI | 0 | |
    | s_date | mediumint(1) | | PRI | 0 | |
    | open_price | double(6,2) | YES | | NULL | |
    | last_price | double(6,2) | YES | | NULL | |
    +------------+--------------+------+-----+---------+-------+


    desc player_info;
    +------------------+--------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +------------------+--------------+------+-----+---------+-------+
    | player_ID | mediumint(6) | | PRI | 0 | |
    | player_email | varchar(40) | YES | | NULL | |
    | player_password | varchar(6) | YES | | NULL | |
    | player_institute | varchar(65) | YES | | NULL | |
    +------------------+--------------+------+-----+---------+-------+

    HOW DO I RUN THIS QUERY IN mysql.... IT IS IN ORACLE.....

    select * from (select player_email, player_institute, ULP from (select PID UPID, sum(LP) ULP from (select player_portfolio.player_ID PID,(floor(player_portfolio.amount_invested/script_price.open_price))*(script_price.last_price - script_price.open_price) LP from player_portfolio, script_price where player_portfolio.script_ID = script_price.script_ID and player_portfolio.p_date = script_price.s_date) group by PID),player_info where player_ID=UPID order by ULP) where rownum < 26;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    like this:
    Code:
    select player_email
         , player_institute
         , ULP 
      from (
           select PID UPID
                , sum(LP) ULP 
             from (
                  select player_portfolio.player_ID PID
                       , (floor(player_portfolio.amount_invested
                               /script_price.open_price))
                        *(script_price.last_price 
                         -script_price.open_price) LP 
                    from player_portfolio
                       , script_price 
                   where player_portfolio.script_ID 
                       = script_price.script_ID 
                     and player_portfolio.p_date 
                       = script_price.s_date
                  ) 
           group 
               by PID
           )
         , player_info 
     where player_ID
         = UPID 
    order 
         by ULP
    limit 25
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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