10 Replies Latest reply: Jul 29, 2013 12:08 PM by jgarry RSS

    Help SQL query into oracle query

    aa9c0046-3b1d-4e77-a645-da9b2b162552

      In sql server 2008 i tried below and it s working fine

       

      Select name from test

      with CTE_R as

      (

      select *, ROW_NUMBER() OVER(ORDER BY id) AS RN

      from test

      )

       

      select

          r1.name as A,

          r2.name as B

      from CTE_R as r1

      left join CTE_R as r2

          on r2.RN = r1.RN + 1

      where r1.RN % 2 = 1

       

       

      please someone tell me how to write the above but in oracle query

        • 1. Re: Help SQL query into oracle query
          Manik

          something like this?

           

          Untested!

          ----------------------

          WITH CTE_R AS

                  (SELECT test.*, ROW_NUMBER () OVER (ORDER BY id) AS RN FROM test)

          SELECT r1.name AS A, r2.name AS B

            FROM CTE_R r1 LEFT JOIN CTE_R r2 ON r2.RN = r1.RN + 1

          WHERE MOD (r1.RN, 2) = 1;

          --------------------

          Cheers,

          Manik.

          • 2. Re: Help SQL query into oracle query
            Nikolay Savvinov

            Hi,

             

            1) in Oracle, you can't combine "*" with column names or expressions without specifying the table alias, i.e. in your case * => test.*

            2) lose the empty line between ")" and select -- some clients may not see this as one query because of the empty line

            3) what do you mean by r1.name, r2.name? there is no such column in CTE_R inline view or test table; if that's some kind of SQL server metadata column, you should explain such things to us because you can't expect much SQL Server knowledge on an Oracle forum

            4) what does "%" operator mean in SQL server? if it's integral division, then you can replace it by something like mod(r1.RN, 2) = 1

            5) for the future reference -- it's easier to get help if you specify  your Oracle version number, explain what your query is supposed to return (preferably with some test data) and post all Oracle errors you are facing

             

            Best regards,

            Nikolay

            • 3. Re: Help SQL query into oracle query
              aa9c0046-3b1d-4e77-a645-da9b2b162552

              i tried but it is returning no rows  would you please  double check from your side

               

              below is table creation

              create table test

              (id number(5),
              name varchar2(50))

              please set id column as primary key


              below is my data

              1 1 A1
              2 2 A2
              3 3 B1
              4 4 B2
              5 5 C1
              6 6 C2
              7 7 D1
              8 8 D2
              9 9 E1
              10 10 E2


              pleasehelp and thanks inj advance

              • 4. Re: Help SQL query into oracle query
                aa9c0046-3b1d-4e77-a645-da9b2b162552

                i tried but it is returning no rows  would you please  double check from your side

                 

                below is table creation

                create table test

                (id number(5),
                name varchar2(50))

                please set id column as primary key


                below is my data

                1 1 A1
                2 2 A2
                3 3 B1
                4 4 B2
                5 5 C1
                6 6 C2
                7 7 D1
                8 8 D2
                9 9 E1
                10 10 E2

                • 5. Re: Help SQL query into oracle query
                  aa9c0046-3b1d-4e77-a645-da9b2b162552

                  below is the table creation

                  CREATE TABLE Test
                  ( idname number (5),
                  ename varchar2(50))

                  below is insert data statement

                  insert into test (idname , ename) values (1, 'A1');
                  insert into test (idname , ename) values (2, 'A2');
                  insert into test (idname , ename) values (3, 'B1');
                  insert into test (idname , ename) values (4, 'B2');
                  insert into test (idname , ename) values (5, 'C1');
                  insert into test (idname , ename) values (6, 'C2');
                  insert into test (idname , ename) values (7, 'D1');
                  insert into test (idname , ename) values (8, 'D2');
                  insert into test (idname , ename) values (9, 'E1');
                  insert into test (idname , ename) values (10, 'E2');




                  Below is simple select * from Test
                  idname ename
                  1 1 A1
                  2 2 A2
                  3 3 B1
                  4 4 B2
                  5 5 C1
                  6 6 C2
                  7 7 D1
                  8 8 D2
                  9 9 E1
                  10 10 E2



                  I am ask you friends to help me to write an sql from Test table that will result the below

                  ename1 ename2
                  A1 A2

                  B1 B2

                  C1 C2

                  D1 D2

                  E1 E2

                  • 6. Re: Help SQL query into oracle query
                    Nikolay Savvinov

                    Hi,

                     

                    use analytics (actually you already are, you're just doing it not in the best way), e.g.:

                     

                    select *
                    from
                    (
                        select ename name, lead(ename) over (order by idname) next_name
                        from test
                    )
                    where next_name is not null;
                    

                     

                    Best regards,

                    Nikolay

                    • 7. Re: Help SQL query into oracle query
                      Girish Sharma

                      Manik has given you the correct reply.  See this one :

                         WITH CTE_R AS
                                 (SELECT test.*, ROW_NUMBER () OVER (ORDER BY idname) AS RN FROM test)
                         SELECT r1.ename AS A, r2.ename AS B
                           FROM CTE_R r1 LEFT JOIN CTE_R r2 ON r2.RN = r1.RN + 1
                      WHERE MOD (r1.RN, 2) = 1
                      /
                      
                      A                                                  B
                      -------------------------------------------------- --------------------------------------------------
                      A1                                                 A2
                      B1                                                 B2
                      C1                                                 C2
                      D1                                                 D2
                      E1                                                 E2
                      
                      SQL>

                      Regards Girish Sharma

                      • 8. Re: Help SQL query into oracle query
                        aa9c0046-3b1d-4e77-a645-da9b2b162552

                        please tell me why it is returning no rows when i am trying it in PL/SQL

                        • 9. Re: Help SQL query into oracle query
                          Girish Sharma

                          please tell us why you are getting no rows when I am getting as shown above ?  How and why we should believe upon your statement, when there is no show of run of above query by copy and paste ?

                          • 10. Re: Help SQL query into oracle query
                            jgarry

                            PL stands for something like "Procedural Language."  In general, if you can do something in straight SQL that is to be preferred, for a number of reasons.  Using analytics (like the "over(...) syntax) lets you do that.

                             

                            We can't see what you are doing, so you need to copy and paste to show us.