This discussion is archived
1 2 3 Previous Next 34 Replies Latest reply: Feb 22, 2013 6:07 AM by 980086 RSS

number plan issue in pl/sql

980086 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

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