1 2 3 Previous Next 34 Replies Latest reply: Feb 22, 2013 8:07 AM by 980086 RSS

    number plan issue in pl/sql

    980086
      Hi,

      I have a table containing -

      CODE

      93
      9370
      9372
      9375
      9377
      9378
      9379
      93809

      I need to make this in the following format & load that in another table---

      FROM TO
      93 - 9369F
      9370 - 9370F
      9371 - 9371F
      9372 - 9372F
      9373 - 9374F
      9375 - 9375F
      9376 - 9376F
      9377 - 9379F
      9380 - 93808F
      93809 - 93809F
      93810 - 93F

      Please can Anyone help me out in this logic.....
      please..
        • 1. Re: number plan issue in pl/sql
          Veeresh.S
          hope this gives you some idea
          SQL> create table testn(numt varchar2(10));
          
          Table created.
          
          SQL>  select * from testn;
          
          NUMT
          ----------
          9369
          9370
          9371
          9372
          9373
          93809
          
          6 rows selected.
          
          SQL> create table testn2(numt varchar2(10));
          
          Table created.
          
          SQL> insert into testn2 (select NUMT||'F' from testn);
          
          6 rows created.
          
          SQL>  select * from testn2;
          
          NUMT
          ----------
          9369F
          9370F
          9371F
          9372F
          9373F
          93809F
          
          6 rows selected.
          if there are any conditions other then concatenating character 'F' then use functions/packages...
          • 2. Re: number plan issue in pl/sql
            Etbin
            Maybe NOT TESTED! not sure whether you want the concatenation or not
            select code "from",
                   lead(code,1,code) || 'F') over (order by code) || 'F' "to"
              from your_table
            union all (select to_char(to_number(max(code)) + 1) from your_table),
                      (select min(code) || 'F' from your_table)
            Regards

            Etbin
            • 3. Re: number plan issue in pl/sql
              sudher
              Hi,

              May be.. the output shall not match your entire requirement... But the same could be tried... It will give a small idea.
              with code_table as
              (
              select 93 code from dual union all
              Select 9370 From Dual Union All
              Select 9372 From Dual Union All
              Select 9375 From Dual Union All
              Select 9377 From Dual Union All
              Select 9378 From Dual Union All
              Select 9379 From Dual Union All
              Select 93809 From Dual
              ), 
              B As (Select Code, Rownum Lineno From Code_Table),
              C As (Select * From B, (Select Level Linecol From Dual Connect By Level <= 3))
              Select start_val "To" , to_char(end_val) ||'F'  "From" From
              (
              Select distinct 
                  Case 
                    When Start_Val < End_Val Then Start_Val + 1
                    Else Start_Val
                  end start_val,
                  Case 
                    When Start_Val < End_Val Then End_Val - 1
                    Else End_Val
                  end end_val
              From 
              (
              Select  Case  Linecol
                      When 1 Then Lag(Code, 1) Over (Order By Linecol, Lineno)
                      Else Code
                      End start_val,
                    Case  Linecol
                      When 3 Then lead(Code, 1) Over (Order By Linecol, Lineno)
                      Else Code
                      End End_Val
              From C
              )
              Where Start_Val Is Not Null
              And End_Val Is Not Null
              )
              Where Start_Val <= End_Val
              order by start_val , end_val;
              
              
              Output 
              ----------
              
              From To
              93     93F
              94     9369F
              9370     9370F
              9371     9371F
              9372     9372F
              9373     9374F
              9375     9375F
              9376     9376F
              9377     9377F
              9378     9378F
              9379     9379F
              9380     93808F
              93809     93809F
              Hope the above one helps

              Regards,
              Sudher

              Edited by: sudher on Feb 3, 2013 2:09 AM

              Edited by: sudher on Feb 3, 2013 3:01 AM
              • 4. Re: number plan issue in pl/sql
                980086
                Thanks for your help......
                I would like to inform you that ...the data I have given is just sample....there will be 10k+ rows.....and also 93 atfirst is not fixed.....
                please help me to get the exact format.....
                93F will be atlast.(as given in example)
                • 5. Re: number plan issue in pl/sql
                  stefan nebesnak
                  977083 wrote:
                  I need to make this in the following format & load that in another table---

                  FROM TO
                  93 - 9369F
                  9370 - 9370F
                  9371 - 9371F
                  9372 - 9372F
                  9373 - 9374F
                  9375 - 9375F
                  9376 - 9376F
                  9377 - 9379F
                  9380 - 93808F
                  93809 - 93809F
                  93810 - 93F
                  select * from code_table;
                  .        CODE
                  1     93
                  2     9370
                  3     9371
                  4     9372
                  5     9373
                  6     9375
                  7     9376
                  8     9377
                  9     9380
                  10     93809
                  11     93810
                  -----
                          select tbl2.code,
                                 case
                                   when (abs(tbl2.code - tbl2.nc)) > 1 then
                                    tbl2.nc - 1
                                   else
                                    tbl2.code
                                 end || 'F' final
                            from (with tbl as (select rownum rn, t.code, max(rownum) over() cnt
                                                 from code_table t)
                                   select t1.rn,
                                          t1.code,
                                          t1.cnt,
                                          nvl((select t2.code
                                                from tbl t2
                                               where t2.rn = t1.rn + 1),
                                              (select to_char(t3.code)
                                                 from tbl t3
                                                where t3.rn = 1) + 1) nc
                                     from tbl t1) tbl2;
                   .       CODE     FINAL
                  1     93     9369F
                  2     9370     9370F
                  3     9371     9371F
                  4     9372     9372F
                  5     9373     9374F
                  6     9375     9375F
                  7     9376     9376F
                  8     9377     9379F
                  9     9380     93808F
                  10     93809     93809F
                  11     93810     93F
                  • 6. Re: number plan issue in pl/sql
                    stefan nebesnak
                    Or..
                    select code,
                           case
                             when (abs(code - nvl(lead(code, 1) over(ORDER BY code asc),
                                                  min(code) over(ORDER BY code asc)))) > 1 then
                              nvl(lead(code, 1) over(ORDER BY code asc),
                                  min(code) over(ORDER BY code asc) + 1) - 1
                             else
                              code
                           end || 'F' final
                      from code_table;
                    .        CODE     FINAL
                    1     93     9369F
                    2     9370     9370F
                    3     9371     9371F
                    4     9372     9372F
                    5     9373     9374F
                    6     9375     9375F
                    7     9376     9376F
                    8     9377     9379F
                    9     9380     93808F
                    10     93809     93809F
                    11     93810     93F
                    • 7. Re: number plan issue in pl/sql
                      980086
                      Thanks a lot.....
                      Can I sort the values of the code column like this....
                      suppose code_table contains-
                      CODE
                      --------
                      93
                      9370
                      34589
                      3450
                      9372
                      9375
                      240
                      3496
                      24058
                      9379
                      9381
                      1458
                      93809
                      14

                      sorted table
                      ----------------
                      14
                      1458
                      240
                      24058
                      3450
                      34589
                      3496
                      93
                      9370
                      9372
                      9375
                      9379
                      93809
                      9381

                      in this way...
                      please help..
                      • 8. Re: number plan issue in pl/sql
                        jeneesh
                        If CODE is not NUMBER type and if it has only numeric values, use
                        order by to_number(code)
                        • 9. Re: number plan issue in pl/sql
                          980086
                          Its not working....
                          It is showing like -
                          14
                          93
                          935
                          1408
                          2504......like this.

                          But actually it should be sorted like this-

                          14
                          1408
                          2504
                          93
                          935.....like this.
                          I think it should be done in radix sort .....I am not sure..
                          Please help.

                          Edited by: 977083 on Feb 4, 2013 4:07 AM
                          • 10. Re: number plan issue in pl/sql
                            BluShadow
                            977083 wrote:
                            Its not working....
                            It is showing like -
                            14
                            93
                            935
                            1408
                            2504......like this.

                            But actually it should be sorted like this-

                            14
                            1408
                            2504
                            93
                            935.....like this.
                            I think it should be done in radix sort .....I am not sure..
                            Please help.

                            Edited by: 977083 on Feb 4, 2013 4:07 AM
                            You mean...
                            SQL> ed
                            Wrote file afiedt.buf
                            
                              1  with t as (select 14 as num from dual union all
                              2             select 93 from dual union all
                              3             select 935 from dual union all
                              4             select 1408 from dual union all
                              5             select 2504 from dual)
                              6  select num
                              7  from t
                              8* order by num
                            SQL> /
                            
                                   NUM
                            ----------
                                    14
                                    93
                                   935
                                  1408
                                  2504
                            
                            SQL> ed
                            Wrote file afiedt.buf
                            
                              1  with t as (select 14 as num from dual union all
                              2             select 93 from dual union all
                              3             select 935 from dual union all
                              4             select 1408 from dual union all
                              5             select 2504 from dual)
                              6  select num
                              7  from t
                              8* order by to_char(num)
                            SQL> /
                            
                                   NUM
                            ----------
                                    14
                                  1408
                                  2504
                                    93
                                   935
                            • 11. Re: number plan issue in pl/sql
                              980086
                              The problem is solved of sorting-
                              I have done
                              SELECT * FROM code_table order by to_char(code);
                              its showing the correct one...

                              But
                              CODE

                              93
                              9370
                              9372
                              9375
                              9377
                              9378
                              9379
                              93809

                              I need to make this in the following format & load that in another table---

                              FROM TO
                              93 - 9369F
                              9370 - 9370F
                              9371 - 9371F
                              9372 - 9372F
                              9373 - 9374F
                              9375 - 9375F
                              9376 - 9376F
                              9377 - 9379F
                              9380 - 93808F
                              93809 - 93809F
                              93810 - 93F

                              Here the same format is not coming....
                              From part is taking from "code"....But there will be like
                              9373 - 9374F
                              9376 - 9376F
                              which is not there in code.....
                              Actually I need it in the format I mentioned.
                              • 12. Re: number plan issue in pl/sql
                                ascheffer
                                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
                                )
                                select code
                                     , case code
                                         when 93 then '9369F'
                                         when 9370 then '9370F'
                                         when 9371 then '9371F'
                                         when 9372 then '9372F'
                                         when 9373 then '9374F'
                                         when 9375 then '9375F'
                                         when 9376 then '9376F'
                                         when 9377 then '9379F'
                                         when 9380 then '93808F'
                                         when 93809 then '93809F'
                                         when 93810 then '93F'
                                       end code_to
                                from code_table
                                • 13. Re: number plan issue in pl/sql
                                  ascheffer
                                  Or, if your code column is numeric
                                  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
                                  )
                                  select code
                                       , coalesce( ( lead( code ) over ( order by code ) - 1 )
                                                 , min( code ) over( order by code )
                                                 ) || 'F'
                                  from code_table
                                  • 14. Re: number plan issue in pl/sql
                                    ascheffer
                                    Or if's numeric and you want it sorted by the string value
                                    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 14 From Dual Union All
                                    Select 1408 From Dual Union All
                                    Select 93809 From Dual Union All
                                    Select 93810 From Dual
                                    )
                                    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 )
                                    1 2 3 Previous Next