This discussion is archived
1 2 3 Previous Next 32 Replies Latest reply: Jan 23, 2013 9:29 AM by 874273 RSS

From  and To columns display

874273 Newbie
Currently Being Moderated
Hi Masters,


I have written a query for display (from row to to row ). Like this
Select rownum,ename,sal from emp where rowid in 
 (select rowid from emp where rownum<&upto
   minus
  select rowid from emp where rownum<=&start);
But now I got a new requirement. I have a table with 50 columns. I wanted to display 10 column to 25 column data.
I don't want to display 1 to 9 and 26 to 50 column data. Is there any solutiion like above query.


Plese advise...!!

Regards
SA
  • 1. Re: From  and To columns display
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: From  and To columns display
    Nitesh. Explorer
    Currently Being Moderated
    Yes you can try this it will fetch according to your requirements ..

    SELECT a.*
    FROM (SELECT ROWNUM rm, b.*
    FROM emp b) a
    WHERE rm >= :c AND rm <= :d;


    :c and :d are bind variables so you can give inputs as 10 and 25 and you will get those column data's alone ..

    Edited by: Niteshkhush on Jan 23, 2013 10:31 AM
  • 3. Re: From  and To columns display
    jeneesh Guru
    Currently Being Moderated
    There is no meaning for ROWNUM if you are not ordering the result set.

    The output will be random..

    What you are trying to do is a kind of pagination.. Look at the different ways to do pagination
  • 4. Re: From  and To columns display
    Nitesh. Explorer
    Currently Being Moderated
    Hi jigneesh,
    His requirement is just he want to display specific columns from 10-25 so accordingly ROWNUM is required as per my knowledge and I have tested my query too as it will surely fetch based on inputs been given to BIND variables ..
  • 5. Re: From  and To columns display
    jeneesh Guru
    Currently Being Moderated
    Niteshkhush wrote:
    Hi jigneesh,
    His requirement is just he want to display specific columns from 10-25 so accordingly ROWNUM is required as per my knowledge and I have tested my query too as it will surely fetch based on inputs been given to BIND variables ..
    Hi Niteshkhush,

    My response was addressed to the OP, not to you.. :)

    Just giving him a hint that - without ORDER BY , if you use ROWNUM, you cannot expect consistent results..
  • 6. Re: From  and To columns display
    Nitesh. Explorer
    Currently Being Moderated
    Oh fine Jineesh .. Yes order by clause is must for expected desired results .. That one is basic and that i Hope he will understand .. Anyways lets see whether OP's requirements are fulfilled or not :)

    Edited by: Niteshkhush on Jan 23, 2013 10:49 AM Edited as Corrected Name by mistake typed Jigneesh instead of Jineesh..
  • 7. Re: From  and To columns display
    Purvesh K Guru
    Currently Being Moderated
    871270 wrote:
    But now I got a new requirement. I have a table with 50 columns. I wanted to display 10 column to 25 column data.
    I don't want to display 1 to 9 and 26 to 50 column data. Is there any solutiion like above query.
    There isn't a way to achieve using a Static SQL. You will have to resort to Dynamic SQL.

    Something this way: (untested since i do not have 11g at hand)
    create or replace procedure print_dynamic_cols
    (
      p_start_col_no        number,
      p_stop_col_no         number,
      p_ref_cur         out sys_refcursor
    )
    is
      v_sql     varchar2(4000);
      ref_cur   sys_refcursor;
    begin
      select 'select ' || rtrim(trailing ', ' from listagg(column_name, ', ') within group (order by column_id)) || ' from test_table'
        into v_sql
        from user_tab_columns
       where table_name = 'TEST_TABLE'
         and column_id between p_start_col_no and p_stop_col_no
      connect by level <= (p_stop_col_no - p_start_col_no) + 1;
    
      open ref_cur for v_sql;
    end;
    Use the Ref Cursor to access the records. But you will have to know the Datatypes of the Columns, specifically since you are referring the column positions.
  • 8. Re: From  and To columns display
    941949 Explorer
    Currently Being Moderated
    you can use this below query

    SQL>SELECT * FROM (SELECT ROWNUM R,E.* FROM EMP E) WHERE R BETWEEN 5 AND 10;
  • 9. Re: From  and To columns display
    Purvesh K Guru
    Currently Being Moderated
    938946 wrote:
    you can use this below query

    SQL>SELECT * FROM (SELECT ROWNUM R,E.* FROM EMP E) WHERE R BETWEEN 5 AND 10;
    But how does this help OP to fetch dynamically columns ranging from position 10 to 25?
  • 10. Re: From  and To columns display
    jeneesh Guru
    Currently Being Moderated
    Niteshkhush wrote:
    Edited by: Niteshkhush on Jan 23, 2013 10:49 AM Edited as Corrected Name by mistake typed Jigneesh instead of Jineesh..
    Still not correct :(
  • 11. Re: From  and To columns display
    Purvesh K Guru
    Currently Being Moderated
    jeneesh wrote:
    Niteshkhush wrote:
    Edited by: Niteshkhush on Jan 23, 2013 10:49 AM Edited as Corrected Name by mistake typed Jigneesh instead of Jineesh..
    Still not correct :(
    Perhaps, Copy and Paste is broken for the poor chap!!! ;)
  • 12. Re: From  and To columns display
    jeneesh Guru
    Currently Being Moderated
    871270 wrote:
    But now I got a new requirement. I have a table with 50 columns. I wanted to display 10 column to 25 column data.
    I don't want to display 1 to 9 and 26 to 50 column data. Is there any solutiion like above query.

    Hopefully, the order of the column will never change for a table in NORMAL conditions..

    So, you can just type in the column names you wanted to display..

    If you are smart enough, you could just query the data dictionary and generate the SELECT list ( I did not mean dynamic SQL - just do it for once and put it in the select statement statically..)
  • 13. Re: From  and To columns display
    986379 Newbie
    Currently Being Moderated
    just you can using rownum between
  • 14. Re: From  and To columns display
    941949 Explorer
    Currently Being Moderated
    for selected column use this query shown below

    SQL>select col1,col2,col3,.........................from <table_name>
1 2 3 Previous Next

Legend

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