This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Dec 11, 2012 11:10 PM by Ayham Go to original post RSS
  • 30. Re: SQLPLUS hanging when execute this  Query
    BrendanP Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Many thanks for you.
1 2 3 Previous Next

Legend

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