Unable to sort on a non database column [message #629230] |
Thu, 04 December 2014 14:11 |
|
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 #629411 is a reply to message #629265] |
Mon, 08 December 2014 14:48 |
|
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 #629474 is a reply to message #629469] |
Tue, 09 December 2014 10:40 |
cookiemonster
Messages: 13943 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 #629481 is a reply to message #629478] |
Tue, 09 December 2014 12:11 |
|
CraigB
Messages: 386 Registered: August 2014 Location: Utah, USA
|
Senior Member |
|
|
megha2525 wrote on Tue, 09 December 2014 10:33Hi 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 #629538 is a reply to message #629511] |
Wed, 10 December 2014 09:06 |
|
CraigB
Messages: 386 Registered: August 2014 Location: Utah, USA
|
Senior Member |
|
|
megha2525 wrote on Tue, 09 December 2014 11:58Hi 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 #629544 is a reply to message #629542] |
Wed, 10 December 2014 09:46 |
|
CraigB
Messages: 386 Registered: August 2014 Location: Utah, USA
|
Senior Member |
|
|
cookiemonster wrote on Wed, 10 December 2014 08:26I'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.
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 #629631 is a reply to message #629568] |
Fri, 12 December 2014 11:12 |
|
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
|
|
|
|
|