Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Strange behavior: Update with select

Strange behavior: Update with select

From: <Prasada.Gunda1_at_hartfordlife.com>
Date: Fri, 27 Jul 2001 12:24:17 -0700
Message-ID: <F001.00357B4E.20010727113055@fatcity.com> 







Strange behavior : Update with select stt.



select stt in update 1 is NOT working correct and select stt in update 2 is
working correct


though the select statements are logically same.



Explanation:




select stt in update 1 doesn't return any data even though it suppose to
return one record.


To confirm that the select stt returns one record, I prepared select stt by
joining the


table(claim) that is updated with the tables used in select stt(pls see
select 1).


It is returning one record.



Then, I modified the select stt (pls see update 2) in update stt and it is
working fine.



The difference between these two select statements is,
In update 1 select stt, table r is joining with p and r joining with p615.
In update 2 select stt, table r is joing with p and p is joining with p615.



The way the data is:


join with r and p yields 1 record and join with r and p615 yields no
record.


explain plan on update 1: sort join and then merge join cartesian.
explain plan on update 2: nested loops outer join.
explain plan on select 1: nested loops outer join.





I belive, update 1 is not working correct because the second join (r and
p615) resulting no data hence the merge join cartesian resulting with no
data.



Even though optimizer generates different plans for update 1 and 2, final
result should be the same.



FYI, In the select statement, same table is being used with different
aliases (p and p615).



Your thoughts are appreciated. It is very interesting, at least for me.



Thanks


prasad



Update 1:





UPDATE claim r


   SET disability_definition  =

       (SELECT p.option_description || ' ' ||  RTRIM(p.description)
              || DECODE(p.duration_code, 'Y',' YEAR(S)',


                                         'M',' MONTH(S)',

                                         'D',' DAY(S)',

                                         'C',' CAL.YEAR',

                                         'L',' LIFE TIME',

                                         'A',' YEARS OF AGE',

                                         'W',' WEEK(S)',NULL)

              || ' ' || p615.option_description
              || ' ' || RTRIM(p615.description)
              || DECODE(p615.duration_code, 'Y',' YEAR(S)',


                                         'M',' MONTH(S)',

                                         'D',' DAY(S)',

                                         'C',' CAL.YEAR',

                                         'L',' LIFE TIME',

                                         'A',' YEARS OF AGE',

                                         'W',' WEEK(S)',NULL)

          FROM coverage_provision p, coverage_provision p615
         WHERE p.provision_id  = '614'
           AND r.case_id = p.case_id
           AND r.coverage_category_code = p.coverage_category_code
           AND r.coverage_type_code = p.coverage_type_code
           AND r.coverage_plan_number = p.coverage_plan_number
           AND r.class_code = p.class_code
           AND r.disability_date >= p.eff_date
           AND r.disability_date < NVL(p.term_date, r.disability_date+1)
           AND r.case_id = p615.case_id(+)
           AND r.coverage_category_code = p615.coverage_category_code(+)
           AND r.coverage_type_code = p615.coverage_type_code(+)
           AND r.coverage_plan_number = p615.coverage_plan_number(+)
           AND r.class_code = p615.class_code(+)
           AND r.disability_date >= p615.eff_date(+)
           AND r.disability_date < NVL(p615.term_date(+),
r.disability_date+1)
           AND p615.provision_id(+) = '615'
      )
   WHERE r.coverage_category_code = 'LTD'
     AND r.coverage_type_code = 'ABIL'
     AND NVL(r.coverage_plan_number,'UNK') <> 'UNK'
     AND NVL(r.class_code,'UNK') <> 'UNK'
     AND r.claim_event_id='9999999'




;



Select 1:




SELECT p.option_description || ' ' ||  RTRIM(p.description)

              || DECODE(p.duration_code, 'Y',' YEAR(S)',


                                         'M',' MONTH(S)',

                                         'D',' DAY(S)',

                                         'C',' CAL.YEAR',

                                         'L',' LIFE TIME',

                                         'A',' YEARS OF AGE',

                                         'W',' WEEK(S)',NULL)

              || ' ' || p615.option_description
              || ' ' || RTRIM(p615.description)
              || DECODE(p615.duration_code, 'Y',' YEAR(S)',


                                         'M',' MONTH(S)',

                                         'D',' DAY(S)',

                                         'C',' CAL.YEAR',

                                         'L',' LIFE TIME',

                                         'A',' YEARS OF AGE',

                                         'W',' WEEK(S)',NULL)

          FROM claim r,
               coverage_provision p,
               coverage_provision p615
         WHERE p.provision_id  = '614'
           AND p615.provision_id(+) = '615'
           AND r.case_id = p.case_id
           AND r.coverage_category_code = p.coverage_category_code
           AND r.coverage_type_code = p.coverage_type_code
           AND r.coverage_plan_number = p.coverage_plan_number
           AND r.class_code = p.class_code
           AND r.disability_date >= p.eff_date
           AND r.disability_date < NVL(p.term_date, r.disability_date+1)
           AND r.case_id = p615.case_id(+)
           AND r.coverage_category_code = p615.coverage_category_code(+)
           AND r.coverage_type_code = p615.coverage_type_code(+)
           AND r.coverage_plan_number = p615.coverage_plan_number(+)
           AND r.class_code = p615.class_code(+)
           AND r.disability_date >= p615.eff_date(+)
           AND r.disability_date < NVL(p615.term_date(+),
r.disability_date+1)
     and r.coverage_category_code = 'LTD'
     AND r.coverage_type_code = 'ABIL'
     AND NVL(r.coverage_plan_number,'UNK') <> 'UNK'
     AND NVL(r.class_code,'UNK') <> 'UNK'
     and r.claim_event_id='9999999'




;



Update 2:





UPDATE  claim r


    SET r.disability_definition  =

        (SELECT  p.option_description || ' ' ||  RTRIM(p.description)
               || DECODE(p.duration_code, 'Y',' YEAR(S)',
                                          'M',' MONTH(S)',
                                          'D',' DAY(S)',
                                          'C',' CAL.YEAR',
                                          'L',' LIFE TIME',
                                          'A',' YEARS OF AGE',
                                          'W',' WEEK(S)',NULL)
               || ' ' || p615.option_description
               || ' ' || RTRIM(p615.description)
               || DECODE(p615.duration_code, 'Y',' YEAR(S)',
                                          'M',' MONTH(S)',
                                          'D',' DAY(S)',
                                          'C',' CAL.YEAR',
                                          'L',' LIFE TIME',
                                          'A',' YEARS OF AGE',
                                          'W',' WEEK(S)',NULL)
           FROM coverage_provision p, coverage_provision p615
          WHERE p.provision_id  = '614'
            AND r.case_id = p.case_id
            AND r.coverage_category_code = p.coverage_category_code
            AND r.coverage_type_code = p.coverage_type_code
            AND r.coverage_plan_number = p.coverage_plan_number
            AND r.class_code = p.class_code
            AND r.disability_date >= p.eff_date
            AND r.disability_date < NVL(p.term_date, r.disability_date+1)
            AND p.case_id = p615.case_id(+)
            AND p.coverage_category_code = p615.coverage_category_code(+)
            AND p.coverage_type_code = p615.coverage_type_code(+)
            AND p.coverage_plan_number = p615.coverage_plan_number(+)
            AND p.class_code = p615.class_code(+)
            AND p615.provision_id(+) = '615'
            AND r.disability_date >= p615.eff_date(+)
            AND r.disability_date < NVL(p615.term_date(+),
r.disability_date+1)
            )
    WHERE r.coverage_category_code = 'LTD'
      AND r.coverage_type_code = 'ABIL'
      AND NVL(r.coverage_plan_number,'UNK') <> 'UNK'
      AND NVL(r.class_code,'UNK') <> 'UNK'


 and r.claim_event_id='9999999'


 ;


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Prasada.Gunda1_at_hartfordlife.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


Received on Fri Jul 27 2001 - 14:24:17 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US