10 Replies Latest reply: Jan 8, 2013 10:23 AM by Solomon Yakobson RSS

    Scalar Subquery to select max

    Nikhil Juneja
      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
          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
            Nikhil Juneja
            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
              Nikhil Juneja
              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
                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
                  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
                    Nikhil Juneja
                    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
                      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
                        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
                          Nikhil Juneja
                          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
                            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.