Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: subquery causing problems

    I have this query which works just fine:
    PHP Code:
    SELECT MAX(mp.lmp_nbr) AS mp_price
           
    mp.mkt_loctn_id
      FROM MARKET_PRICE mp
        WHERE mp
    .mkt_price_dt SYSDATE-1
          GROUP BY mp
    .mkt_loctn_id
    output is this:
    Code:
      MP_PRICE MKT_LOCTN_ID
    ---------- ----------------
         55.66 4000
         50.65 4001
         55.62 4002
         58.08 4003
         57.64 4004
         53.77 4005
         53.73 4006
         56.14 4007
         54.57 4008
    I want to also display the date for those values (date is also in the same table). I thought I could use the below query by it is not working. It keeps giving me an error when I try to match on the group-by function MAX(mp.lmp_nbr).

    PHP Code:
    SELECT MAX(mp.lmp_nbr) AS mp_price
           
    mp.mkt_loctn_id,
           (
    SELECT mkt_price_dt 
                      FROM MARKET_PRICE 
                           WHERE lmp_nbr 
    mp_price 
                              
    AND mkt_loctn_id mp.mkt_loctn_id) AS the_date
    FROM MARKET_PRICE mp
    WHERE mp
    .mkt_price_dt SYSDATE-1
    GROUP BY mp
    .mkt_loctn_id
    - The_Duck
    you can lead someone to something but they will never learn anything ...

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

    Re: subquery causing problems

    Originally posted by The_Duck
    I have this query which works just fine:
    PHP Code:
    SELECT MAX(mp.lmp_nbr) AS mp_price
           
    mp.mkt_loctn_id
      FROM MARKET_PRICE mp
        WHERE mp
    .mkt_price_dt SYSDATE-1
          GROUP BY mp
    .mkt_loctn_id
    output is this:
    Code:
      MP_PRICE MKT_LOCTN_ID
    ---------- ----------------
         55.66 4000
         50.65 4001
         55.62 4002
         58.08 4003
         57.64 4004
         53.77 4005
         53.73 4006
         56.14 4007
         54.57 4008
    I want to also display the date for those values (date is also in the same table). I thought I could use the below query by it is not working. It keeps giving me an error when I try to match on the group-by function MAX(mp.lmp_nbr).

    PHP Code:
    SELECT MAX(mp.lmp_nbr) AS mp_price
           
    mp.mkt_loctn_id,
           (
    SELECT mkt_price_dt 
                      FROM MARKET_PRICE 
                           WHERE lmp_nbr 
    mp_price 
                              
    AND mkt_loctn_id mp.mkt_loctn_id) AS the_date
    FROM MARKET_PRICE mp
    WHERE mp
    .mkt_price_dt SYSDATE-1
    GROUP BY mp
    .mkt_loctn_id
    Do this instead:

    select lmp_nbr, mkt_locn_id, mkt_price_dt
    from market price
    where lmp_nbr, mkt_locn_id in
    (
    SELECT MAX(mp.lmp_nbr) AS mp_price,
    mp.mkt_loctn_id
    FROM MARKET_PRICE mp
    WHERE mp.mkt_price_dt > SYSDATE-1
    GROUP BY mp.mkt_loctn_id;
    );

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    doesn't let me do that.

    PHP Code:
    SELECT lmp_nbr
           
    mkt_loctn_id
           
    mkt_price_dt
    FROM MARKET_PRICE
    WHERE lmp_nbr
    mkt_loctn_id IN
          
    SELECT MAX(mp.lmp_nbr) AS mp_price
                   
    mp.mkt_loctn_id
            FROM MARKET_PRICE mp
            WHERE mp
    .mkt_price_dt SYSDATE-1
            GROUP BY mp
    .mkt_loctn_id); 
    Code:
    WHERE lmp_nbr, mkt_loctn_id IN
                 *
    ERROR at line 5:
    ORA-00920: invalid relational operator
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by The_Duck
    doesn't let me do that.

    PHP Code:
    SELECT lmp_nbr
           
    mkt_loctn_id
           
    mkt_price_dt
    FROM MARKET_PRICE
    WHERE lmp_nbr
    mkt_loctn_id IN
          
    SELECT MAX(mp.lmp_nbr) AS mp_price
                   
    mp.mkt_loctn_id
            FROM MARKET_PRICE mp
            WHERE mp
    .mkt_price_dt SYSDATE-1
            GROUP BY mp
    .mkt_loctn_id); 
    Code:
    WHERE lmp_nbr, mkt_loctn_id IN
                 *
    ERROR at line 5:
    ORA-00920: invalid relational operator
    Sorry, I forgot the parentheses:

    PHP Code:
    SELECT lmp_nbr
           
    mkt_loctn_id
           
    mkt_price_dt
    FROM MARKET_PRICE
    WHERE 
    (lmp_nbrmkt_loctn_idIN
          
    SELECT MAX(mp.lmp_nbr) AS mp_price
                   
    mp.mkt_loctn_id
            FROM MARKET_PRICE mp
            WHERE mp
    .mkt_price_dt SYSDATE-1
            GROUP BY mp
    .mkt_loctn_id); 

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296

    Thumbs up Thanks a lot

    Hell yeah!


    Dood, you rock. Thanks for the help.

    I edited the subquery you threw my way to include my other parameters. I went from 56 seconds to 78 milisecs.
    PHP Code:
    /* LATEST and BEST QUERY */                   
    SELECT lmp_nbr
           
    mkt_loctn_id
           
    MAX(mkt_price_dt)
    FROM MARKET_PRICE
    WHERE 
    (energy_mkt_cdmkt_loctn_idmkt_prcng_type_cdlmp_nbr IN
        
    SELECT 
                   mp
    .energy_mkt_cd,
                   
    mp.mkt_loctn_id,
                   
    mp.mkt_prcng_type_cd,
                   
    MAX(mp.lmp_nbr)
          
    FROM MARKET_PRICE mp
          WHERE  mp
    .energy_mkt_cd 'NEISO' AND
                 
    mp.mkt_prcng_type_cd '5M' AND
                 
    mp.mkt_price_dt 
                 BETWEEN TO_DATE
    ('2003/07/14 04:00:00''YYYY/MM/DD HH24:MI:SS'
                 AND 
    TO_DATE('2003/07/15 04:00:00''YYYY/MM/DD HH24:MI:SS')
          
    GROUP BY   mp.energy_mkt_cd,
                         
    mp.mkt_loctn_id,
                       
    mp.mkt_prcng_type_cd)
    GROUP BY lmp_nbrmkt_loctn_id
    ORDER BY 2

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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