This discussion is archived
1 2 3 Previous Next 32 Replies Latest reply: Jan 23, 2013 9:29 AM by 874273 Go to original post RSS
  • 15. Re: From  and To columns display
    874273 Newbie
    Currently Being Moderated
    not working ...!!! it is displaying all columns...!!

    Regards
    SA
  • 16. Re: From  and To columns display
    874273 Newbie
    Currently Being Moderated
    thanks for your advise...!!! :)
  • 17. Re: From  and To columns display
    941949 Explorer
    Currently Being Moderated
    is your problem solved?
  • 18. Re: From  and To columns display
    874273 Newbie
    Currently Being Moderated
    No boss....!!! I have seen your answer ... first day oracle learner also can do that....!! but i am not expecting that kind of answer. Hope you can't understand my problem. Check once again my question.


    Regards
    SA
  • 19. Re: From  and To columns display
    Purvesh K Guru
    Currently Being Moderated
    The use of ROWNUM, will never ever suppress the Projection of Query.

    Explicit specification of Column Names will do it. All, you have tried is fetching rows between a Lower and Upper bound.

    IMO, You should follow advice of Jeneesh, to put a Static SQL with the number of columns you need to fetch. Since, you did not mention, if you would be getting a Dynamic/Run Time "Request" to project different columns, I am recommending you to follow some solution similar to one I gave, which involves use of Dynamic SQL. If you have a fixed request of column positions, then it willl always be better to use the Static SQL.
  • 20. Re: From  and To columns display
    Nitesh. Explorer
    Currently Being Moderated
    Oh Sorry Jeneesh :) ... I am on the job now so unable to notice 'e' instead written 'i' as while expressing your name in thoughts itself Haaa .. Chill and still OP didnt get solution ah .. Did you tried my query just try that once and add order by clause as per your requirement in my query ..
  • 21. Re: From  and To columns display
    874273 Newbie
    Currently Being Moderated
    Hi Purvesh,

    I am getting error while compiling your script...!!!
    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 ODM_ITEM_PARTS'
        into v_sql
        from user_tab_columns
       where table_name = 'ODM_ITEM_PARTS'      --- give ur tablenamme
         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;
      
      dbms_output.put_line('v_sql' ||v_sql);
      
      open ref_cur for v_sql;
    end;
    Error
    ******
    remote_object_name=>'ODM_ITEM_PARTS');
    END;
    Error at line 1
    ORA-44004: invalid qualified SQL name
    ORA-06512: at "SYS.DBMS_ASSERT", line 188
    ORA-06512: at "SYS.DBMS_COMPARISON", line 372
    ORA-06512: at line 4
    Script Terminated on line 1.
    Please advise....!!!

    Regards
    SA
  • 22. Re: From  and To columns display
    Nitesh. Explorer
    Currently Being Moderated
    Try this and kindly let me know as how its not meeting your requirements dude

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


    Just give bind variables input as a as 10 and for d as 25

    Edited by: Niteshkhush on Jan 23, 2013 12:32 PM

    Edited by: Niteshkhush on Jan 23, 2013 12:32 PM
  • 23. Re: From  and To columns display
    941949 Explorer
    Currently Being Moderated
    SCOTT>SELECT a.ename,a.sal
    2 FROM (SELECT ROWNUM rm, b.*
    3 FROM emp b) a
    4 WHERE rm >= :c AND rm <= :d
    5 order by ename ;
    SP2-0552: Bind variable "D" not declared.
  • 24. Re: From  and To columns display
    Nitesh. Explorer
    Currently Being Moderated
    Which application you are using there .. In SQL Developer and TOAD and sql*plus its supported, as just give CTRL+ENTER on the query so it will prompt you to enter the values for BIND variables and then you can give INPUT values to the Bind variables... Got it ..
  • 25. Re: From  and To columns display
    jeneesh Guru
    Currently Being Moderated
    938946 wrote:
    SCOTT>SELECT a.ename,a.sal
    2 FROM (SELECT ROWNUM rm, b.*
    3 FROM emp b) a
    4 WHERE rm >= :c AND rm <= :d
    5 order by ename ;
    SP2-0552: Bind variable "D" not declared.
    Use this
    SELECT a.ename,a.sal
    FROM (SELECT ROWNUM rm, b.*
    FROM emp b) a
    WHERE rm >= &c AND rm <= &d
    order by ename ; 
  • 26. Re: From  and To columns display
    Nitesh. Explorer
    Currently Being Moderated
    Yes Jeneesh that also same as may be '&' may support in his application ...
  • 27. Re: From  and To columns display
    Purvesh K Guru
    Currently Being Moderated
    I do not have Oracle 11g at hand to test the solution I earlier provided.

    Here is a 10g compliant version of it: Just replace the Select statement with the below and fetch it into the V_SQL variable.
    create table test_table
    (
      col1        number,
      col2        number,
      col3        number,
      col4        number,
      col5        number,
      col6        number,
      col7        number,
      col8        number
    );
    
    select 'select ' || 
           ltrim(max(sys_connect_by_path(column_name, ', ')) keep( dense_rank last order by rn - 1), ', ') || 
           ' from test_table' sel
      from (
            select table_name, column_name,
                   row_number() over (order by column_id) rn
                from user_tab_columns
               where table_name = 'TEST_TABLE'      --- give ur tablenamme
                 and column_id between 2 and 6
           )
     group by table_name
    connect by rn - 1 = prior rn
           and table_name = prior table_name
       start with rn = 1;
    
    SEL
    --------------------------------------------------------------------------
    select COL2, COL3, COL4, COL5, COL6 from test_table
  • 28. Re: From  and To columns display
    Karthick_Arp Guru
    Currently Being Moderated
    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); 
    This is termed as Pagination and this has been in use for a very long time. And your implementation is not the best one available. Please read [url http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html] On Top-N and Pagination Queries By Tom Kyte 
    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 solution like above query.
    Yes its doable. But the question is how you want to do it. Basically this is a presentation requirement and generally better handled in the presentation layer of the application.

    You should provide a broad explanation of why you need a requirement like this to get the optimal solution. What is the purpose?

    Next, when you want to display 10 th to 25 th column of a table how you determine which are the columns that fall in this condition. Because in SQL the order of appearance of column in SELECT list is of user choice. You need to specify it manually.

    So when you have answers for the above questions you can build a Dynamic SQL as already demonstrated.
  • 29. Re: From  and To columns display
    874273 Newbie
    Currently Being Moderated
    Hi Karthik,

    Nice questions...!!! Please find below my answers..!!
    You should provide a broad explanation of why you need a requirement like this to get the optimal solution. What is the purpose? 
    I am working on Oracle Apps, Hope you know apps tables are having huge columns. If i want to check some column values every time I have to write all column names. And you know writing a column lists with comma(',') irritation. and every time I have to see table desc. then remember column names and write the query.
    Next, when you want to display 10 th to 25 th column of a table how you determine which are the columns that fall in this condition. Because in SQL the order of appearance of column in SELECT list is of user choice. You need to specify it manually. 
    yes, columnids are fixed, it won't change in future. As of now I don't have that kind of problem.

    I have seen your words that "yes, it is doable" ... Appreciate your confidence. keep it up.. Thanks in adv.

    Regards
    SA

Legend

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