3 Replies Latest reply: Nov 21, 2012 1:27 PM by rp0428 RSS

    Insert data

    choti
      Please help me to insert data into the following table
      create table xddw.temp_quarter_vj
      (QNum_in_Year varchar2(128)
      ,Year Number
      ,QNum varchar2(128)
      )
      
      Insert data into table from 2006 to 2030
      
      Expected output for 01.01.2006
      QNum_in_Year=012006
      year=2006
      Qnum=01
        • 1. Re: Insert data
          ranit B
          insert into temp_quarter_vj 
          select 
              to_char(to_date('01.01.'||level,'mm.dd.yyyy'),'Q')||level as "Qnum_in_year",
              level "year",
              to_char(to_date('01.01.'||level,'mm.dd.yyyy'),'Q') "Qnum"
          from dual
          where level>=2006
          connect by level <= 2030;
          Edited by: ranit B on Nov 22, 2012 12:54 AM
          • 2. Re: Insert data
            Frank Kulash
            Hi,
            choti wrote:
            Please help me to insert data into the following table
            create table xddw.temp_quarter_vj
            (QNum_in_Year varchar2(128)
            ,Year Number
            ,QNum varchar2(128)
            )
            That's thinking ahead! With 128 characters for qnum_in_quarter, you'll be all set through the year 999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999. But why do you need 128 characters for the quarter? Given that you do need 128 characters for qnum, shouldn't you have at least 132 characters for qnum_in_year?
            If you don't need that many characters, make the maximum sizes smaller. Making the strings to big is just asking for trouble.
            Insert data into table from 2006 to 2030
            
            Expected output for 01.01.2006
            QNum_in_Year=012006
            year=2006
            Qnum=01
            Here's one way:
            INSERT INTO     temp_quarter_vj (qnum_in_year, year, qnum)
            WITH     quarters   AS
            (
                 SELECT     TO_CHAR (LEVEL, 'FM00')     AS qnum
                 FROM     dual
                 CONNECT BY     LEVEL     <= 4
            )
            ,     years        AS
            (
                 SELECT     2005 + LEVEL     AS year
                 FROM     dual
                 CONNECT BY     LEVEL     <= 25
            )
            SELECT     q.qnum || TO_CHAR (y.year)
            ,     y.year
            ,     q.qnum
            FROM          quarters     q
            CROSS JOIN     years          y
            ;
            • 3. Re: Insert data
              rp0428
              >
              create table xddw.temp_quarter_vj
              (QNum_in_Year varchar2(128)
              ,Year Number
              ,QNum varchar2(128)
              )
              >
              Why are you using a table like this that contains redundant data and has numeric data stored as strings?

              Why not just create a view that gives you the information you need in the format you want?