Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Method to get mod (in PL/SQL)

Aketi JyuuzouOct 12 2013 — edited Oct 19 2013

There is 2 Methods to get mod (in PL/SQL)

begin

    --Method1

    DBMS_Output.Put_Line(mod(17,6));

    --Method2

    DBMS_Output.Put_Line(17 mod 6);

end;

/

My question is Which Method is better

I think Method2 is unDocumented PL/SQL Documented.

Therefore we should use Method1.
This post has been answered by Frank Kulash on Oct 12 2013
Jump to Answer

Comments

Solomon Yakobson 
with T as
(  --OK. Case 2-5 filled with 2-3 and 4-5.
   select 2 StartVal, 5 EndVal, 2 FillStart, 3 FillEnd from dual union all
   select 2 StartVal, 5 EndVal, 4 FillStart, 5 FillEnd from dual union all
   --NOT OK. Case 3-6 filled with 3-4 and 4-7, point 4-4 is fileld more than once, point 7-7 is not needed.
   select 3 StartVal, 6 EndVal, 3 FillStart, 4 FillEnd from dual union all
   select 3 StartVal, 6 EndVal, 4 FillStart, 7 FillEnd from dual union all
   --NOT OK. Case 4-7 filled with 3-4 and 4-7, too much filled in point "4-4" and not needed point "3-3"
   select 4 StartVal, 7 EndVal, 3 FillStart, 4 FillEnd from dual union all
   select 4 StartVal, 7 EndVal, 4 FillStart, 7 FillEnd from dual union all  
   --NOT OK. Case 4-8 filled with 4-5 and 7-8, missing is point 6-6
   select 4 StartVal, 8 EndVal, 4 FillStart, 5 FillEnd from dual union all
   select 4 StartVal, 8 EndVal, 7 FillStart, 8 FillEnd from dual union all
   --OK. Case 5-10 filled with 5-6 and 7-8 and 9-10
   select 5 StartVal, 10 EndVal, 5 FillStart, 6 FillEnd from dual union all
   select 5 StartVal, 10 EndVal, 7 FillStart, 8 FillEnd from dual union all
   select 5 StartVal, 10 EndVal, 9 FillStart, 10 FillEnd from dual union all
   --NOT OK. Case 5-11 filled with 5-6 and 8-9 and 10-11, missing is point 7-7.
   select 5 StartVal, 11 EndVal, 5 FillStart, 6 FillEnd from dual union all
   select 5 StartVal, 11 EndVal, 8 FillStart, 9 FillEnd from dual union all
   select 5 StartVal, 11 EndVal, 10 FillStart, 11 FillEnd from dual
)
select  distinct startval,
                 endval
  from  (
         select  startval,
                 endval,
                 case
                   when lag(fillend,1,startval - 1) over(partition by startval,endval order by fillstart) < fillstart - 1 then 'Gap'
                   when lag(fillend,1,startval - 1) over(partition by startval,endval order by fillstart) >= fillstart then 'Overlap'
                   when row_number() over(partition by startval,endval order by fillstart desc) = 1 and endval > fillend then 'Gap'
                   when row_number() over(partition by startval,endval order by fillstart desc) = 1 and endval < fillend then 'Overlap'
                   else 'OK'
                 end status
           from  t
        )
  where status != 'OK'
/

  STARTVAL     ENDVAL
---------- ----------
         3          6
         4          7
         4          8
         5         11

SQL>
SY.
1 person found this helpful
WestDrayton
Seems correct. Thx.
And seems i understand all the logic behind.
with T as
(  --Gap1
   select 3 StartVal, 11 EndVal, 3 FillStart, 6 FillEnd from dual union all
   select 3 StartVal, 11 EndVal, 8 FillStart, 11 FillEnd from dual union all
--Overlap2      
   select 5 StartVal, 11 EndVal, 5 FillStart, 6 FillEnd from dual union all
   select 5 StartVal, 11 EndVal, 7 FillStart, 8 FillEnd from dual union all
   --Overlap1
   select 5 StartVal, 10 EndVal, 4 FillStart, 6 FillEnd from dual union all
   select 5 StartVal, 10 EndVal, 7 FillStart, 10 FillEnd from dual union all
--Gap2
   select 3 StartVal, 6 EndVal, 3 FillStart, 4 FillEnd from dual union all
   select 3 StartVal, 6 EndVal, 5 FillStart, 7 FillEnd from dual
)
        select  startval,
                 endval,
                 FillStart,
                 FillEnd,
                 case
                   when lag(fillend,1,startval - 1) over(partition by startval,endval order by fillstart) < fillstart - 1 then 'Gap1'
                   when lag(fillend,1,startval - 1) over(partition by startval,endval order by fillstart) >= fillstart then 'Overlap1'
                   when row_number() over(partition by startval,endval order by fillstart desc) = 1 and endval > fillend then 'Gap2'
                   when row_number() over(partition by startval,endval order by fillstart desc) = 1 and endval < fillend then 'Overlap2'
                   else 'OK'
                 end status
           from  t
           order by 1,2;
           /*
           3	6	3	4	OK
3	6	5	7	Overlap2
3	11	3	6	OK
3	11	8	11	Gap1
5	10	4	6	Overlap1
5	10	7	10	OK
5	11	5	6	OK
5	11	7	8	Gap2
           */
WestDrayton
I have one more additional requirement: all 4 columns have now additional attribute that describes if the value is in Months or in Years.
For example in Case1 all vlaues are in Monthes, Case1 is valid. Case2 has also all columns in both records as monthes, but is not Valid, because area (47-47) overlaps. Case3 has on first record FillStart as 3 Years (which is 36 monthes) and is Valid. All 4 columns can have in any combination the Month/Year choosen.
with T as
(  --Case1 OK.
   select 'Case1' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
   select 'Case1' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
   --Case2 NOT OK.
   select 'Case2' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 35 FillStart, 'Month' TypeFS, 47 FillEnd, 'Month' TypeFE from dual union all
   select 'Case2' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 47 FillStart, 'Month' TypeFS, 215 FillEnd, 'Month' TypeFE from dual union all
   --Case3 OK.
   select 'Case3' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 3 FillStart, 'Year' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
   select 'Case3' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
   --Case4 NOT OK.
   select 'Case4' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 3 FillStart, 'Year' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
   select 'Case4' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 48 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
   --Case5 OK.
   select 'Case5' Segment, 3 StartVal, 'Year' TypeSV, 216 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
   select 'Case5' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all   
   --Case6 OK.
   select 'Case6' Segment, 3 StartVal, 'Year' TypeSV, 18 EndVal, 'Year' TypeEV, 36 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
   select 'Case6' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 61 FillStart, 'Month' TypeFS, 18 FillEnd, 'Year' TypeFE from dual union all   
   --Case7 NOT OK.
   select 'Case7' Segment, 3 StartVal, 'Year' TypeSV, 18 EndVal, 'Year' TypeEV, 35 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
   select 'Case7' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 60 FillStart, 'Month' TypeFS, 18 FillEnd, 'Year' TypeFE from dual
)
select  Segment,
       (case when TypeSV='Year' then StartVal*12 else StartVal end) m_StartVal,
       (case when TypeEV='Year' then EndVal*12 else EndVal end) m_EndVal,
       (case when TypeFS='Year' then FillStart*12 else FillStart end) m_FillStart,
       (case when TypeFE='Year' then FillEnd*12 else FillEnd end) m_FillEnd,                 
      case
          when lag(fillend,1,startval - 1) over(partition by Segment, startval,endval order by fillstart) < fillstart - 1 then 'Gap1'
          when lag(fillend,1,startval - 1) over(partition by Segment, startval,endval order by fillstart) >= fillstart then 'Overlap1'
          when row_number() over(partition by Segment, startval,endval order by fillstart desc) = 1 and endval > fillend then 'Gap2'
          when row_number() over(partition by Segment, startval,endval order by fillstart desc) = 1 and endval < fillend then 'Overlap2'
          else 'OK'
        end status
  from  t
  order by 1,2;
Edited by: CharlesRoos on May 3, 2010 12:32 AM
Aketi Jyuuzou
There is hierarchicalquery version :D
with T as
(  --OK. Case 2-5 filled with 2-3 and 4-5.
   select 2 StartVal, 5 EndVal, 2 FillStart, 3 FillEnd from dual union all
   select 2 StartVal, 5 EndVal, 4 FillStart, 5 FillEnd from dual union all
   --NOT OK. Case 3-6 filled with 3-4 and 4-7, point 4-4 is fileld more than once, point 7-7 is not needed.
   select 3 StartVal, 6 EndVal, 3 FillStart, 4 FillEnd from dual union all
   select 3 StartVal, 6 EndVal, 4 FillStart, 7 FillEnd from dual union all
   --NOT OK. Case 4-7 filled with 3-4 and 4-7, too much filled in point "4-4" and not needed point "3-3"
   select 4 StartVal, 7 EndVal, 3 FillStart, 4 FillEnd from dual union all
   select 4 StartVal, 7 EndVal, 4 FillStart, 7 FillEnd from dual union all  
   --NOT OK. Case 4-8 filled with 4-5 and 7-8, missing is point 6-6
   select 4 StartVal, 8 EndVal, 4 FillStart, 5 FillEnd from dual union all
   select 4 StartVal, 8 EndVal, 7 FillStart, 8 FillEnd from dual union all
   --OK. Case 5-10 filled with 5-6 and 7-8 and 9-10
   select 5 StartVal, 10 EndVal, 5 FillStart, 6 FillEnd from dual union all
   select 5 StartVal, 10 EndVal, 7 FillStart, 8 FillEnd from dual union all
   select 5 StartVal, 10 EndVal, 9 FillStart, 10 FillEnd from dual union all
   --NOT OK. Case 5-11 filled with 5-6 and 8-9 and 10-11, missing is point 7-7.
   select 5 StartVal, 11 EndVal, 5 FillStart, 6 FillEnd from dual union all
   select 5 StartVal, 11 EndVal, 8 FillStart, 9 FillEnd from dual union all
   select 5 StartVal, 11 EndVal, 10 FillStart, 11 FillEnd from dual)
select StartVal,EndVal
from (select StartVal,EndVal,FillStart,FillEnd,
      min(FillStart) over(partition by StartVal,EndVal) as MinFillStart,
      count(*) over(partition by StartVal,EndVal) as cnt
      from t)
where connect_by_IsLeaf = 1
  and (EndVal != FillEnd or  Level != cnt)
start with FillStart = MinFillStart
connect by nocycle prior StartVal = StartVal
               and prior EndVal = EndVal
               and prior FillEnd+1 = FillStart;

STARTVAL  ENDVAL
--------  ------
       3       6
       4       7
       4       8
       5      11
1 person found this helpful
WestDrayton
Thx, but Look my last post.
All 4 columns can be either Month or Year values.
How the query should be then?
Aketi Jyuuzou
Answer
We can calc using case expression ;-)
col path for a30

with T as
(  --Case1 OK.
select 'Case1' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case1' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case2 NOT OK.
select 'Case2' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 35 FillStart, 'Month' TypeFS, 47 FillEnd, 'Month' TypeFE from dual union all
select 'Case2' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 47 FillStart, 'Month' TypeFS, 215 FillEnd, 'Month' TypeFE from dual union all
--Case3 OK.
select 'Case3' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 3 FillStart, 'Year' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case3' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case4 NOT OK.
select 'Case4' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 3 FillStart, 'Year' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case4' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 48 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case5 OK.
select 'Case5' Segment, 3 StartVal, 'Year' TypeSV, 216 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case5' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all   
--Case6 OK. OK??? I suppose this is not OK
select 'Case6' Segment, 3 StartVal, 'Year' TypeSV, 18 EndVal, 'Year' TypeEV, 36 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
select 'Case6' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 61 FillStart, 'Month' TypeFS, 18 FillEnd, 'Year' TypeFE from dual union all   
--Case7 NOT OK.
select 'Case7' Segment, 3 StartVal, 'Year' TypeSV, 18 EndVal, 'Year' TypeEV, 35 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
select 'Case7' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 60 FillStart, 'Month' TypeFS, 18 FillEnd, 'Year' TypeFE from dual
)
select Segment,StartVal,EndVal,sys_connect_by_path(FillStart || '->' || FillEnd,',') as path
from (select Segment,
      case TypeSV when 'Year' then 12*StartVal  else StartVal end as StartVal,
      case TypeEV when 'Year' then 12*EndVal    else EndVal end as EndVal,
      case TypeFS when 'Year' then 12*FillStart else FillStart end as FillStart,
      case TypeFE when 'Year' then 12*FillEnd   else FillEnd end as FillEnd,
      min(case TypeFS when 'Year' then 12*FillStart else FillStart end)
      over(partition by Segment) as MinFillStart,
      count(*) over(partition by Segment) as cnt
      from t)
where connect_by_IsLeaf = 1
  and (EndVal != FillEnd or  Level != cnt)
start with FillStart = MinFillStart
connect by nocycle prior Segment = Segment
               and prior FillEnd+1 = FillStart;

SEGME   STARTVAL     ENDVAL  PATH
-----  ---------  ---------  -------
Case2         36        216  ,35->47
Case4         36        216  ,36->48
Case6         36        216  ,36->48
Case7         36        216  ,35->48
Marked as Answer by WestDrayton · Sep 27 2020
WestDrayton
Yes, case6 is not ok.
Query seems correct.
Thx.

Edited by: CharlesRoos on May 3, 2010 2:45 AM
Aketi Jyuuzou
If there is below data case which min(FillStart) > StartVal,
we have to add Level > 2 or prior FillStart = StartVal at connect by clause :-)
with T as
(  select 1 StartVal, 10 EndVal, 3 FillStart,  6 FillEnd from dual union all
   select 1 StartVal, 10 EndVal, 7 FillStart,  8 FillEnd from dual union all
   select 1 StartVal, 10 EndVal, 9 FillStart, 10 FillEnd from dual)
select StartVal,EndVal
from (select StartVal,EndVal,FillStart,FillEnd,
      min(FillStart) over(partition by StartVal,EndVal) as MinFillStart,
      count(*) over(partition by StartVal,EndVal) as cnt
      from t)
where connect_by_IsLeaf = 1
  and (EndVal != FillEnd or Level != cnt)
start with FillStart = MinFillStart
connect by nocycle (Level > 2 or prior FillStart = StartVal) //to add this logic
               and prior StartVal = StartVal
               and prior EndVal = EndVal
               and prior FillEnd+1 = FillStart;
1 person found this helpful
Aketi Jyuuzou
The other way using recursive with clause B-)
with T as
(  select 2 StartVal, 5 EndVal, 2 FillStart, 3 FillEnd from dual union all
   select 2 StartVal, 5 EndVal, 4 FillStart, 5 FillEnd from dual union all
   select 3 StartVal, 6 EndVal, 3 FillStart, 4 FillEnd from dual union all
   select 3 StartVal, 6 EndVal, 4 FillStart, 7 FillEnd from dual union all
   select 4 StartVal, 7 EndVal, 3 FillStart, 4 FillEnd from dual union all
   select 4 StartVal, 7 EndVal, 4 FillStart, 7 FillEnd from dual union all
   select 4 StartVal, 8 EndVal, 4 FillStart, 5 FillEnd from dual union all
   select 4 StartVal, 8 EndVal, 7 FillStart, 8 FillEnd from dual union all
   select 5 StartVal, 10 EndVal, 5 FillStart, 6 FillEnd from dual union all
   select 5 StartVal, 10 EndVal, 7 FillStart, 8 FillEnd from dual union all
   select 5 StartVal, 10 EndVal, 9 FillStart, 10 FillEnd from dual union all
   select 5 StartVal, 11 EndVal, 5 FillStart, 6 FillEnd from dual union all
   select 5 StartVal, 11 EndVal, 8 FillStart, 9 FillEnd from dual union all
   select 5 StartVal, 11 EndVal, 10 FillStart, 11 FillEnd from dual),
rec(StartVal,EndVal,FillStart,FillEnd,Val) as(
select StartVal,EndVal,FillStart,FillEnd,FillStart
  from t
union all
select StartVal,EndVal,FillStart,FillEnd,Val+1
  from rec
 where Val+1 <= FillEnd)
select StartVal,EndVal
  from rec
group by StartVal,EndVal
having not(EndVal-StartVal+1
          =all(count(*),
               count(distinct case when Val between StartVal and EndVal
                                   then Val end)))
order by StartVal,EndVal;

StartVal  EndVal
--------  ------
       3       6
       4       7
       4       8
       5      11
1 person found this helpful
Solomon Yakobson
CharlesRoos wrote:
All 4 columns can have in any combination the Month/Year choosen.
with T as
(  --Case1 OK.
   select 'Case1' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
   select 'Case1' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
   --Case2 NOT OK.
   select 'Case2' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 35 FillStart, 'Month' TypeFS, 47 FillEnd, 'Month' TypeFE from dual union all
   select 'Case2' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 47 FillStart, 'Month' TypeFS, 215 FillEnd, 'Month' TypeFE from dual union all
   --Case3 OK.
   select 'Case3' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 3 FillStart, 'Year' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
   select 'Case3' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
   --Case4 NOT OK.
   select 'Case4' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 3 FillStart, 'Year' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
   select 'Case4' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 48 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
   --Case5 OK.
   select 'Case5' Segment, 3 StartVal, 'Year' TypeSV, 216 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
   select 'Case5' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all   
   --Case6 OK.
   select 'Case6' Segment, 3 StartVal, 'Year' TypeSV, 18 EndVal, 'Year' TypeEV, 36 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
   select 'Case6' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 61 FillStart, 'Month' TypeFS, 18 FillEnd, 'Year' TypeFE from dual union all   
   --Case7 NOT OK.
   select 'Case7' Segment, 3 StartVal, 'Year' TypeSV, 18 EndVal, 'Year' TypeEV, 35 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
   select 'Case7' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 60 FillStart, 'Month' TypeFS, 18 FillEnd, 'Year' TypeFE from dual
)
select  Segment,
        m_StartVal,
        m_EndVal,
        m_FillStart,
        m_FillEnd,
        case
          when lag(m_FillEnd,1,m_StartVal - 1) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart) <  m_FillStart - 1 then 'Gap1'
          when lag(m_FillEnd,1,m_StartVal - 1) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart) >= m_FillStart then 'Overlap1'
          when row_number() over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart desc) = 1 and m_EndVal > m_FillEnd then 'Gap2'
          when row_number() over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart desc) = 1 and m_EndVal < m_FillEnd then 'Overlap2'
          else 'OK'
        end status
  from  (
         select  Segment,
                 case when TypeSV='Year' then StartVal*12 else StartVal end m_StartVal,
                 case when TypeEV='Year' then EndVal*12 else EndVal end m_EndVal,
                 case when TypeFS='Year' then FillStart*12 else FillStart end m_FillStart,
                 case when TypeFE='Year' then FillEnd*12 else FillEnd end m_FillEnd
           from  t
        )
  order by 1,
           2
/

SEGME M_STARTVAL   M_ENDVAL M_FILLSTART  M_FILLEND STATUS
----- ---------- ---------- ----------- ---------- --------
Case1         36        216          36         48 OK
Case1         36        216          49        216 OK
Case2         36        216          35         47 Overlap1
Case2         36        216          47        215 Overlap1
Case3         36        216          36         48 OK
Case3         36        216          49        216 OK
Case4         36        216          36         48 OK
Case4         36        216          48        216 Overlap1
Case5         36        216          36         48 OK
Case5         36        216          49        216 OK
Case6         36        216          36         48 OK

SEGME M_STARTVAL   M_ENDVAL M_FILLSTART  M_FILLEND STATUS
----- ---------- ---------- ----------- ---------- --------
Case6         36        216          61        216 Gap1
Case7         36        216          35         48 Overlap1
Case7         36        216          60        216 Gap1

14 rows selected.

SQL>
SY.
1 person found this helpful
WestDrayton
See Case10 below, algorithm is wrong there, it shouldnt output Gap1.
with T as
(     
   select 'Case10' Segment, 3 StartVal, 'Year' TypeSV, 84 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 5 FillEnd, 'Year' TypeFE from dual union all   
   select 'Case10' Segment, 3 StartVal, 'Year' TypeSV, 84 EndVal, 'Month' TypeEV, 6 FillStart, 'Year' TypeFS, 7 FillEnd, 'Year' TypeFE from dual
)
select  Segment,
        m_StartVal,
        m_EndVal,
        m_FillStart,
        m_FillEnd,
        case
          when lag(m_FillEnd,1,m_StartVal - 1) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart) <  m_FillStart - 1 then 'Gap1'
          when lag(m_FillEnd,1,m_StartVal - 1) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart) >= m_FillStart then 'Overlap1'
          when row_number() over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart desc) = 1 and m_EndVal > m_FillEnd then 'Gap2'
          when row_number() over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart desc) = 1 and m_EndVal < m_FillEnd then 'Overlap2'
          else 'OK'
        end status
  from  (
         select  Segment,
                 case when TypeSV='Year' then StartVal*12 else StartVal end m_StartVal,
                 case when TypeEV='Year' then EndVal*12 else EndVal end m_EndVal,
                 case when TypeFS='Year' then FillStart*12 else FillStart end m_FillStart,
                 case when TypeFE='Year' then FillEnd*12 else FillEnd end m_FillEnd
           from  t
        )
  order by 1,
           2,
           3,
           4;
/*Case10	36	84	36	60	OK
Case10	36	84	72	84	Gap1*/
The rules for (FillStart/TypeFS,FillEnd/TypeFE) pairs are as following:

1. If on Prevoius row TypeFE=Year then on Current row FillStart must be as following:
1.1 if on Current row TypeFS=Year, then on current row FillStart must be greater by 1 than on Previous row FillEnd was.
Example of "OK Case":
row1=(FillStart/TypeFS,FillEnd/TypeFE) =(36/Month, 5/Year)
row2=(FillStart/TypeFS,FillEnd/TypeFE) =(6/Year, 7/Year)
Example of "NOT OK Case":
row1=(FillStart/TypeFS,FillEnd/TypeFE) =(36/Month, 5/Year)
row2=(FillStart/TypeFS,FillEnd/TypeFE) =(5/Year, 7/Year)
1.2 if on Current row TypeFS=Month, then on current row FillStart must be greater by 1 than on Previous row "FillEnd converted to monthes" was.
Example of "OK Case":
row1=(FillStart/TypeFS,FillEnd/TypeFE) =(36/Month, 5/Year)
row2=(FillStart/TypeFS,FillEnd/TypeFE) =(61/Month, 7/Year)
Example of "NOT OK Case":
row1=(FillStart/TypeFS,FillEnd/TypeFE) =(36/Month, 5/Year)
row2=(FillStart/TypeFS,FillEnd/TypeFE) =(60/Month, 7/Year)

Example of "OK Case" for filling area 3Y-7Y:
row1=(FillStart/TypeFS,FillEnd/TypeFE) =(36/Month, 4/Year)
row2=(FillStart/TypeFS,FillEnd/TypeFE) =(5/Year, 71/Month)
row3=(FillStart/TypeFS,FillEnd/TypeFE) =(6/Year, 7/Year)
WestDrayton
Not answered.
Solomon Yakobson
CharlesRoos wrote:
Not answered.
I see. Here is a fix:
with T as
(  --Case1 OK.
   select 'Case1' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
   select 'Case1' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
   --Case2 NOT OK.
   select 'Case2' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 35 FillStart, 'Month' TypeFS, 47 FillEnd, 'Month' TypeFE from dual union all
   select 'Case2' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 47 FillStart, 'Month' TypeFS, 215 FillEnd, 'Month' TypeFE from dual union all
   --Case3 OK.
   select 'Case3' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 3 FillStart, 'Year' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
   select 'Case3' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
   --Case4 NOT OK.
   select 'Case4' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 3 FillStart, 'Year' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
   select 'Case4' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 48 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
   --Case5 OK.
   select 'Case5' Segment, 3 StartVal, 'Year' TypeSV, 216 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
   select 'Case5' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all   
   --Case6 OK.
   select 'Case6' Segment, 3 StartVal, 'Year' TypeSV, 18 EndVal, 'Year' TypeEV, 36 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
   select 'Case6' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 61 FillStart, 'Month' TypeFS, 18 FillEnd, 'Year' TypeFE from dual union all   
   --Case7 NOT OK.
   select 'Case7' Segment, 3 StartVal, 'Year' TypeSV, 18 EndVal, 'Year' TypeEV, 35 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
   select 'Case7' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 60 FillStart, 'Month' TypeFS, 18 FillEnd, 'Year' TypeFE from dual union all
   select 'Case10' Segment, 3 StartVal, 'Year' TypeSV, 84 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 5 FillEnd, 'Year' TypeFE from dual union all   
   select 'Case10' Segment, 3 StartVal, 'Year' TypeSV, 84 EndVal, 'Month' TypeEV, 6 FillStart, 'Year' TypeFS, 7 FillEnd, 'Year' TypeFE from dual
)
select  Segment,
        m_StartVal,
        m_EndVal,
        m_FillStart,
        m_FillEnd,
        case
          when lag(m_FillEnd,1,m_StartVal - gap_val) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart) <  m_FillStart - gap_val then 'Gap1'
          when lag(m_FillEnd,1,m_StartVal - gap_val) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart) >= m_FillStart then 'Overlap1'
          when row_number() over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart desc) = 1 and m_EndVal > m_FillEnd then 'Gap2'
          when row_number() over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart desc) = 1 and m_EndVal < m_FillEnd then 'Overlap2'
          else 'OK'
        end status
  from  (
         select  Segment,
                 m_StartVal,
                 m_EndVal,
                 m_FillStart,
                 m_FillEnd,
                 case
                   when lag(TypeFE,1,TypeFS) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart)='Year' then 12 else 1 end gap_val
           from  (
                  select  Segment,
                          case when TypeSV='Year' then StartVal*12 else StartVal end m_StartVal,
                          case when TypeEV='Year' then EndVal*12 else EndVal end m_EndVal,
                          case when TypeFS='Year' then FillStart*12 else FillStart end m_FillStart,
                          case when TypeFE='Year' then FillEnd*12 else FillEnd end m_FillEnd,
                          TypeFS,
                          TypeFE
                    from  t
                 )
        )
  order by 1,
           2
/

SEGMEN M_STARTVAL   M_ENDVAL M_FILLSTART  M_FILLEND STATUS
------ ---------- ---------- ----------- ---------- --------
Case1          36        216          36         48 OK
Case1          36        216          49        216 OK
Case10         36         84          36         60 OK
Case10         36         84          72         84 OK
Case2          36        216          35         47 Overlap1
Case2          36        216          47        215 Overlap1
Case3          36        216          36         48 OK
Case3          36        216          49        216 OK
Case4          36        216          36         48 OK
Case4          36        216          48        216 Overlap1
Case5          36        216          36         48 OK

SEGMEN M_STARTVAL   M_ENDVAL M_FILLSTART  M_FILLEND STATUS
------ ---------- ---------- ----------- ---------- --------
Case5          36        216          49        216 OK
Case6          36        216          36         48 OK
Case6          36        216          61        216 Gap1
Case7          36        216          35         48 Overlap1
Case7          36        216          60        216 OK

16 rows selected.

SQL>
SY.

Edited by: Solomon Yakobson on May 4, 2010 1:34 PM
WestDrayton
Thx, seems perfect.

with T as
(  --Case1 OK.
   select 'Case11' Segment, 36 StartVal, 'Month' TypeSV, 84 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
   select 'Case11' Segment, 3 StartVal, 'Year' TypeSV, 84 EndVal, 'Month' TypeEV, 5 FillStart, 'Year' TypeFS, 71 FillEnd, 'Month' TypeFE from dual union all
   select 'Case11' Segment, 36 StartVal, 'Month' TypeSV, 7 EndVal, 'Year' TypeEV, 6 FillStart, 'Year' TypeFS, 7 FillEnd, 'Year' TypeFE from dual
)
select  Segment,
        m_StartVal,
        m_EndVal,
        m_FillStart,
        m_FillEnd,
        case
          when lag(m_FillEnd,1,m_StartVal - gap_val) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart) <  m_FillStart - gap_val then 'Gap1'
          when lag(m_FillEnd,1,m_StartVal - gap_val) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart) >= m_FillStart then 'Overlap1'
          when row_number() over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart desc) = 1 and m_EndVal > m_FillEnd then 'Gap2'
          when row_number() over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart desc) = 1 and m_EndVal < m_FillEnd then 'Overlap2'
          else 'OK'
        end status
  from  (
         select  Segment,
                 m_StartVal,
                 m_EndVal,
                 m_FillStart,
                 m_FillEnd,
                 case
                   when lag(TypeFE,1,TypeFS) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart)='Year' then 12 else 1 end gap_val
           from  (
                  select  Segment,
                          case when TypeSV='Year' then StartVal*12 else StartVal end m_StartVal,
                          case when TypeEV='Year' then EndVal*12 else EndVal end m_EndVal,
                          case when TypeFS='Year' then FillStart*12 else FillStart end m_FillStart,
                          case when TypeFE='Year' then FillEnd*12 else FillEnd end m_FillEnd,
                          TypeFS,
                          TypeFE
                    from  t
                 )
        )
  order by 1,
           2
;
/*Case11	36	84	36	48	OK
Case11	36	84	60	71	OK
Case11	36	84	72	84	OK*/
Aketi Jyuuzou
OOPS my hierarchical solution needs using minus like below :-(
with T as
(select 1 StartVal, 10 EndVal, 2 FillStart, 10 FillEnd from dual)
select StartVal,EndVal from t
minus
select StartVal,EndVal
from (select StartVal,EndVal,FillStart,FillEnd,
      min(FillStart) over(partition by StartVal,EndVal) as MinFillS,
      count(*) over(partition by StartVal,EndVal) as cnt
      from t)
where connect_by_IsLeaf = 1
  and EndVal = FillEnd 
  and Level = cnt
start with FillStart = all(StartVal,MinFillS)
connect by nocycle prior StartVal = StartVal
               and prior EndVal = EndVal
               and prior FillEnd+1 = FillStart;

StartVal  EndVal
--------  ------
       1      10
WestDrayton
Aketi, thx, but,
1) hierarchical thing is always slow(er) and error friendly.
2) We must use Month/Year parameters as well.
WestDrayton
Aketi, thx, but,
1) hierarchical thing is always slow(er) and error friendly.
2) We must use Month/Year parameters as well.
1 - 17
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 16 2013
Added on Oct 12 2013
13 comments
3,808 views