This discussion is archived
11 Replies Latest reply: Feb 14, 2013 4:54 AM by AlbertoFaenza RSS

query

936056 Newbie
Currently Being Moderated
Hi All,

I have a question as like below.

I have a table as test in that i have 2 columns as like below with the data.

col1 col2
A 2
B 3
C 1

Could you please let me know how can i get the output as like below.

col1 col2
A 2
A 2
B 3
B 3
B 3
C 1

Regards,
Jyothirmai K
  • 1. Re: query
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: query
    jeneesh Guru
    Currently Being Moderated
    Recursion
    with REC_DATA(COL1,COL2,RN) as
    (
      select COL1,COL2,1
      from YOUR_TABLE
      union all
      select COL1,COL2,RN+1
      from REC_DATA
      where RN+1 <= COL2
    )
    select COL1,COL2
    from REC_DATA
    order by 1;
    And MODEL
    select col1,col2
    from your_table
    model
     partition by (col1)
     dimension by (1 rn)
     measures (col2)
     rules upsert all
     iterate(999) until(iteration_number+1 = col2[1])
     (
     col2[iteration_number+1] = col2[1]
     )
    order by 1;
    Edited by: jeneesh on Dec 12, 2012 11:06 AM
  • 3. Re: query
    Ashu_Neo Pro
    Currently Being Moderated
    Try this
    SQL> WITH data1(col1, col2) AS
      2  (
      3  SELECT 'A',2  FROM dual UNION ALL
      4  SELECT 'B',3 FROM dual UNION ALL
      5  SELECT 'C',1 FROM dual
      6  )
      7  SELECT col1,col2 from data1
      8  CONNECT BY level - 1 < col2
      9  AND PRIOR col2 = col2
     10  AND prior SYS_GUID() IS NOT NULL
     11  ORDER BY 1
     12  /
    
    C       COL2
    - ----------
    A          2
    A          2
    B          3
    B          3
    B          3
    C          1
    
    6 rows selected.
    NB:- Format your code of SQL.

    Thanks!
  • 4. Re: query
    936056 Newbie
    Currently Being Moderated
    Hi Ashu,

    If i try with the below query i am getting the output.

    SELECT col1,col2 from data1
    CONNECT BY level - 1 < col2
    AND PRIOR col2 = col2
    AND prior SYS_GUID() IS NOT NULL
    ORDER BY 1;


    if i try with the below query i am getting an error as like ORA-32033: unsupported column aliasing.


    WITH data1(col1, col2) AS
    (
    SELECT 'A',2 FROM dual UNION ALL
    SELECT 'B',3 FROM dual UNION ALL
    SELECT 'C',1 FROM dual
    )
    SELECT col1,col2 from data1
    CONNECT BY level - 1 < col2
    AND PRIOR col2 = col2
    AND prior SYS_GUID() IS NOT NULL
    ORDER BY 1


    Could you please explain this query.


    Thanks for your help.


    Regards,
    Jyothirmai K
  • 5. Re: query
    Ashu_Neo Pro
    Currently Being Moderated
    Op Wrote: 
    f i try with the below query i am getting an error as like ORA-32033: unsupported column aliasing.
    
    /* WITH data1(col1, col2) AS  commented for recheck the query. this type of declaration only works for 11gR2 */
    WITH data1 AS
    (
    SELECT 'A' col1 ,2 col2 FROM dual UNION ALL
    SELECT 'B',3 FROM dual UNION ALL
    SELECT 'C',1 FROM dual
    )
    SELECT col1,col2 from data1
    CONNECT BY level - 1 < col2
    AND PRIOR col2 = col2
    AND prior SYS_GUID() IS NOT NULL
    ORDER BY 1
    /
    Above query will never give you error. I posted the query execution and output. in my previous reply. Show me your execution part with proper explanation.

    I created a temp table to store your sample data and mentioned columns names in bracket by WITH CLAUSE.
    CONNECT BY level - 1 < col2
    AND PRIOR col2 = col2
    AND prior SYS_GUID() IS NOT NULL
    Last 2 conditions have to be used together; As first one keep the pointer on same row till the connect by condition get satisfied and 2nd one for reset leveling.

    NB: I guess, you are using 11g. As sys_guid() is used as a unique identifier has been introduced in 11g. for more, you can google it.. :)

    Thanks!

    Edited by: Ashu_Neo on Dec 12, 2012 2:06 PM
    -- commented
  • 6. Re: query
    jeneesh Guru
    Currently Being Moderated
    Jyothirmai wrote:
    Hi Ashu,

    If i try with the below query i am getting the output.

    SELECT col1,col2 from data1
    CONNECT BY level - 1 < col2
    AND PRIOR col2 = col2
    AND prior SYS_GUID() IS NOT NULL
    ORDER BY 1;


    if i try with the below query i am getting an error as like ORA-32033: unsupported column aliasing.


    WITH data1(col1, col2) AS
    (
    SELECT 'A',2 FROM dual UNION ALL
    SELECT 'B',3 FROM dual UNION ALL
    SELECT 'C',1 FROM dual
    )
    SELECT col1,col2 from data1
    CONNECT BY level - 1 < col2
    AND PRIOR col2 = col2
    AND prior SYS_GUID() IS NOT NULL
    ORDER BY 1


    Could you please explain this query.


    Thanks for your help.


    Regards,
    Jyothirmai K
    Try the bellow, it will work. WITH CLAUSE column aliasing is supported only in the latest versions ..
    WITH data1 AS
    (
    SELECT 'A' col1,2 col2 FROM dual UNION ALL
    SELECT 'B',3 FROM dual UNION ALL
    SELECT 'C',1 FROM dual
    )
    SELECT col1,col2 from data1
    CONNECT BY level - 1 < col2
    AND PRIOR col2 = col2
    AND prior SYS_GUID() IS NOT NULL
    ORDER BY 1
    @Ashu - WITH CLAUSE column aliasing is not supprted in 11gR1, it is available only in 11gR2

    Edited by: jeneesh on Dec 12, 2012 1:55 PM
  • 7. Re: query
    Purvesh K Guru
    Currently Being Moderated
    Ashu_Neo wrote:
    Above query will never give you error. I posted the query execution and output. in my previous reply. Show me your execution part with proper explanation.

    I created a temp table to store your sample data and mentioned columns names in bracket by WITH CLAUSE.
    Not correct.

    It will give you error if executing on Oracle 10.2. It was probably introduced in Oracle 11.1 onwards (Not sure, so if I am wrong please do correct.)

    --Edit:-
    Column aliasing for Subquery factoring introduced in Oracle 11.2. Read here Select Statement for more information


    @OP,
    Remove the Column alias in With clause and apply it to the Select statements to get proper output.

    Edited by: Purvesh K on Dec 12, 2012 1:57 PM
    Added Link.
  • 8. Re: query
    Ashu_Neo Pro
    Currently Being Moderated
    @Ashu - WITH CLAUSE column aliasing is not supprted in 11gR1, it is available only in 11gR2
    Got you.. :)
    Actually, I am on 11g. So not able to check out Op's concern. :(

    Thanks,
    Jeenesh, Purvesh. Will take care of it while replying next time.. :)
  • 9. Re: query
    Purvesh K Guru
    Currently Being Moderated
    Ashu_Neo wrote:
    Jeenesh, Purvesh. Will take care of it while replying next time.. :)
    I wouldn't say your mistake though. It was OP's as (s)he did not provide the Oracle version, so that makes one less trouble for people (although another when OP comes asking that the solution does not work for them) of thinking about the oracle version they are using. ;)
  • 10. Re: query
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Ashu_Neo wrote:
    CONNECT BY level - 1 < col2
    AND PRIOR col2 = col2
    AND prior SYS_GUID() IS NOT NULL
    Last 2 conditions have to be used together; As first one keep the pointer on same row till the connect by condition get satisfied and 2nd one for reset leveling.
    The "keeping row together" condition is wrong?

    It should be "+and prior col1 = col1+". Col2 is not necessarily unique per row as I understand the test data - col1 is the unique identifier with col2 stating the number of elements/rows for that identifier.
  • 11. Re: query
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    its' quite funny that a similar question was posted this morning where I have answered: {thread:id=2498689}

    You can reuse the same code with a small change:
    WITH mydata(col1, col2) AS 
    (  
       SELECT 'A', 2 FROM DUAL UNION ALL
       SELECT 'B', 3 FROM DUAL UNION ALL
       SELECT 'C', 1 FROM DUAL
    )
    , max_times AS
    ( 
       SELECT LEVEL lvl
         FROM DUAL
      CONNECT BY LEVEL <= (SELECT MAX (col2) FROM mydata)
    )
    SELECT a.col1, a.col2
      FROM mydata a, max_times b
     WHERE b.lvl <= a.col2
     ORDER BY a.col1;
    
    COL1       COL2
    ---- ----------
    A             2
    A             2
    B             3
    B             3
    B             3
    C             1
    Regards.
    Al

Legend

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