11 Replies Latest reply: Feb 14, 2013 6:54 AM by AlbertoFaenza RSS

    query

    936056
      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
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: query
            jeneesh
            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
              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
                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
                  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
                    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
                      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
                        @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
                          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
                            Billy~Verreynne
                            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
                              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