This discussion is archived
10 Replies Latest reply: Jan 8, 2013 8:23 AM by Solomon Yakobson RSS

Scalar Subquery to select max

NikhilJuneja Newbie
Currently Being Moderated
Hi All,

Oracle Version
11.2.0.1.0


I need to write a query Update a table based on the following criteria:
create table trades (tid number, descp varchar2(20));

create table trade_descp (tid number, aud_desc varchar2(20), c_date date)


insert into trades
values
(1,NULL)

insert into trades
values
(3,NULL)

insert into trades
values
(4,NULL)

insert into trade_descp values
(1,'abdc(M)','1-Jan-2012')

insert into trade_descp values
(1,'abdc(M)','1-Jan-2013')

insert into trade_descp values
(1,'abdc(N)','1-Jan-2014')
The output which i require is to update the trades table with a description having the maximum date from trade_descp table based on some filter for e.g descp like '%M%'.
There is a index on tid column in trade_descp table (having huge records).

Can this be achieved in direct SQL.


Thanks and Regards,
Nik
  • 1. Re: Scalar Subquery to select max
    BluShadow Guru Moderator
    Currently Being Moderated
    What's wrong with just:
    update trades
    set descp = (select to_char(max(c_date),'DD-MON-YYYY') from trade_descp where aud_desc like '%M%')
    ?

    (and by the way, you're insert of dates is trying to insert strings. you should have to_date(..., 'DD-MON-YYYY') in the insert statements.)

    Edited by: BluShadow on 08-Jan-2013 13:48
    converted date to string for the description
  • 2. Re: Scalar Subquery to select max
    NikhilJuneja Newbie
    Currently Being Moderated
    Thanks for your prompt reply,

    it was just a scenario created on my personal laptop so had strings inserted.

    Shouldn't there be a join on tid?

    I need to select values based on tid.


    Thanks,
    Nik


    Also I need to update description based on a case
    e.g.
    set descp =
    case when
    aud_desc like '%M%' then 'MATCHED'
    when
    aud_desc like '%U%' then 'UNMATCHED'
    END

    Edited by: Nikhil Juneja on Jan 8, 2013 5:55 AM
  • 3. Re: Scalar Subquery to select max
    NikhilJuneja Newbie
    Currently Being Moderated
    select * from 
    (
    select b.*, dense_rank() over (order by c_date desc) rn from trades a,
    trade_descp b
    where a.tid = b.tid
    and aud_desc like '%M%' 
    ) where rn = 1
    I need to update trades table with the aud desc which comes from the query above,
    but the issue which is coming is it if I directly try an update is the scope:
    update trades a 
    set descp = 
    (
    select aud_descp from 
    (
    select b.*, dense_rank() over (order by c_date desc) rn from
    trade_descp b
    where b.tid = a.tid
    and aud_desc like '%M%' 
    ) where rn = 1
    )
    it gives ORA-00904 A.TID invalid identifier....
  • 4. Re: Scalar Subquery to select max
    jeneesh Guru
    Currently Being Moderated
    Try MERGE..
    merge into trades a 
    using 
    (
         select tid,aud_descp 
         from 
         (     
           select tid,aud_descp,dense_rank() over (order by c_date desc) rn 
           from trade_descp
           where aud_desc like '%M%' 
         )
         where rn = 1
    ) b
    on (b.tid=a.tid)
    when matched then 
      update set a.descp = b.aud_descp;
    Or
    merge into trades a 
    using 
    (
           select tid,aud_descp,dense_rank() over (order by c_date desc) rn 
           from trade_descp
           where aud_desc like '%M%' 
    ) b
    on (b.tid=a.tid and b.rn=1)
    when matched then 
      update set a.descp = b.aud_descp;
  • 5. Re: Scalar Subquery to select max
    BluShadow Guru Moderator
    Currently Being Moderated
    Nikhil Juneja wrote:
    Thanks for your prompt reply,

    it was just a scenario created on my personal laptop so had strings inserted.

    Shouldn't there be a join on tid?

    I need to select values based on tid.
    That wasn't in your initial requirement. You have to be clear in what you specify.
    Just add it into the subquery as a join condition.
  • 6. Re: Scalar Subquery to select max
    NikhilJuneja Newbie
    Currently Being Moderated
    Thanks for the solution,
    but this is doing a full table scan of the trade_descp table. Can we make us of the index built over TID column?

    I tried the solution on my Test environment and unfortunately the query hangs..

    Thanks,
    Nik
  • 7. Re: Scalar Subquery to select max
    sb92075 Guru
    Currently Being Moderated
    Nikhil Juneja wrote:
    Thanks for the solution,
    but this is doing a full table scan of the trade_descp table. Can we make us of the index built over TID column?

    I tried the solution on my Test environment and unfortunately the query hangs..

    Thanks,
    Nik
    queries do NOT "hang; but they may take a long time to return results

    ALTER SESSION SET SQL_TRACE=TRUE;

    to see where time is actually be spent

    post EXPLAIN PLAN for this new SQL
  • 8. Re: Scalar Subquery to select max
    Solomon Yakobson Guru
    Currently Being Moderated
    Nikhil Juneja wrote:
    but this is doing a full table scan of the trade_descp table. Can we make us of the index built over TID column?
    SQL> merge
      2    into trades a
      3    using (
      4           select  tid,
      5                   max(aud_desc) keep(dense_rank last order by c_date) descp
      6             from  trade_descp
      7             where aud_desc like '%M%'
      8             group by tid
      9          ) b
     10      on (
     11          b.tid = a.tid
     12         )
     13    when matched
     14      then update
     15              set a.descp = b.descp
     16  /
    
    1 row merged.
    
    SQL> select  *
      2    from  trades
      3  /
    
           TID DESCP
    ---------- --------------------
             1 abdc(M)
             3
             4
    
    SQL> explain plan for
      2  merge
      3    into trades a
      4    using (
      5           select  tid,
      6                   max(aud_desc) keep(dense_rank last order by c_date) descp
      7             from  trade_descp
      8             where aud_desc like '%M%'
      9             group by tid
     10          ) b
     11      on (
     12          b.tid = a.tid
     13         )
     14    when matched
     15      then update
     16              set a.descp = b.descp
     17  /
    
    Explained.
    
    SQL> @?\rdbms\admin\utlxpls
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 1775522890
    
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------------
    |   0 | MERGE STATEMENT                 |                  |     2 |    48 |     5  (20)| 00:00:01 |
    |   1 |  MERGE                          | TRADES           |       |       |            |          |
    |   2 |   VIEW                          |                  |       |       |            |          |
    |   3 |    SORT GROUP BY                |                  |     2 |   142 |     5  (20)| 00:00:01 |
    |   4 |     NESTED LOOPS                |                  |       |       |            |          |
    |   5 |      NESTED LOOPS               |                  |     2 |   142 |     4   (0)| 00:00:01 |
    |   6 |       TABLE ACCESS FULL         | TRADES           |     3 |   111 |     3   (0)| 00:00:01 |
    |*  7 |       INDEX RANGE SCAN          | TRADE_DESCP_IDX1 |     3 |       |     0   (0)| 00:00:01 |
    |*  8 |      TABLE ACCESS BY INDEX ROWID| TRADE_DESCP      |     1 |    34 |     1   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       7 - access("TID"="A"."TID")
       8 - filter("AUD_DESC" IS NOT NULL AND "AUD_DESC" LIKE '%M%')
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    25 rows selected.
    
    SQL> 
    SY.
  • 9. Re: Scalar Subquery to select max
    NikhilJuneja Newbie
    Currently Being Moderated
    Thanks Solomon for such a quick solution.

    It updated all records in a matter of few seconds.

    One small query though , you have done a group by based on TID,
    in my original query there is one more column based on which i need to fetch the records, so that also should be selected and added in group by clause?

    Thanks,
    Nik
  • 10. Re: Scalar Subquery to select max
    Solomon Yakobson Guru
    Currently Being Moderated
    Nikhil Juneja wrote:
    in my original query there is one more column based on which i need to fetch the records, so that also should be selected and added in group by clause?
    Hard to tell for sure without knowing detail. If just to fetch the records, it should be added to WHERE clause. If it is used to match records it should be added to GROUP BY and ON clause.

    SY.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points