1 2 3 Previous Next 33 Replies Latest reply: Dec 12, 2012 1:10 AM by Ayham Go to original post RSS
      • 30. Re: SQLPLUS hanging when execute this  Query
        BrendanP
        I took up the challenge of implementing a model solution. It was harder than I thought and the solution is somewhat tortuous but it gives another SQL-only option. I'll post if anyone is interested.
        • 31. Re: SQLPLUS hanging when execute this  Query
          Ayham
          Ok. Anyway many thanks for you and we will see other how can they help us.
          • 32. Re: SQLPLUS hanging when execute this  Query
            jihuyao
            So applying the new rule is like turning off weak link conditionally (i.e. set it percentage=0). This can be determined below as in the view named broken_weak_link (for any change in the rule, just modify the view).
             create or replace view broken_weak_link
            as select * from temp_base
            where percentage<50
            and loc1 in (
            select loc1 loc from temp_base where percentage>=50
            union all 
            select loc2 loc from temp_base where percentage>=50
            )
            and loc2 in (
            select loc1 loc from temp_base where percentage>=50
            union all 
            select loc2 loc from temp_base where percentage>=50
            )
            /
            With the view above as reference, re-create the temp view to turn off all broken weak links and run the pl/sql and check the result as below
            create or replace view temp
            as select t1.id, t1.loc1, t1.loc2,
            case
               when exists (select * from broken_weak_link t2 where t2.id=t1.id) then 0
               else t1.percentage
            end as percentage,
            t1.gid
            from temp_base t1
            /
            
            --run pl/sql
            --check result
            
            SQL> ed
            Wrote file afiedt.buf
            
              1  select * from temp
              2* order by gid, id
            SQL> /
            
                    ID LOC1                 LOC2                 PERCENTAGE        GID
            ---------- -------------------- -------------------- ---------- ----------
                     1 1                    2                            90          1
                     3 4                    3                            30          2
                     4 10                   8                           100          2
                     6 8                    4                            50          2
                     2 5                    7                            60          3
                     7 7                    12                          100          3
                     5 2                    8                             0
            
            7 rows selected.
            
            SQL> ed
            Wrote file afiedt.buf
            
              1  select * from temp_base
              2* order by gid, id
            SQL> /
            
                    ID LOC1                 LOC2                 PERCENTAGE        GID
            ---------- -------------------- -------------------- ---------- ----------
                     1 1                    2                            90          1
                     3 4                    3                            30          2
                     4 10                   8                           100          2
                     6 8                    4                            50          2
                     2 5                    7                            60          3
                     7 7                    12                          100          3
                     5 2                    8                            20
            
            7 rows selected.
            • 33. Re: SQLPLUS hanging when execute this  Query
              Ayham
              Many thanks for you.
              1 2 3 Previous Next