Home » Developer & Programmer » Forms » How to Filter a DB Data Block based on a column not in the Block
How to Filter a DB Data Block based on a column not in the Block [message #249351] Wed, 04 July 2007 03:03 Go to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Guys,

I have a Database Data block that is populated by WHEN-NEW-FORM-INSTANCE trigger

set_block_property('myBlock_name', onetime_where, 'where MVT_ST='||stat_value);
go_block('myBlock_name');
execute_query;


After populating my Block, I have a CONTROL LIST ITEM that I need to use to Filter my Records AREA-WISE (Area 1, Area 2...).

The issue is that AREA column is not part of the DB table on which my DB Block is based. Area is coming from another Table that I need to Join this Table to the Block Table in order to FILTER the Block.

How can I do this? Can I Populate my Block with a Record Set coming from a JOIN of More than one Table (since my Block is based on Only One of the tables)?

Suggestions are Appreciated.

Thanks,
Baz



Re: How to Filter a DB Data Block based on a column not in the Block [message #249401 is a reply to message #249351] Wed, 04 July 2007 06:16 Go to previous messageGo to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Update,

I managed to FILTER by AREA without using a JOIN query, instead I am using a SUBQUERY in the WHERE CLAUSE of the Set_block_property.

area_selected := :parameter.AREA_PARAM;

set_block_property('AREA3_MVTS', onetime_where, 'where MVT_ST='||stat_value||' 
and (
MVT_ORIG_CD IN (SELECT TANK_CD from TANK where TANK_AREA_CD='||''''||area_selected||''''||')
OR
MVT_DEST_CD IN (SELECT TANK_CD from TANK where TANK_AREA_CD='||''''||area_selected||''''||')
)'							
);



[Updated on: Wed, 04 July 2007 08:03]

Report message to a moderator

Re: How to Filter a DB Data Block based on a column not in the Block [message #252740 is a reply to message #249401] Thu, 19 July 2007 18:52 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Are you happy with this solution?

Have you considered using a view? Look at the characteristics in 'user_updatable_columns' to verify that you can still make changes to your 'base' table after you have created your view.

David
Previous Topic: HOST command works in 2 workstations and fails in other 2
Next Topic: Deploy Forms 9i applications on Websphere Application Server Ver 6.1.1
Goto Forum:
  


Current Time: Tue Dec 06 14:28:32 CST 2016

Total time taken to generate the page: 0.08824 seconds