Re: ORACLE FORMS QUERY QUESTION

There is an RTSS note about how to do this. And in the SQL*Forms Advanced SQL*Forms book there is an example for doing foreign key queries in non-base table fields. The TECHNIQUE is more important than the actual solution, because the solution goes about it a very roundabout way.

The example given takes a foreign key and creates a LIST of values which are copy()ed into with the "#" override. Well, it would be better to make it copy() in the subquery which generated the list. This way, there's no frustrating problem with working around PL/SQL string length limitations.

Basically, you
  COPY('# in (select key from foriegn_table where desc=:query)','block.field');

-Andy

smuench_at_oracle.com (Steven P. Muench) writes:

>EUGENY -- In a PRE-QUERY trigger, use the COPY() built-in to assign a
> given string (whatever it may contain) to a field in the
> block about to be queryied.
 

> If the field in question is called QUAL_CODE in the
> LINE_ITEM block, then a block-level trigger on the LINE_ITEM
> block would look like:
 

> --
> -- Pre-Query on LINE_ITEM
> --
> BEGIN
> COPY( '# (value1, ...)', 'line_item.qual_code' );
> END;
 
> Of course, the first argument could be any string which you
> may have "glued" together programmatically. In fact, the
> second argument (specifying the *name* of the destination
> field) could also be supplied as a CHAR variable which might
> have be derived as well.
>
>_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> Steve Muench Email: smuench_at_oracle.com
> Forms Development
> Product Manager

-- 
Andrew Finkenstadt, Vista-Chrome, Inc., Homes & Land Publishing Corporation
GEnie Unix RoundTable Manager, andy_at_vistachrome.com, andy_at_genie.geis.com.
"[The author] neither accidentally nor intentionally omits or includes 
anything that could support a preconceived thesis." - C&EN 21-DEC-92 p.72