This discussion is archived
1 2 3 Previous Next 34 Replies Latest reply: Feb 22, 2013 6:07 AM by 980086 Go to original post RSS
  • 15. Re: number plan issue in pl/sql
    980086 Newbie
    Currently Being Moderated
    I cannot hard code any value.there will be 100000 of values in real life.So I have to make the code generic.
    I cannot hard code as
    with code_table as
    (
    select 93 code from dual union all
    Select 9370 From Dual Union All
    Select 9371 From Dual Union All
    Select 9372 From Dual Union All
    Select 9373 From Dual Union All
    Select 9375 From Dual Union All
    Select 9376 From Dual Union All
    Select 9377 From Dual Union All
    Select 9380 From Dual Union All
    Select 93809 From Dual Union All
    Select 93810 From Dual
    )

    Don't misunderstand me if I am annoying you.....
    please help...
  • 16. Re: number plan issue in pl/sql
    ascheffer Expert
    Currently Being Moderated
    This looks pretty generic to me
    select code
         , coalesce( ( lead( code ) over ( order by to_char( code ) ) - 1 )
                   , min( code ) over( order by to_char( code ) )
                   ) || 'F' code_to
    from code_table
    order by to_char( code )
  • 17. Re: number plan issue in pl/sql
    980086 Newbie
    Currently Being Moderated
    oopss ....ya its working....
    But not in the required format......
    what I need I want to explain in a very short example....
    code
    14
    1485
    23
    2306
    93
    93725
    93726

    new format will be
    14 - 1484F
    1485 - 1485F
    1486 - 14F
    23 - 2305F
    2306 - 2306F
    2307 - 23F
    93 - 93724F
    93725 - 93726F
    93727 - 93F


    Please help me to get in this exact format....
  • 18. Re: number plan issue in pl/sql
    ascheffer Expert
    Currently Being Moderated
    ????
    select to_char( code ) || ' - ' ||
          coalesce( ( lead( code ) over ( order by to_char( code ) ) - 1 )
                   , min( code ) over( order by to_char( code ) )
                   ) || 'F' code_to
    from code_table
    order by to_char( code )
  • 19. Re: number plan issue in pl/sql
    980086 Newbie
    Currently Being Moderated
    I have used this code having
    code table-
         14
         1485
         23
         2306
         93
         93725
         93726


    select code,
    case
    when (abs(code - nvl(lead(code, 1) over(ORDER BY to_char(code) asc),
    min(code) over(ORDER BY to_char(code) asc)))) > 1 then
    nvl(lead(code, 1) over(ORDER BY to_char(code) asc),
    min(code) over(ORDER BY to_char(code) asc) + 1) - 1
    else
    code
    end || 'F' final
    from code_table

    O/P I got-
         14     ---- 1484F
         1485 ----     22F
         23     --- 2305F
         2306     --- 92F
         93     --- 93724F
         93725---     93725F
         93726     ---- 14F

    But I want --
    14 ---- 1484F
    1485 ---- 1485F
    1486 ----14F
    23 ---- 2305F
    2306 -- 2306F
    2307 --- 23F
    93 --- 93724F
    93725 -- 93726F
    93727 -- 93F

    Edited by: 977083 on Feb 4, 2013 11:11 PM
  • 20. Re: number plan issue in pl/sql
    ascheffer Expert
    Currently Being Moderated
    And what's the logic for this, for instance what happens to code 93726 ?
  • 21. Re: number plan issue in pl/sql
    980086 Newbie
    Currently Being Moderated
    If the difference is 1 then it will include in the same group....
    like
    if
    14
    1484
    1485
    1486
    1492
    then in final number plan number starting with 1485 and 1486 will be in the same continuation row
    here it will be
    14 ------ 1483F
    1484 --- 1486F
    1487 ---- 1491F
    1492 --- 14F

    Here F denotes the end of that number series....
    As 1483F means end of number series starting with 1483.....
    So 1484 --- 1486F means in this row.....number starting with 1484 , 1485,1486 are included..1486F denotes end of 1486 series.
    So 1485,1486 is already been included in this series..

    Edited by: 977083 on Feb 5, 2013 12:05 AM
  • 22. Re: number plan issue in pl/sql
    980086 Newbie
    Currently Being Moderated
    And also ...
    like
    14
    1408
    2510
    2530
    93
    9306

    here when we are dealing with number starting with 14...
    it would be
    14 ---- 1407F
    1408 --- 1408F
    1409 ---- 14F (Not 2509F)....like wise for all cases...
  • 23. Re: number plan issue in pl/sql
    ascheffer Expert
    Currently Being Moderated
    And why not
    14 - 14F
    15 - 1407F
    1408 - 1408F
    1409 - 14F
    I have not seen you mentioning anything about grouping with the first two digits.
  • 24. Re: number plan issue in pl/sql
    980086 Newbie
    Currently Being Moderated
    Its not only grouping with first 2 digit....
    I have to first sort like this- SELECT * FROM code_table order by to_char(code);
    here it will be sorted like this....
    14
    1408
    2510
    93
    9306
    with this when you see 14 series is completed 25 series starting we need to have 14F.
    14---1407F
    1408---1408F
    1409----14F (NOT 2509F ....because next one will be 25 series...)
    25 ----2509F
    2510 ---- 2510F
    2511---25F (because next one is 93 series....)
  • 25. Re: number plan issue in pl/sql
    ascheffer Expert
    Currently Being Moderated
    By why do you start a new group after 1408 and not after 14?

    And what happens if this is your input

    1
    14
    143
    14301
    14303
    14306
    1432
    14322
    14327
    145
    146

    Edited by: ascheffer on Feb 5, 2013 11:57 AM
  • 26. Re: number plan issue in pl/sql
    980086 Newbie
    Currently Being Moderated
    14 or 93(1ST 1/2/3 DIGITS) all are country codes....
    After sorting this is the series right?
    1
    14
    143
    14301
    14303
    14306
    1432
    14322
    14327
    145
    146

    if you say this is the series found after sorting then 1 is country code.
    it will be
    1---13F
    14--14F
    15---142F
    143---14301F
    14302---14302F
    14303----14303F
    14304---14305F
    14306----14306F
    14307----1431F
    1432---1432F
    14321---14321F
    14322---14322F
    14323----14326F
    14327----14327F
    14328----144F
    145----146F
    147-----1F
  • 27. Re: number plan issue in pl/sql
    ascheffer Expert
    Currently Being Moderated
    Still don't see any logic in it
    1
    14
    143
    14301
    14303
    14306
    1432
    14322
    14327
    145
    146

    if you say this is the series found after sorting then 1 is country code.
    it will be
    1---13F <--- * group 1 forms group ending 1 before next element *
    14--14F
    15---142F
    143---14301F <--- * group 143 forms group ending with next element *
    14302---14302F
    14303----14303F
    14304---14305F
    14306----14306F
    14307----1431F
    1432---1432F <-- * group 1432 forms group ending with itself *
    14321---14321F
    14322---14322F
    14323----14326F
    14327----14327F
    14328----144F
    145----146F
    147-----1F
  • 28. Re: number plan issue in pl/sql
    980086 Newbie
    Currently Being Moderated
    I AM SORRY....

    this will be the correct number series--

    1---13F
    14--142F
    143---14301F
    14302---14302F
    14303----14303F
    14304---14305F
    14306----14306F
    14307----1431F
    1432---1432F
    14321---14321F
    14322---14322F
    14323----14326F
    14327----14327F
    14328----144F
    145----146F
    147-----1F
  • 29. Re: number plan issue in pl/sql
    ascheffer Expert
    Currently Being Moderated
    Stil don't see any logic in de end of a group
    1---13F
    14--142F <-- one before next element
    143---14301F <-- next element, why not 14300F
    14302---14302F
    14303----14303F
    14304---14305F
    14306----14306F
    14307----1431F
    1432---1432F <-- last element of group, why not 14321F
    14321---14321F
    14322---14322F
    14323----14326F
    14327----14327F
    14328----144F
    145----146F
    147-----1F

    Edited by: ascheffer on Feb 5, 2013 3:31 PM

Legend

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