OraFAQ Forum: Forms » How to sort and query on non database column

Home » Developer & Programmer » Forms » How to sort and query on non database column (Oracle forms 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to sort and query on non database column [message #629122]

Wed, 03 December 2014 10:30

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

Report message to a moderator

Re: How to sort and query on non database column [message #629127 is a reply to message #629122]

Wed, 03 December 2014 10:58

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

The first step is to establish a working master-detail relationship. Did you do that? Wizard creates it successfully, so - if you didn't follow it, do it now. Doing so, when you query master data block, its details will automatically appear in the detail block. It also means that you don't have to do anything manually (i.e. your PRE-QUERY attempt shouldn't be done at all).

Such a PRE-QUERY trigger or SET_BLOCK_PROPERTY with its ONETIME_WHERE or DEFAULT_WHERE clause are usually used when there's a "fake" master-detail relationship so you have to simulate what Forms normally do by itself. If possible, let Forms do the dirty job.

Also, if you use that form for querying purposes only and have to deal with non-database items (because you can't avoid that), consider creating a view (which would join all tables necessary to display data you're interested in) and then base your form data block(s) on the view. It'll be much simpler to maintain & your form will remain rather simple.

Report message to a moderator

Re: How to sort and query on non database column [message #629132 is a reply to message #629127]

Wed, 03 December 2014 11:14

megha2525
Messages: 62
Registered: June 2012
Location: columbus

Member

Hi Littlefoot,

Thank you for the reply. This is a form which was already being used and hence I cannot change it to use a view. I am on working to it to add a few features.
Yes, the master detail relationship is all set up and it works fine when I click on show records button.
So, if the detail block had like 6 records out of which 3 records have transaction_status_description as 'Approved', i would like to query on the transaction_status_description column and search for those 'Approved' records.
Can you please tell me how can I solve this?

Thanks
Megha

Report message to a moderator

Re: How to sort and query on non database column [message #629135 is a reply to message #629132]

Wed, 03 December 2014 12:12

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 #629136 is a reply to message #629135]

Wed, 03 December 2014 12:32

megha2525
Messages: 62
Registered: June 2012
Location: columbus

Member

Hi Craig,

Thank you so much for the reply.
I am working on oracle forms 10g .
I am trying to implement a manual sort on the detail block. once the records are displayed in the detail block, then the user wants to do a manual sort.
Something like the user clicks on a button '^' for ascending and 'v' for descending.
How can that be accomplished?

Thanks
Megha

Report message to a moderator

Re: How to sort and query on non database column [message #629140 is a reply to message #629136]

Wed, 03 December 2014 13:52

CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA

Senior Member

Use the code I provided in your buttons to change the ORDER BY property of your block when the user clicks the button. Your code will need to Clear the Block (CLEAR_BLOCK) and re-query using the new Order By.

Craig...

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

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.

Report message to a moderator

Re: How to sort and query on non database column [message #629145 is a reply to message #629143]

Wed, 03 December 2014 15:33

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...

Report message to a moderator

Re: How to sort and query on non database column [message #629148 is a reply to message #629145]

Wed, 03 December 2014 18:32

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 #629156 is a reply to message #629148]

Thu, 04 December 2014 00:10

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It won't compile as it contains syntax error(s); for example, a closing single quote missing in the "-- Is this right?" line.

Anyway, it seems that you didn't understand what Craig has told you. Why did you use V_ORDERBY variable and never used it? Instead of ordering by TRANS_STATUS_DESC, you should have used V_ORDERBY instead.

Moreover,

execute_query;
clear_block(no_validate);

combination is stupid - as soon as you fetch records, you clear the block and there's no way that anyone would see anything in there.

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

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 #629165 is a reply to message #629164]

Thu, 04 December 2014 01:44

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

The variable should contain DECODE statement only (remove SELECT ... FROM). As you are assigning a string to it, it must be enclosed into single quotes:

v_orderby := 'decode ( ... )';

As DECODE itself contains strings (which also have to be enclosed into single quotes), you'll need to use two single quotes:

v_orderby := 'decode (status, ''A'', ''APPROVED'', ...)';

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

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

Report message to a moderator

Re: How to sort and query on non database column [message #629176 is a reply to message #629170]

Thu, 04 December 2014 02:56

cookiemonster
Messages: 13920
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?

Report message to a moderator

Re: How to sort and query on non database column [message #629206 is a reply to message #629176]

Thu, 04 December 2014 07:44

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

Report message to a moderator

Previous Topic:	Web Forms
Next Topic:	how to call database package , after saving the Data

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 00:13:18 CDT 2024