| Forms 6i - dynamic order by problem [message #429654] |
Thu, 05 November 2009 00:56  |
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 #429664 is a reply to message #429662] |
Thu, 05 November 2009 01:48  |
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 #429673 is a reply to message #429668] |
Thu, 05 November 2009 02:48 |
adnanBIH
Messages: 41
Registered: November 2007
Location: BiH, Sarajevo
|
Member |
|
|
Littlefoot wrote on Thu, 05 November 2009 09:27Perhaps 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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
