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]