DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Urgent Pleeeeeeeeease help mee!!

  1. #1
    Join Date
    Jun 2001
    Posts
    109

    Unhappy

    How do u sort the column in a multi record block either in ascending or descending order?

    I used the following code to do that but didn't help...:-( :-(

    begin
    if get_block_property('block_name',order) = 'column_name ASC' then
    set_block_property('block_name',default_where,'column_name' DESC');
    else
    set_block_property('block_name',order by, 'column_anmeASC');
    end if;
    end;


    In runtime if the user clicks a button it should sort whole column in the oppsite order in which it is present i.e if the column is present in ascending order then should display it in descending order vice versa.

    Can anybody please help me, its urgent.

  2. #2
    Join Date
    Aug 2000
    Posts
    462
    PLEASE don't create mulitple threads for the exact same question.

    What I told you before was correct. You can ONLY change the order by setting the order_by clause, they requerying. If that means you have to requery the master table in the master/detail relationship, then do that.

    if get_block_property('DETAIL_BLOCK' . . . then
    set_block_property('DETAIL_BLOCK' . . .
    . . . .
    end if;
    go_block('MASTER_BLOCK');
    execute_query;
    Oracle DBA and Developer

  3. #3
    Join Date
    Jun 2001
    Posts
    109
    Originally posted by kmesser
    PLEASE don't create mulitple threads for the exact same question.

    What I told you before was correct. You can ONLY change the order by setting the order_by clause, they requerying. If that means you have to requery the master table in the master/detail relationship, then do that.

    if get_block_property('DETAIL_BLOCK' . . . then
    set_block_property('DETAIL_BLOCK' . . .
    . . . .
    end if;
    go_block('MASTER_BLOCK');
    execute_query;


    I am sorry, it didn't work......
    Actually the 1st bock is a control block where the user enters the criteria to query the database. After entering the criteria when he presses the button it takes to the 2nd block which is base table block and fetches the records according to the critria he entered. After the records are fetched into the 2nd block, when the user clicks the button then it should sort the records.
    this is what I wrote I know its not the logic....

    begin


    if (get_block_property('ctrblk', order_by) = 'csm_caseno asc') then
    go_block('ctrblk');
    execute_query;
    set_block_property('casemain', order_by,'csm_Caseno desc');
    elsif (get_block_property('ctrblk', order_by) = 'csm_caseno desc') then
    go_block('ctrblk');
    execute_query;
    set_block_property('casemain', order_by,'csm_Caseno asc');
    end if ;

    can anyone help me....:-(

    thanks
    saritha

  4. #4
    Join Date
    Aug 2000
    Posts
    462
    The answer I provided will work. The sequence in which you execute commands makes a difference.

    Here's the pseudocode:

    1 - check the value of ORDER_BY in the block to sort
    2 - set it to the opposite order_by
    3 - go_block to the block to be ordered, or the parent block if the block to be ordered is a child block.
    4 - execute_query;

    email your form (just the .FMB) to oracle_dev2000@hotmail.com if this doesn't work, and I'll fix it for you.

    Oracle DBA and Developer

  5. #5
    Join Date
    May 2001
    Posts
    11

    Cool hi

    hi saritha,

    after setting the block_property u have to requery
    i.e. after button is pressed use execute_query built in.

    otherwise the records previously fetched remains static,when
    u requery then the new records will be displayed according
    to the block property which was set accordingly
    try this one


    begin
    if get_block_property('block_name',order) = 'column_name ASC' then
    set_block_property('block_name',default_where,'column_name' DESC');
    EXECUTE_QUERY(NO_VALIDATE);
    else
    set_block_property('block_name',order by, 'column_anmeASC');
    EXECUTE_QUERY(NO_VALIDATE);
    end if;
    end;


    bye
    sarath

  6. #6
    Join Date
    Jun 2001
    Posts
    109

    Unhappy Re: hi

    Originally posted by sarath
    hi saritha,

    after setting the block_property u have to requery
    i.e. after button is pressed use execute_query built in.

    otherwise the records previously fetched remains static,when
    u requery then the new records will be displayed according
    to the block property which was set accordingly
    try this one


    begin
    if get_block_property('block_name',order) = 'column_name ASC' then
    set_block_property('block_name',default_where,'column_name' DESC');
    EXECUTE_QUERY(NO_VALIDATE);
    else
    set_block_property('block_name',order by, 'column_anmeASC');
    EXECUTE_QUERY(NO_VALIDATE);
    end if;
    end;


    Thanks!! I can't use execute query because there is no master detail relation between the 2 blocks. 1st block is control block where the user enters the criteria for his search ,when the button is clicked it displays thousands of records in the next block which gets data (fields) from 2 different tables which have relation.I wrote cursor to fetch the records (selected fields,not all fields)from 2 different tables.
    In the backend (default)it displays records in the ascending order ,so if I set during design time the order by clause for the block property to "csm_Caseno desc" this should display me in the descending order right? But it doesn't do that ,its displaying in the same ascending order in which it is present in the backend.
    How do I set the "Orderby" property or
    " default_where" property for a block during design time & run time?
    I appreciate your help.
    Thanks
    saritha.



    bye
    sarath

  7. #7
    Join Date
    Aug 2000
    Posts
    462
    We have both given you the same answer: you MUST requery to alter the sort order.

    You should remove the order_by from your underlying data source, and put it in the ORDER_BY in your data block you wish to order.
    Oracle DBA and Developer

  8. #8
    Join Date
    Jun 2001
    Posts
    109
    Originally posted by kmesser
    We have both given you the same answer: you MUST requery to alter the sort order.

    You should remove the order_by from your underlying data source, and put it in the ORDER_BY in your data block you wish to order.
    Hello kmesser ,
    I really appriciate your help. Here is my code. I sent the .fmb file also.

    1st Method.....I opened 2 cursors one for asc & other for desc and opened the corresponding cursors by checking the block property "ORDER BY"....Actually it should refresh the query which is already present and replace with the cursor's query....If there anything to refresh the query in FORMS...I can't use CLEAR_BLOCK, if I use it , it just clears the block.....
    I will give a simple example how it should work, open the windows NY TASK MANAGER, click on the PROCESSES....then u see image name, memory usage etc.....if u click on the memory usage then it sorts that memory usage column ....it is just like that what my application should, it shouldn't clear the block which already had data , it should only refresh the old query with new query.....I am sorry to confuse u...


    declare
    st_number case_address.csm_st_nmbr%type;
    st_name case_address.csm_st_name%type;
    direction case_address.csm_st_pfx%type;
    description casemain.csm_description%type;
    cno casemain.csm_caseno%type;
    orderby varchar2(30);

    cursor c1 is select a.csm_st_name, a.csm_st_nmbr,a.csm_St_pfx,
    m.csm_description, m.csm_caseno from casemain m,case_address a
    where m.csm_Caseno like ltrim( :ctrblk.case_number||'%')
    and m.csm_caseno = a.csm_caseno
    and m.csm_caseno like :ctrblk.case_number ||'%'
    and a.csm_caseno like :ctrblk.case_number ||'%'
    order by m.csm_Caseno desc;

    cursor c2 is select a.csm_st_name, a.csm_st_nmbr,a.csm_St_pfx,
    m.csm_description, m.csm_caseno from casemain m,case_address a
    where m.csm_Caseno like ltrim( :ctrblk.case_number||'%')
    and m.csm_caseno = a.csm_caseno
    and m.csm_caseno like :ctrblk.case_number ||'%'
    and a.csm_caseno like :ctrblk.case_number ||'%'
    order by m.csm_Caseno asc;


    begin
    --redisplay;
    orderby:=get_block_property('casemain',order_by);
    --message(orderby);message(' ');
    if (orderby= 'casemain.csm_Caseno ASC') then
    open c1;
    loop
    fetch c1 into st_name,st_number,direction, description,cno;
    :casemain.address:= st_number||st_name||direction;
    :casemain.csm_caseno := cno;
    :casemain.csm_description := description;
    end loop;
    close c1;
    elsif (order_by = 'casemain.csm_caseno desc') then
    open c2;
    loop
    fetch c2 into st_name,st_number,direction, description,cno;
    :casemain.address:= st_number||st_name||direction;
    :casemain.csm_caseno := cno;
    :casemain.csm_description := description;
    end loop;
    close c2;
    end if;

    exception
    when no_data_found then
    message(' ');
    when others then
    message(' ');

    end;

    2nd WAY....In this methos I used SET/GET BLOCK PROPERTY BUILTINS....

    /*begin
    --set_item_property('casemain.csm_Caseno',current_row_background_color, 'black');
    -- set_item_property('casemain.csm_Caseno',current_row_foreground_color, 'white');
    if (get_block_property('casemain', order_by) =:casemain.csm_Caseno ) then

    set_block_property('casemain', order_by ,'casemain.csm_Caseno = ctrblk.csm_caseno');
    --set_block_property('casemain', order_by,:casemain.csm_Caseno);
    execute_query;
    elsif (get_block_property('ctrblk', order_by) = :casemain.csm_caseno) then

    set_block_property('casemain', default_where ,'casemain.csm_Caseno = ctrblk.csm_caseno');
    set_block_property('casemain', order_by,:casemain.csm_caseno);
    execute_query;
    end if ;
    exception
    when others then
    raise form_trigger_failure;
    end;*/

    hello all,
    The Orderby.fmb file didn't work. It just clear the block thats it it doesn't get the records. the excute query works with only a database block, it doesn't take into cosideration the value in control block.
    Please help with this Order by clause.

    thanks
    saritha.

    [Edited by saritha on 06-18-2001 at 09:02 AM]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width