1 2 Previous Next 17 Replies Latest reply: Jun 21, 2013 1:10 PM by JustinCave RSS

    dynamic rows

    Oracle Maniac

      Hi All ,

       

      {code}

      create table src

      (

      id number,

      val number,

      data varchar2(100)

      )

       

       

      insert into src values (1,1,'SUN');

      insert into src values (2,2,'WED');

      insert into src values (3,3,'MON');

       

       

       

       

       

      create table trg

      (

      id number,

      val number,

      data varchar2(100)

      )

       

      required rows to be inserted in the target table.

       

       

       

      insert into trg values (1,1,'SUNDAY');

      insert into trg values (2,0,NULL);

      insert into trg values (2,0,NULL);

      insert into trg values (3,0,NULL);

      insert into trg values (3,0,NULL);

      insert into trg values (3,0,NULL);

       

       

      {code}

       

      based on the column value  of the source table src's column val , i need to populate my target table trg . If the value of val is 1 then  only one target row is created in the target .If the value of val in the source table src is 2 then the target is populated with 2 rows .The values of the target columns are mapped as follow:

      1)id -as it is

      2)val - if the val of src is 1 then map the val as it is .If the value of val is more than one then create as many rows as the value of val ,id will be as it is and the  value of val and data will be null

      3)data - if the val of src is 1 then expand the abbreviation else null .

       

       

      Thanks

      Rahul

        • 1. Re: dynamic rows
          Solomon Yakobson

          insert

            into trg

            select  id,

                    case val

                      when 1 then 1

                      else 0

                    end,

                    case val

                      when 1 then data

                    end

              from  src,

                    xmltable(

                             '1 to xs:integer(.)'

                             passing val

                            )

          /


          6 rows created.

          SQL> select  *
            2    from  trg
            3  /

                  ID        VAL DATA
          ---------- ---------- ------
                   1          1 SUN
                   2          0
                   2          0
                   3          0
                   3          0
                   3          0

          6 rows selected.

          SQL>

           

          SY.

          • 2. Re: dynamic rows
            pollywog
            WITH t
                 AS (SELECT *
                       FROM src
                     MODEL
                        PARTITION BY (val)
                        DIMENSION BY (0 d)
                        MEASURES (id, val - 1 i, 0 rn, data)
                        RULES
                           ITERATE (100) UNTIL ITERATION_NUMBER = i[0]
                           (id [ITERATION_NUMBER] = id[0],
                           rn [ITERATION_NUMBER] = ITERATION_NUMBER + 1))
              SELECT id,
                     CASE val WHEN 1 THEN 1 ELSE 0 END val,
                     CASE val WHEN 1 THEN data END data
                FROM t
            ORDER BY id
            
            IDVALDATA
            1
            1
            SUN
            2
            0
            2
            0
            3
            0
            3
            0
            3
            0
            • 3. Re: dynamic rows
              myOra_help

              HI Rahul,

               

              You can use below..

               

              SELECT t.id,case t.id when 1 then t.val else 0 end "VAL", case t.id when 1 then decode(t.data,'SUN','SUNDAY','MON','MONDAY','TUE','TUESDAY','WED','WEDNESDAY','THUS','THUSDAY','FRI','FRIDAY','SAT','SATURDAY')
              else null end "DATA"
              FROM src t,(select level rn
              from dual
              connect by level <= (select max(id) from src)) t1
              where t1.rn <= t.id
              order by 1,2 3;

               

              Output

              --------

              ID VAL DATA

              1 1      SUNDAY

              2 0

              2 0

              3 0

              3 0

              3 0

               

              • 4. Re: dynamic rows
                Solomon Yakobson

                I missed you want to expand day abbreviation:

                 

                insert

                  into trg

                  select  id,

                          case val

                            when 1 then 1

                            else 0

                          end,

                          case val

                            when 1 then to_char(next_day(sysdate,data),'FMDAY')

                          end

                    from  src,

                          xmltable(

                                   '1 to xs:integer(.)'

                                   passing val

                                  )

                /


                6 rows created.

                SQL> select  *
                  2    from  trg
                  3  /

                        ID        VAL DATA
                ---------- ---------- ----------------
                         1          1 SUNDAY
                         2          0
                         2          0
                         3          0
                         3          0
                         3          0

                6 rows selected.

                SQL>

                 

                SY.

                • 5. Re: dynamic rows
                  Oracle Maniac

                  Awesome Folks , is pivoting a way of doing this ?

                  • 6. Re: dynamic rows
                    Oracle Maniac

                    Solomon ,

                     

                    I get the below error

                     

                    ORA-19226: XPTY0006 - XQuery dynamic type mismatch:  expected xs:integer got empty sequence  . Does comma represents a JOIN ?

                    • 7. Re: dynamic rows
                      myOra_help


                      Pivoting is something when we convert rows to columns or vice varsa. Here rows are already present and just need to display number of times as per column value.

                      • 8. Re: dynamic rows
                        Oracle Maniac

                        Sorry ,but the id was just for example. Here my id number is more of an alphanumeric text , like vehicle identification number

                        • 9. Re: dynamic rows
                          Solomon Yakobson

                          What is your Oracle version?

                           

                          SY.

                          • 10. Re: dynamic rows
                            Solomon Yakobson

                            RahulK wrote:

                             

                            get the below error

                             

                            ORA-19226: XPTY0006 - XQuery dynamic type mismatch:  expected xs:integer got empty sequence  . Does comma represents a JOIN ?

                             

                            This means val is NULL on some rows. And you didn't set rules how to handle NULLs. You could either exclude nulls by adding WHERE val IS NOT NULL., or do something like:

                             

                            SQL> update src
                              2  set val = null
                              3  where id = 2
                              4  /

                            1 row updated.

                            SQL> select  id,
                              2          case val
                              3            when 1 then 1
                              4            else 0
                              5          end,
                              6          case val
                              7            when 1 then to_char(next_day(sysdate,data),'FMDAY')
                              8          end
                              9    from  src,
                            10          xmltable(
                            11                   '1 to xs:integer(.)'
                            12                   passing val
                            13                  )
                            14  /
                            ERROR:
                            ORA-19226: XPTY0006 - XQuery dynamic type mismatch:  expected xs:integer got empty sequence

                             

                            no rows selected

                            SQL> select  id,
                              2          case val
                              3            when 1 then 1
                              4            else 0
                              5          end,
                              6          case val
                              7            when 1 then to_char(next_day(sysdate,data),'FMDAY')
                              8          end
                              9    from  src,
                            10          xmltable(
                            11                   '1 to xs:integer(.)'
                            12                   passing nvl(val,1)
                            13                  )
                            14  /

                                    ID CASEVALWHEN1THEN1ELSE0END CASEVALWH
                            ---------- ------------------------- ---------
                                     1                         1 SUNDAY
                                     2                         0
                                     3                         0
                                     3                         0
                                     3                         0

                            SQL>

                             

                            SY.

                            • 11. Re: dynamic rows
                              Rahul_India

                              Hi why have you used connect by in from query.

                              I mean whatis what situations we can use

                               

                              select level from dual conect by

                               

                              in the from query.

                              i have seen it being  used in many places so thats why asking

                              • 12. Re: dynamic rows
                                Frank Kulash

                                Hi,

                                 

                                SELECT  LEVEL  AS n

                                FROM    dual

                                CONNECT BY  LEVEL <= x

                                ;

                                 

                                Is an efficient way to generate a Counter Table , that is, a table (or, more commonly, a result set) that counts 1, 2, 3, ..., x.  In many situations, joining to a counter table is a way of getting the same results in SQL that you would get by using a loop in procedural language.

                                If the table in the query does not have exactly 1 row, then using CONNECT BY to generate a counter table can be tricky.  Solomon showed a different way of generating a counter table:

                                 

                                SELECT  column_value

                                FROM    XMLTABLE ( '1 to xs.integer (.)

                                                   PASSING x

                                                 )

                                ;

                                 

                                to avoid that complication.

                                • 13. Re: dynamic rows
                                  Rahul_India

                                  Thanks Frank  for clearing some of my doubts .Can you show me a very basic example where i can use connect by in from clause.

                                   

                                   

                                  Do you mean that for  generating counter table the XMLTABLE method is the best way?

                                  • 14. Re: dynamic rows
                                    Frank Kulash

                                    Hi,

                                    Rahul_India wrote:

                                     

                                    Thanks Frank  for clearing some of my doubts .Can you show me a very basic example where i can use connect by in from clause.

                                    No, that's impossible. CONNECT BY is a clause, which comes after the FROM clause.  You can't have one inside the other.

                                    Here's a basic example of how you can use CONNECT BY in a counter table.  The main query below looks for employees in the scott.emp table whose anniversary will occur in the next 90 days.  That is, if run on June 21, it will find employees whose hiredate  is between June 22 and September 19, regardless of the year: The counter table just generates the 90 days wanted.

                                     

                                    WITH next_90_days  AS

                                    (

                                        SELECT  LEVEL          AS n

                                        ,       TO_CHAR ( SYSDATE + LEVEL

                                                       , 'DD-MON'

                                                       )      AS str

                                        FROM    dual

                                        CONNECT BY  LEVEL <= 90

                                    )

                                    SELECT    e.empno, e.ename, e.hiredate

                                    FROM      scott.emp     e

                                    JOIN      next_90_days  c  ON  c.str = TO_CHAR ( hiredate

                                                                                   , 'DD-MON'

                                                                                   )

                                    ORDER BY  n

                                    ;

                                     

                                     

                                     

                                    Do you mean that for  generating counter table the XMLTABLE method is the best way?


                                     

                                    No, I would still use CONNECT BY in the situation above, where we need the next 90 days, and nothing in the counter table depends on anything in scott.emp.

                                    1 2 Previous Next