Home » Developer & Programmer » Forms » Forms 6i - dynamic order by problem
Forms 6i - dynamic order by problem [message #429654] Thu, 05 November 2009 00:56 Go to next message
adnanBIH
Messages: 41
Registered: November 2007
Location: BiH, Sarajevo
Member
Hello everybody. How do I overcome next problem ?
I created procedure:
PROCEDURE GetAnalit_From_Old_IO IS
CURSOR Fill IS
SELECT distinct col1, col2
FROM table a
WHERE col1>100
ORDER BY decode(:GLOBAL.order,null,col1,:GLOBAL.order) desc;

and defined global variable on when-new-form-instance trigger
:GLOBAL.order :='col3';

For some reason, this doesn't work - when call procedure it doesn't ordering fetched data. I also tried ORDER BY decode(to_char(:GLOBAL.order),null,col1,to_char(:GLOBAL.order)), but it seems that's illegal to use to_char in this context.
Please help.
Thanks in advance.
Re: Forms 6i - dynamic order by problem [message #429662 is a reply to message #429654] Thu, 05 November 2009 01:39 Go to previous messageGo to next message
viral_dhruv
Messages: 1
Registered: August 2006
Location: Hyderabad
Junior Member

Hi,

Please confirm what exactly are you trying to achieve here. You can anyway sort based on one column at a time. You may use the second column for ordering within the first ordered values.

Thanks,
Viral Dhruv
Re: Forms 6i - dynamic order by problem [message #429664 is a reply to message #429662] Thu, 05 November 2009 01:48 Go to previous messageGo to next message
adnanBIH
Messages: 41
Registered: November 2007
Location: BiH, Sarajevo
Member
I tried to create procedure for dynamically sorting of data on the block, so users could sort data by more criteria.
For example, i created when-button-pressed triger,and define value for :GLOBAL.order (ex. :GLOBAL.order=emp_id). So, when press button, i dynamically sort data on block.
On another button i create trigger when asign :GLOBAL.order=dept, when by pressing button have another order of data, etc...
Re: Forms 6i - dynamic order by problem [message #429668 is a reply to message #429664] Thu, 05 November 2009 02:27 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you could set sorting order by SET_BLOCK_PROPERTY built-in, using its ORDER_BY property.
Re: Forms 6i - dynamic order by problem [message #429673 is a reply to message #429668] Thu, 05 November 2009 02:48 Go to previous messageGo to next message
adnanBIH
Messages: 41
Registered: November 2007
Location: BiH, Sarajevo
Member
Littlefoot wrote on Thu, 05 November 2009 09:27
Perhaps you could set sorting order by SET_BLOCK_PROPERTY built-in, using its ORDER_BY property.

Littlefoot, datablock is populated via procedure, bucause it's not based on view or table. In the meantime, i rewrote procedure:
PROCEDURE GetAnalit_From_Old_IO (col_order in varchar2) IS
CURSOR Fill IS
SELECT distinct col1, col2
FROM table a
WHERE col1>100
ORDER BY decode(col_order,null,col3,'col1',col1) desc;

but it works only if parameter value is NULL.
Re: Forms 6i - dynamic order by problem [message #429676 is a reply to message #429654] Thu, 05 November 2009 03:20 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Dear,

It's absolutely true. It works only when the parameter is null.

Quote:
I created procedure:
PROCEDURE GetAnalit_From_Old_IO IS
CURSOR Fill IS
SELECT distinct col1, col2
FROM table a
WHERE col1>100
ORDER BY decode(:GLOBAL.order,null,col1,:GLOBAL.order) desc;


and defined global variable on when-new-form-instance trigger
:GLOBAL.order :='col3';


The "order by" clause should contain columns that you select.
In your case, you are selecting col1 and col2, but you are ordering by col3, which is logically invalid.
So you may need to assign a value to the global parameter which exists in the select statement.

Rajy
Re: Forms 6i - dynamic order by problem [message #429678 is a reply to message #429654] Thu, 05 November 2009 03:24 Go to previous message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Sorry for my previous reply. I was talking about the "order by", but totally thinking about the "group by".

Rajy
Previous Topic: Maximum no of rows in a list block
Next Topic: Parameter Problem`
Goto Forum:
  


Current Time: Wed Dec 07 04:39:31 CST 2016

Total time taken to generate the page: 0.06692 seconds