This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Jun 21, 2013 11:10 AM by Justin Cave RSS

dynamic rows

Oracle Maniac Explorer
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    Awesome Folks , is pivoting a way of doing this ?

  • 6. Re: dynamic rows
    Oracle Maniac Explorer
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated


    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 Explorer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    What is your Oracle version?

     

    SY.

  • 10. Re: dynamic rows
    Solomon Yakobson Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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

Legend

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