Re: Query multiple tables in Forms 4.0 block?

In article <3s7l82$t4a_at_zorn.mnet.medstroms.se>, p.salmu_at_mn.medstroms.se says...
>
>gbrown_at_partech.com (George Brown) wrote:
>>
>> Can you query, and in turn display multiple tables in a Forms 4.0 block?
>> I have an ID that I want to "map" to a name which is contained in another
>> table. I get an Oracle can't execute query error. Any ideas?
>> --
>A block can have only one "base table", but it can be a view that
>combines whatever you like. That could of course limit the use of
>the form to query only. A simpler solution is to define the name
>item as non base table and add a post-query trigger that retrieves
>it.

Basing the block on a view of the related tables will give you the most flexibility in query specification. You can perform updates to to the first table by using explicit locking and DML triggers. The DML triggers do the updates to the first table (otherwise, Forms will try to update a multi-table view - trouble there). Look into the ON-LOCK and ON-INSERT / -UPDATE / -DELETE triggers.

If you go with making the NAME field a non-database field, you can still use it in query specification, although in a somewhat limited manner. Use a PRE-QUERY trigger to stick a nested query into an unused (possibly non-displayed) database field.

-- 
Richard Hull
rwhull_at_ramlink.net