Home » Developer & Programmer » Forms » How to sort and query on non database column (Oracle forms 10g)
How to sort and query on non database column [message #629122] |
Wed, 03 December 2014 10:30 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/0e34224d536ce6589f54f6b98a3d492f?s=64&d=mm&r=g) |
megha2525
Messages: 62 Registered: June 2012 Location: columbus
|
Member |
|
|
Hi All,
I do have a master detail form .
Master form is based on cards table and detail block is based on transactions table. User enters invoice date and clicks on show records button.
The relevant detail records are displayed on the detail block.
The detail block has a non data base item called transaction_status_description. It is populated based on a db column transaction_status.
My issue is how can I sort and query on the transaction_status_description?
I tried the following code after searching on forums and others.
In the pre-query trigger on the detail block I wrote the below code to query .
IF UPPER(:TRANS.TRANS_STATUS_DESC) = 'APPROVED' THEN
:TRANSACTION_STATUS := 'A';
ELSIF UPPER(:TRANS.TRANS_STATUS_DESC) = 'DISPUTE' THEN
:TRANSACTION_STATUS := 'D';
ELSIF UPPER(:TRANS.TRANS_STATUS_DESC) = 'REDISTRIBUTED TO GL' THEN
:TRANSACTION_STATUS := 'G';
ELSIF UPPER(:TRANS.TRANS_STATUS_DESC) = 'INVALID GL ACCOUNT' THEN
:TRANSACTION_STATUS := 'I';
ELSIF UPPER(:TRANS.TRANS_STATUS_DESC) = 'NEW' THEN
:TRANSACTION_STATUS := 'N';
ELSIF UPPER(:TRANS.TRANS_STATUS_DESC) = 'REVIEWED' THEN
:TRANSACTION_STATUS := 'R';
END IF;
But it is not working. I am still not able to query on transaction_status_description.
Please help.
Thanks
Megha
|
|
|
|
|
Re: How to sort and query on non database column [message #629135 is a reply to message #629132] |
Wed, 03 December 2014 12:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/6f539f43889b4b3e3fcb6c591073de8f?s=64&d=mm&r=g) |
CraigB
Messages: 386 Registered: August 2014 Location: Utah, USA
|
Senior Member |
|
|
Quote:My issue is how can I sort and query on the transaction_status_description?
Looking at your code sample, the TRANSACTION_STATUS code is part of the DETAIL block right and the TRANSACTION_STATUS_DESCRIPTION is just the "Description" of the status code. Therefore, you can perform your filtering and ordering using the TRANSACTION_STATUS field.
For the SORT, just update the ORDER_BY property of the block to sort by a decoded status code using the descriptions. For example, in the ORDER_BY property of your block put the following:
decode(TRANSACTION_STATUS,'A','APPROVED','D','DISPUTE','G','REDISTRIBUTED TO GL'
,'I','INVALID GL ACCOUNT','N','NEW','R','REVIEWED'
,TRANSACTION_STATUS) asc
This will sort the result set by the "Description" in ascending order. You can also set the ORDER_BY clause using the SET_BLOCK_PROPERTY() built-in.
You can use this same method with your query. You'll have to update the DEFAULT_WHERE to decode the TRANSACTION_STATUS and filter using the description you want. For example, if you wanted to query on APPROVED records only use the following:
-- Sample Key-ExeQry trigger
-- Code is not tested...
DECLARE
v_def_where VARCHAR2(2000) := Get_Block_Property('TRANS',DEFAULT_WHERE);
v_new_where VARCHAR2(2000);
BEGIN
v_new_where := 'DECODE(TRANSACTION_STATUS,'A','APPROVED','D','DISPUTE','G','REDISTRIBUTED TO GL'
,'I','INVALID GL ACCOUNT','N','NEW','R','REVIEWED',TRANSACTION_STATUS) = '''APPROVED'''';
Set_Block_Property('TRANS',DEFAULT_WHERE, v_new_where);
Execute_Query;
-- Now reset the DEFAULT_WHERE back to it's original value.
Set_Block_Property('TRANS',DEFAULT_WHERE, v_def_where);
END;
You didn't state your Forms version so the above code does not use the ONETIME_WHERE parameter instead of the DEFAULT_WHERE parameter. If you are using Forms 10g or higher then you don't have to record the current value of the Block's WHERE clause as the ONETIME_WHERE parameter will only affect the WHERE_CLAUSE for that instance of the EXECUTE_QUERY.
Craig...
[Updated on: Wed, 03 December 2014 12:13] Report message to a moderator
|
|
|
|
|
Re: How to sort and query on non database column [message #629143 is a reply to message #629140] |
Wed, 03 December 2014 14:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/0e34224d536ce6589f54f6b98a3d492f?s=64&d=mm&r=g) |
megha2525
Messages: 62 Registered: June 2012 Location: columbus
|
Member |
|
|
Hi Craig,
Thank you for the reply.
I am not able to use the order by property in the transactions block as there is another database column in it.
I have this code in the when button pressed trigger ( when the user clicks on '^' to sort the transaction status description column (non db item) in ascending order)
IF GET_ITEM_PROPERTY('TRANS.TRAN_STATUS_ORDER',LABEL)='^' THEN
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,'TRANS_STATUS_DESC');
SET_ITEM_PROPERTY('TRANS.TRAN_STATUS_ORDER',LABEL,'v');
ELSIF GET_ITEM_PROPERTY('TRAN_STATUS_ORDER',LABEL) = 'v' THEN
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,'TRANS_STATUS_DESC');
SET_ITEM_PROPERTY('TRAN_STATUS_ORDER',LABEL,'^');
END IF;
EXECUTE_QUERY;
Where should I write the decode statement and clear_block?
Please help.
Thanks
Megha.
|
|
|
Re: How to sort and query on non database column [message #629145 is a reply to message #629143] |
Wed, 03 December 2014 15:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/6f539f43889b4b3e3fcb6c591073de8f?s=64&d=mm&r=g) |
CraigB
Messages: 386 Registered: August 2014 Location: Utah, USA
|
Senior Member |
|
|
Quote:Where should I write the decode statement and clear_block?
Well, you want the block to be resorted when your user click the '^' or 'v' button so it makes sense that the code would be added to these/this button.
Looking at your code sample, it appears you are using the a single button and changing the Up or Down arrow character displayed and using this as your determining factor when setting the sort options.
It makes sense to me that you would add your code as follows:
IF GET_ITEM_PROPERTY('TRANS.TRAN_STATUS_ORDER',LABEL)='^' THEN
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,'TRANS_STATUS_DESC');
SET_ITEM_PROPERTY('TRANS.TRAN_STATUS_ORDER',LABEL,'v');
-- Add your decode variable assignment here...
-- Set the Block ORDER_BY property here, using SET_BLOCK_PROPERTY()
ELSIF GET_ITEM_PROPERTY('TRAN_STATUS_ORDER',LABEL) = 'v' THEN
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,'TRANS_STATUS_DESC');
SET_ITEM_PROPERTY('TRAN_STATUS_ORDER',LABEL,'^');
-- Add your decode variable assignment here...
-- Set the Block ORDER_BY property here, using SET_BLOCK_PROPERTY()
END IF;
-- Add your CLEAR_BLOCK(NO_VALIDATE) here.
EXECUTE_QUERY;
Craig...
|
|
|
Re: How to sort and query on non database column [message #629148 is a reply to message #629145] |
Wed, 03 December 2014 18:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/0e34224d536ce6589f54f6b98a3d492f?s=64&d=mm&r=g) |
megha2525
Messages: 62 Registered: June 2012 Location: columbus
|
Member |
|
|
Hi Craig
Thank you for the reply.
declare
v_orderby varchar2(2000);
begin
IF GET_ITEM_PROPERTY('TRANS.TRAN_STATUS_ORDER',LABEL)='^' THEN
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,'TRANS_STATUS_DESC');
SET_ITEM_PROPERTY('TRANS.TRAN_STATUS_ORDER',LABEL,'v');
-- Add your decode variable assignment here...
v_orderby := decode(TRANSACTION_STATUS,'A','APPROVED','D','DISPUTE','G','REDISTRIBUTED TO GL'
,'I','INVALID GL ACCOUNT','N','NEW','R','REVIEWED'
,TRANSACTION_STATUS); -- Is this how I should do ?
-- Set the Block ORDER_BY property here, using SET_BLOCK_PROPERTY()
SET_BLOCK_PROPERTY('TRANS',ORBER_BY, 'TRANS_STATUS_DESC); -- Is this right?
ELSIF GET_ITEM_PROPERTY('TRANS.TRAN_STATUS_ORDER',LABEL) = 'v' THEN
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,'TRANS_STATUS_DESC');
SET_ITEM_PROPERTY('TRAN_STATUS_ORDER',LABEL,'^');
-- Add your decode variable assignment here...
-- Set the Block ORDER_BY property here, using SET_BLOCK_PROPERTY()
END IF;
EXECUTE_QUERY;
CLEAR_BLOCK(NO_VALIDATE);
END;
I am doing some thing wrong as I am not able to compile.
Please let me know.
Thanks
Megha
[EDITED by LF: applied [code] tags]
[Updated on: Thu, 04 December 2014 00:06] by Moderator Report message to a moderator
|
|
|
|
Re: How to sort and query on non database column [message #629164 is a reply to message #629156] |
Thu, 04 December 2014 01:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/0e34224d536ce6589f54f6b98a3d492f?s=64&d=mm&r=g) |
megha2525
Messages: 62 Registered: June 2012 Location: columbus
|
Member |
|
|
Hi Littlefoot,
I tried the following but couldn't make it work.
DECLARE
v_orderby varchar2(2000);
BEGIN
IF GET_ITEM_PROPERTY('TRANS.TRAN_STATUS_ORDER',LABEL)='v' THEN
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,'TRANS_STATUS_DESC');
SET_ITEM_PROPERTY('TRANS.TRAN_STATUS_ORDER',LABEL,'^');
v_orderby := 'SELECT decode(TRANS.TRANSACTION_STATUS,A,APPROVED,D,DISPUTE,G,REDISTRIBUTED TO GL
,I,INVALID GL ACCOUNT,N,NEW,R,REVIEWED
,TRANS.TRANSACTION_STATUS) FROM DUAL';
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,v_orderby);
ELSIF GET_ITEM_PROPERTY('TRANS.TRAN_STATUS_ORDER',LABEL) = '^' THEN
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,'TRANS_STATUS_DESC');
SET_ITEM_PROPERTY('TRAN_STATUS_ORDER',LABEL,'v');
v_orderby := 'SELECT decode(TRANS.TRANSACTION_STATUS,A,APPROVED,D,DISPUTE,G,REDISTRIBUTED TO GL
,I,INVALID GL ACCOUNT,N,NEW,R,REVIEWED
,TRANS.TRANSACTION_STATUS) desc FROM DUAL';
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,v_orderby);
END IF;
CLEAR_BLOCK(NO_VALIDATE);
EXECUTE_QUERY;
END;
I do not know how to assign the decode statement to a variable.
Please help.
Thanks
Megha
[Updated on: Thu, 04 December 2014 01:42] Report message to a moderator
|
|
|
|
Re: How to sort and query on non database column [message #629170 is a reply to message #629165] |
Thu, 04 December 2014 02:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/0e34224d536ce6589f54f6b98a3d492f?s=64&d=mm&r=g) |
megha2525
Messages: 62 Registered: June 2012 Location: columbus
|
Member |
|
|
Hi Littlefoot,
Thank you for the reply.
Please tell me what else am I doing wrong. Here's the updated code. trans_status_desc is a non database column and transaction_status is a database column. Using the code below, how are trans_status_desc and transaction status being linked?
DECLARE
v_orderby varchar2(2000);
BEGIN
IF GET_ITEM_PROPERTY('TRANS.TRAN_STATUS_ORDER',LABEL)='v' THEN
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,'TRANS_STATUS_DESC');
SET_ITEM_PROPERTY('TRANS.TRAN_STATUS_ORDER',LABEL,'^');
v_orderby := 'decode(TRANS.TRANSACTION_STATUS,''A'',''APPROVED'',''D'',''DISPUTE'',''G'',''REDISTRIBUTED TO GL''
,''I'',''INVALID GL ACCOUNT'',''N'',''NEW'',''R'',''REVIEWED''
,TRANS.TRANSACTION_STATUS)';
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,v_orderby);
ELSIF GET_ITEM_PROPERTY('TRANS.TRAN_STATUS_ORDER',LABEL) = '^' THEN
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,'TRANS_STATUS_DESC');
SET_ITEM_PROPERTY('TRAN_STATUS_ORDER',LABEL,'v');
v_orderby := 'decode(TRANS.TRANSACTION_STATUS,''A'',''APPROVED'',''D'',''DISPUTE'',''G'',''REDISTRIBUTED TO GL''
,''I'',''INVALID GL ACCOUNT'',''N'',''NEW'',''R'',''REVIEWED''
,TRANS.TRANSACTION_STATUS) desc';
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,v_orderby);
END IF;
CLEAR_BLOCK(NO_VALIDATE);
EXECUTE_QUERY;
END;
Thanks
Megha
|
|
|
Re: How to sort and query on non database column [message #629176 is a reply to message #629170] |
Thu, 04 December 2014 02:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
These lines are pointless and should be removed:
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,'TRANS_STATUS_DESC');
You can't order by that column and you're setting the order by to something else straight after.
If it's not working then you need to tell us in what why it's not working - are you getting an error?
|
|
|
Re: How to sort and query on non database column [message #629206 is a reply to message #629176] |
Thu, 04 December 2014 07:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/0e34224d536ce6589f54f6b98a3d492f?s=64&d=mm&r=g) |
megha2525
Messages: 62 Registered: June 2012 Location: columbus
|
Member |
|
|
Hi All,
Thanks for all the help.
Here's the updated code that works.
DECLARE
v_orderby varchar2(2000);
BEGIN
IF GET_ITEM_PROPERTY('TRANS.TRAN_STATUS_ORDER',LABEL)='v' THEN
SET_ITEM_PROPERTY('TRANS.TRAN_STATUS_ORDER',LABEL,'^');
v_orderby := 'decode(TRANSACTION_STATUS,''A'',''APPROVED'',''D'',''DISPUTE'',''G'',''REDISTRIBUTED TO GL''
,''I'',''INVALID GL ACCOUNT'',''N'',''NEW'',''R'',''REVIEWED''
,TRANSACTION_STATUS)';
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,v_orderby);
ELSIF GET_ITEM_PROPERTY('TRANS.TRAN_STATUS_ORDER',LABEL) = '^' THEN
SET_ITEM_PROPERTY('TRAN_STATUS_ORDER',LABEL,'v');
v_orderby := 'decode(TRANSACTION_STATUS,''A'',''APPROVED'',''D'',''DISPUTE'',''G'',''REDISTRIBUTED TO GL''
,''I'',''INVALID GL ACCOUNT'',''N'',''NEW'',''R'',''REVIEWED''
,TRANSACTION_STATUS) desc';
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,v_orderby);
END IF;
CLEAR_BLOCK(NO_VALIDATE);
EXECUTE_QUERY;
END;
Thanks
Megha
|
|
|
Goto Forum:
Current Time: Mon Sep 09 08:08:57 CDT 2024
|