Home » Developer & Programmer » Forms » Unable to sort on a non database column (oracle 10g)
Unable to sort on a non database column [message #629230] Thu, 04 December 2014 14:11 Go to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
Hi All,
I am working oracle forms 10g and it is master detail form . The master form is based on cards table and detail block is based on transactions table.
One of the columns in the detail block is vendor_name which is an non database column.
Vendor_name is populated by using vendor_id which is a database column in the transactions table.

 SELECT v.vendor_name
         INTO :trans.vendor_name 
         FROM  ap.ap_suppliers v
         WHERE :trans.vendor_id = v.vendor_id;


After the records are fetched into the detail block, I would like to perform a manual sort on vendor name.

I wrote the following code in the when button pressed trigger for performing a manual sort on vendor_name.

DECLARE
v_query varchar2(2000);
	
BEGIN 	
         
         v_query := 'select a.transaction_date, a.transaction_amount, b.vendor_name from pcard.transactions a, ap.ap_suppliers b
           where a.card_number = :CARDS.CARD_NUMBER and 
           a.vendor_id=b.vendor_id  and to_char(invoice_date,''MON-YY'') = :CONTROL.PERIOD_NAME order by b.vendor_name';           

set_block_property('TRANS', query_data_source_name, v_query);

EXECUTE_QUERY;

END;


But I am getting an error message 'cannot set the blocks query data source'


Please help.

Thanks
Megha

Re: Unable to sort on a non database column [message #629233 is a reply to message #629230] Thu, 04 December 2014 15:39 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
You can't reference the :CONTROL.PERIOD_NAME like this with a From Clause Query because the SQL Statement is sent to the database to be parsed and the database case see the :CONTROL.PERIOD_NAME object so this will generate an error. Rather than use a reference to the PERIOD_NAME, pass the value of the period name instead. For example:
v_query := 'select a.transaction_date, a.transaction_amount, b.vendor_name from pcard.transactions a, ap.ap_suppliers b
           where a.card_number = :CARDS.CARD_NUMBER and 
           a.vendor_id=b.vendor_id  and to_char(invoice_date,''MON-YY'') = '''||:CONTROL.PERIOD_NAME||'''order by b.vendor_name'; 


I may not have enough Quotes around the :CONTROL.PERIOD_NAME so make sure verify the syntax is correct.

Craig...
Re: Unable to sort on a non database column [message #629265 is a reply to message #629233] Fri, 05 December 2014 03:02 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've got enough quotes, but it needs quotes round :cards.card_number and there needs to be a space before the order by.
Re: Unable to sort on a non database column [message #629411 is a reply to message #629265] Mon, 08 December 2014 14:48 Go to previous messageGo to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
Hi Cookiemonster and Craig,

Thank you for the reply. I am getting the same error message

'cannot set the blocks query data source'


This is the updated code.
DECLARE
v_query varchar2(2000);
	
BEGIN 	


v_query := 'select b.vendor_name from pcard.transactions a, ap.ap_suppliers b
           where a.card_number = '''||:CARDS.CARD_NUMBER||''' and 
           a.vendor_id=b.vendor_id  and to_char(invoice_date,''MON-YY'') = '''||:CONTROL.PERIOD_NAME||''' order by b.vendor_name';  

set_block_property('TRANS', query_data_source_name, v_query);

:control.test:= get_block_property('TRANS',query_data_source_name);

CLEAR_BLOCK(no_validate);

EXECUTE_QUERY;
END;





In order to test what is being set in the blocks  query data source i used a text item :control.test . 
control.test displays card.transactions and not the query.


Please help.

Thanks
Megha


[Updated on: Mon, 08 December 2014 14:51]

Report message to a moderator

Re: Unable to sort on a non database column [message #629412 is a reply to message #629411] Mon, 08 December 2014 17:11 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
So, have you printed (through your code) the value of your "v_query" variable so you can see if you have a valid SQL statement?

Craig...
Re: Unable to sort on a non database column [message #629469 is a reply to message #629412] Tue, 09 December 2014 10:00 Go to previous messageGo to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
That's right Craig. I tried to see whats being set .

So I set it this way

:control.test:= get_block_property('TRANS',query_data_source_name);


control.test displays card.transactions and not the query.

Please help.

Thanks
Megha
Re: Unable to sort on a non database column [message #629474 is a reply to message #629469] Tue, 09 December 2014 10:40 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You try to use set_block_property and get an error.
If you get an error then it hasn't changed the block property.
So how do you expect get_block_property to show you the value you failed to change it to?

As Craig already said - you need to display the contents of v_query.
So assign the control item to v_query directly.




RAC: that should have been get_block_property in the third sentence.

[Updated on: Tue, 09 December 2014 10:42]

Report message to a moderator

Re: Unable to sort on a non database column [message #629475 is a reply to message #629474] Tue, 09 December 2014 10:48 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What's your blocks Query Data Source Type property set to
Re: Unable to sort on a non database column [message #629478 is a reply to message #629475] Tue, 09 December 2014 11:33 Go to previous messageGo to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
Hi cookiemonster,
Query Data Source Type = Table
query Data source Name = card.transactions

Thanks
Megha

[Updated on: Tue, 09 December 2014 11:34]

Report message to a moderator

Re: Unable to sort on a non database column [message #629481 is a reply to message #629478] Tue, 09 December 2014 12:11 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
megha2525 wrote on Tue, 09 December 2014 10:33
Hi cookiemonster,
Query Data Source Type = Table
query Data source Name = card.transactions

Thanks
Megha

Your block is a From Clause Query so your Query Data Source Type property should be set to "FROM clause query".

Have you ever used a From Clause Query (FCQ) data block before? If not, there are Forms Help topics on this as well as numerous tutorials on the Internet. You may want to research FCQ block a bit more before you try to create one...

Craig...

[Updated on: Tue, 09 December 2014 12:15]

Report message to a moderator

Re: Unable to sort on a non database column [message #629482 is a reply to message #629481] Tue, 09 December 2014 12:20 Go to previous messageGo to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
Hi Craig,

Thank you for the reply. I am using the from clause because I am trying to set by vendor_name which is a non database column.
The form is an existing form and I am only adding more features to it.
All the records are retrieved into the master and detail blocks and it works fine.
I am trying to add a manual sort feature to the vendor_name column in the detail block and that is not working.

Thanks
Megha
Re: Unable to sort on a non database column [message #629484 is a reply to message #629482] Tue, 09 December 2014 12:46 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
Since you are using a From Clause Query block, why didn't you just add the Vendor_Name column to the Block Data Source Query. Then you don't have to add all of the extra code to sort, since it will be a base table item. Much more simple...

Craig...
Re: Unable to sort on a non database column [message #629485 is a reply to message #629484] Tue, 09 December 2014 12:58 Go to previous messageGo to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
Hi Craig,
The block is based on a table , not From clause Query.
Megha
Re: Unable to sort on a non database column [message #629487 is a reply to message #629485] Tue, 09 December 2014 13:41 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
To reiterate what Craig already said:
If the block is based on a table then you can not set the query_data_source_name property to a select statement. You can only set it to the name of a table or view.
Re: Unable to sort on a non database column [message #629488 is a reply to message #629487] Tue, 09 December 2014 13:45 Go to previous messageGo to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
Hi Cookiemonster and Craig,
Quote:

Since you are using a From Clause Query block, why didn't you just add the Vendor_Name column to the Block Data Source Query. Then you don't have to add all of the extra code to sort, since it will be a base table item. Much more simple...


The vendor_name is a non data base column .And the form is already designed to use a transactions table as the the query data source.

Now how can a manual sort on a non database item be accomplished?

Megha
Re: Unable to sort on a non database column [message #629511 is a reply to message #629488] Wed, 10 December 2014 02:51 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
We gave you one way in your other thread on the subject.
The alternative is make the item a database item - base the block on a view or from query clause that includes the column.
Re: Unable to sort on a non database column [message #629538 is a reply to message #629511] Wed, 10 December 2014 09:06 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
megha2525 wrote on Tue, 09 December 2014 11:58
Hi Craig,
The block is based on a table , not From clause Query.
Megha

Really...the only time I know of to use the block Query_Data_Source_Name property is with a From Clause Query. Your code...

megha2525 wrote on Thur, 04 December 2014 13:11
DECLARE
   v_query varchar2(2000);
BEGIN 	
   v_query := 'select a.transaction_date, a.transaction_amount, b.vendor_name' 
               ||' from pcard.transactions a, ap.ap_suppliers b '
               ||' where a.card_number = :CARDS.CARD_NUMBER '
               ||' and a.vendor_id=b.vendor_id '
               ||' and to_char(invoice_date,''MON-YY'') = :CONTROL.PERIOD_NAME 
               ||' order by b.vendor_name';           
   set_block_property('TRANS', query_data_source_name, v_query);
   EXECUTE_QUERY;
END;

The reason you are getting the error is because you are passing the reference to your Block.Items instead of passing the value of your block.items. With a From Clause Query based block, the SQL is parsed by the database so you have to pass the actual values of :CARDS.CARD_NUMBER and :CONTROL.PERIOD_NAME because these objects don't exist in the database. Change your query too:
DECLARE
   v_query varchar2(2000);
BEGIN 	
   v_query := 'select a.transaction_date, a.transaction_amount, b.vendor_name' 
               ||' from pcard.transactions a, ap.ap_suppliers b '
               ||' where a.card_number = ' ''|| :CARDS.CARD_NUMBER ||' '''
               ||' and a.vendor_id=b.vendor_id '
               ||' and to_char(invoice_date,''MON-YY'') = ' '' ||:CONTROL.PERIOD_NAME|| ' '''
               ||' order by b.vendor_name';           
   set_block_property('TRANS', query_data_source_name, v_query);
   EXECUTE_QUERY;
END;

In addition to this, each column listed in the SQL Statement must have a corresponding ITEM defined in the Data Block and there MUST be a listing for the column in the Query Data Source Columns block property.

You seem intent on not learning how to use a From Clause Query based block. They are not difficult, but there are rules you have to follow. I strongly recommend you review a few of the tutorials that are available on the Internet. Here are a couple of the better ones:
https://sites.google.com/site/craigsoraclestuff/oracle-forms---how-to-s/forms-how-to---base-a-block-on-a-from-clause-query
http://sheikyerbouti.developpez.com/from_clause/from_clause.htm

There's an old saying, "You can lead a horse to water, but you can't make him drink." We've led you to the water...now it is up to you to drink.

Craig...
Re: Unable to sort on a non database column [message #629542 is a reply to message #629538] Wed, 10 December 2014 09:26 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm pretty sure he's getting the error because Query Data Source Type is set to Table.
Re: Unable to sort on a non database column [message #629543 is a reply to message #629542] Wed, 10 December 2014 09:41 Go to previous messageGo to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
Hi Craig and Cookiemonster,

Thank you for the reply. The only reason I could not change the detail block to use a from clause is because there are a lot of calculated columnns and summary columns in this already existing form and a lot of program units are dependent on the base table. I have used the from clause in other forms and it works wonderfully.
Also, I could not use the View as this form is not a read only form .
Thanks again for all the time and patience that you had in helping me .

Megha
Re: Unable to sort on a non database column [message #629544 is a reply to message #629542] Wed, 10 December 2014 09:46 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
cookiemonster wrote on Wed, 10 December 2014 08:26
I'm pretty sure he's getting the error because Query Data Source Type is set to Table.

Agreed - I did not notice the post where this was listed. Embarassed

With the Query Data Source Type set to Table, you can't dynamically change the Query Data Source Name property to an SQL Statement.

I think you need to stop, rollback all your changes to the Form and start over...

Craig...

[Updated on: Wed, 10 December 2014 10:00]

Report message to a moderator

Re: Unable to sort on a non database column [message #629545 is a reply to message #629544] Wed, 10 December 2014 10:03 Go to previous messageGo to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
Quote:

I think you need to stop, rollback all your changes to the Form and start over...

Craig...


If I base the detail block on from clause, Can I still use the form to insert data to the database ?

Megha
Re: Unable to sort on a non database column [message #629546 is a reply to message #629545] Wed, 10 December 2014 10:15 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
Yes, but you have to write all of the DML statements yourself in the On-Insert, On-Update and On-Delete triggers.

Craig...
Re: Unable to sort on a non database column [message #629568 is a reply to message #629546] Thu, 11 December 2014 01:31 Go to previous messageGo to next message
deepakmannazhi
Messages: 137
Registered: February 2010
Location: Dubai, UAE
Senior Member

Hi Megha,

Hope this will help you

https://community.oracle.com/thread/73101?start=0&tstart=0
Re: Unable to sort on a non database column [message #629631 is a reply to message #629568] Fri, 12 December 2014 11:12 Go to previous messageGo to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
Hi Deepak,

That worked.
I created a database function to fetch the vendor name and
used the set_block_property to do the manual sort.
Here's the code.

IF  GET_ITEM_PROPERTY('TRANS.VNAME_SORT',LABEL)='v' THEN 
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,'card.get_vendorname(vendor_id)');
SET_ITEM_PROPERTY('TRANS.VNAME_SORT',LABEL,'^');
ELSIF GET_ITEM_PROPERTY('VNAME_SORT',LABEL) = '^' THEN
SET_BLOCK_PROPERTY('TRANS',ORDER_BY,'card.get_vendorname(vendor_id) DESC');
SET_ITEM_PROPERTY('TRANS.VNAME_SORT',LABEL,'v');
END IF;
EXECUTE_QUERY;


Thanks
Megha
Re: Unable to sort on a non database column [message #629645 is a reply to message #629631] Sat, 13 December 2014 11:54 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What a nice trick! Cool.
Re: Unable to sort on a non database column [message #629690 is a reply to message #629645] Mon, 15 December 2014 03:10 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Just bare in mind that if you have a lot of data using a function to order it can be really slow.
Previous Topic: Developer crashed
Next Topic: ** Refresh another running form from another form, how to?
Goto Forum:
  


Current Time: Fri Mar 29 04:44:28 CDT 2024