This discussion is archived
10 Replies Latest reply: Jul 29, 2013 10:08 AM by jgarry RSS

Help SQL query into oracle query

aa9c0046-3b1d-4e77-a645-da9b2b162552 Newbie
Currently Being Moderated

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

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

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

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

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

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

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

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

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

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

    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.

Incoming Links

Legend

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