Re: Forms 5.0 Non base table query HELP!!

From: Valeri Sorokine <vsorokin_at_dd.ru>
Date: Wed, 12 May 1999 14:14:55 +0400
Message-ID: <3739549F.B8AEC047_at_dd.ru>

You can use PRE-QUERY trigger like this:

/* Add restrictive query on FK items if value(s) entered in non-table

   items and/or if there are items which may contain nulls as valid    values. */
BEGIN
  DECLARE
    block_id Block := find_block('CUSTOMER');     sub_where VARCHAR2(512);
    def_where VARCHAR2(2000);
    FUNCTION add_and(P_WHERE IN VARCHAR2) RETURN VARCHAR2 IS     BEGIN

      IF (nvl( length(P_WHERE), 0) != 0) THEN
        RETURN( P_WHERE || ' AND ');
      ELSE
        RETURN( P_WHERE );
      END IF;

    END;
  BEGIN
    def_where := ...
    ...
    sub_where := NULL;
    IF (:CUSTOMER.DSP_ZONE IS NOT NULL) THEN

      sub_where := add_and( sub_where ) || '(UPPER(ZONE) LIKE '''||
                   UPPER(:CUSTOMER.DSP_ZONE) || ''')';

    END IF;
    IF (sub_where IS NOT NULL) THEN

      def_where := add_and( def_where ) || '((HOUSING_SEQ_NUMBER) IN '||
                   '(SELECT HOUSING_SEQ_NUMBER '||
                   'FROM HOUSING_LOCATION WHERE '|| sub_where || '))';

    END IF;
    ...
    set_block_property(block_id, DEFAULT_WHERE, def_where);   END;
END; where DSP_ZONE is the Field being shown by means of post query trigger.

Hope this helps.

Let me know if you still have the problem...

hutch699_at_my-dejanews.com wrote:
>
> I have this item that I am populating by means of a post query trigger. The
> user would like to be able to query on this item. It belongs to a table that
> is related to the base table. How would I go about letting the user query on
> this field?
>
> The base table is customer and it is related to the housing location table by
> means of a fk of housing_Seq_number. The Field being shown by means of post
> query trigger is zone from housing location.It is a tabular layout, so it
> lists several customers.Any ideas or help would be appreciated. If you could
> send answers to Hutch699_at_hotmail.com, I would appreciate it. If you cant then
> post them here and I will track them down. Thanks to all.
>
> Customer Housing Location
> housing_seq_number housing_Seq_number
> order_number zone
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

-- 
Valeri Sorokine
ProSoft, Russia, Moscow, Information Systems Division
Phone: +7 (095) 234 0636 (6 lines) FAX: +7 (095) 234 0640
E-mail: vsorokin_at_dd.ru OR vsorokin_at_prosoft.ru
http://www.dd.ru

Received on Wed May 12 1999 - 12:14:55 CEST