Home » Developer & Programmer » Forms » order by in query mode (10g, xp, dev 6i)
order by in query mode [message #349114] Thu, 18 September 2008 23:32 Go to next message
rzkhan
Messages: 370
Registered: March 2005
Senior Member
Dear All

I have the following code which applies order by for a block.
if :rg=1 then
	
go_block('all_students');
set_block_property('all_students',order_by,'rn');  
execute_query;

elsif	:rg=2 then
go_block('all_students');
set_block_property('all_students',order_by,'name');
execute_query;


else
	null;
end if;
	


when the form is in query mode and I press any radio button, The form shows all records (orderd by rn or name) and not the specific records I have queried. Please suggest a solution.


Thanks
Riaz
Re: order by in query mode [message #349195 is a reply to message #349114] Fri, 19 September 2008 04:43 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
How are you specifying which records the form should query?
Are you using a default where clause on the block or something else?
Re: order by in query mode [message #349206 is a reply to message #349114] Fri, 19 September 2008 05:57 Go to previous messageGo to next message
rzkhan
Messages: 370
Registered: March 2005
Senior Member
I Press F7 and F8 for querying records.

Re: order by in query mode [message #349216 is a reply to message #349114] Fri, 19 September 2008 06:16 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
That doesn't entirely answer my question but I think I can hazard a guess as to what's happening.

You're going into enter-query mode (F7).
Entering some values into items on the datablock to restrict the query.
Executing the query (F8) and only getting back the records that match what you entered.
You're then changing the value of the radio button which causes the form to execute query on the block and you're getting all rows back,
instead of just the ones you had before.

Does that sound right?

And do you have:
a default where clause on the block?
a pre-query trigger on the block?

Answer those and we should be able to sort out your problem
Re: order by in query mode [message #349221 is a reply to message #349114] Fri, 19 September 2008 06:29 Go to previous messageGo to next message
rzkhan
Messages: 370
Registered: March 2005
Senior Member
Thanks for your response.

My scenario is exactly the same as you have guessed.

furthermore

1. I dont have a default_Where clause in my form.
2. There is no pre-query trigger associated with the block.

Re: order by in query mode [message #349232 is a reply to message #349114] Fri, 19 September 2008 07:14 Go to previous message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
Right then.

What you're looking at is standard forms behaviour.
Values entered in enter-query mode are only good for the first execute-query after they're entered.
They are lost after that, which is why you're getting all rows back when you change the radio group and re-execute the query.

So what to do?
Two work arounds spring to mind.

1. Create a control block.
This will be a non-database datablock that will contain items that correspond to each item in your main data-block.
Restrict the query in the main datablock using the items in the control block with either a pre-query trigger or the datablock where clause.
You can then enter your query criteria in the control block and the values will not be lost when you execute a query.
Your radio button code should then work just fine.

2. Use get_block_property(<block_name>, last_query).
This is bit more fiddly than the previous solution but the end result looks neater.
This function returns a text string containing the last query the datablock executed agains the database.
What you need to do is strip the where cluase out of that string and then reapply it to the datablock using set_block_property(default_where).
This can all be done in your existing code on the radio group which will end up looking something like this:

DECLARE

l_old_query         VARCHAR2(1000);
l_new_where_clause  VARCHAR2(1000);

BEGIN

IF :rg=1 THEN
  
  go_block('all_students');
  set_block_property('all_students',order_by,'rn');
  l_old_query := get_block_property('all_students', last_query);
  l_new_where_clause := <some code to parse out the where clause>
  set_block_property('all_students', default_where_clause, l_new_where_clause);
  execute_query;
  set_block_property('all_students', default_where_clause, '');

ELSIF :rg=2 THEN

  <code as above>

ELSE

  NULL;

END IF;


You'll have to work out the code to parse out the where clause yourself but it's not difficult - you just need some substrs and instrs.
Previous Topic: FRM 40505 - unable o perform query (FROM QUERY CLAUSE)
Next Topic: ole
Goto Forum:
  


Current Time: Mon Feb 17 19:08:42 CST 2025