| How to Filter a DB Data Block based on a column not in the Block [message #249351] |
Wed, 04 July 2007 03:03  |
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  |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
