Urgent Pleeeeeeeeease help mee!!

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.

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;

Quote:

---

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

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.

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

Quote:

---

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

---

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.

Quote:

---

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]