1 2 3 Previous Next 34 Replies Latest reply: Feb 22, 2013 8:07 AM by 980086 Go to original post RSS
      • 15. Re: number plan issue in pl/sql
        980086
        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
          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
            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
              ????
              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
                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
                  And what's the logic for this, for instance what happens to code 93726 ?
                  • 21. Re: number plan issue in pl/sql
                    980086
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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