1 2 3 Previous Next 32 Replies Latest reply: Jan 23, 2013 11:29 AM by 874273 RSS

    From  and To columns display

    874273
      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
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: From  and To columns display
            Nitesh.
            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
              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.
                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
                  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.
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  just you can using rownum between
                                  • 14. Re: From  and To columns display
                                    941949
                                    for selected column use this query shown below

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