Re: Forms query on a non base table item

From: Valeri Sorokine <vsorokin_at_dd.ru>
Date: Thu, 22 Apr 1999 11:56:59 +0400
Message-ID: <371ED64B.8E01403E_at_dd.ru>

There is one more example: 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. */
/* */
/* Code to restrict the query is added to the default where */
/* clause of the block. */
/* */
/* (c) Designer/2000 1.3.x :) */
/* */
BEGIN
  DECLARE
    block_id Block := find_block('YOUR_BLOCK');     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
    sub_where := NULL;
    IF (:YOUR_BLOCK.CUSTOMER_NAME IS NOT NULL) THEN

      sub_where := add_and( sub_where ) 
                   || '(UPPER(CUSTOMER_NAME) LIKE '''
                   || UPPER(:YOUR_BLOCK.CUSTOMER_NAME) || ''')';

    END IF;
    IF (sub_where IS NOT NULL) THEN

      def_where := add_and( def_where )
                   || '(CUSTOMER_CODE IN '
                   ||'(SELECT CUSTOMER_CODE '
                   ||'FROM CUSTOMER WHERE '|| sub_where || '))';

    END IF;
    set_block_property(block_id, DEFAULT_WHERE, def_where);   END;
END; [Quoted] Hope this helps.

Good Luck.

Loyal Barber wrote:

> 
[Quoted] [Quoted] > I have read several responses to your post and it appears that either I
> or everyone else missed the point.  I believe the question was, how do
> you allow QUERY on a non-base table item.  I have to make some
> assumptions
> here to make it work:
> 1. I assume you are in some way allowing them into something like
> enter-query mode.
> 2. I assume you are using the base functionality of key-execute-query or
> similar
> 
> To accomplish this
> 1. check for not null condition in your non-base table item: customer
> name
> 2. If it is not null, dynamically set the where clause for the base
> table block based on
> a subquery from the customer table:
> 
> WHERE customer_code IN (
>     SELECT customer_code
>     FROM customer
>     WHERE  UPPER(customer_code) LIKE
[Quoted] > '%'||UPPER(LTRIM(RTRIM(:b_order.customer_name)))||'%')
> 
> 3. execute the query.
> 
> Loyal Barber
> BLT Soft Incorporated
> 
> Bob Hirons wrote:
> 
> > How do I code a form to allow query on a non base table item?
> >
> > e.g.
> > base table ORDER has-
> > ORD_NO
> > ORD_DESC
> > CUSTOMER_CODE
> >
> > table CUSTOMER has-
> > CUSTOMER_CODE
> > CUSTOMER_NAME
> >
> > form has items
> > ORD_NO                        base table item
> > ORD_DESC                    base table item
> > CUSTOMER_CODE       base table item
> > CUSTOMER_NAME       non base table item
> >
> > Wish to allow query on CUSTOMER_NAME.
> 
> --
> The difference between Windows 95 and 98?
> 3!

-- 
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 Thu Apr 22 1999 - 09:56:59 CEST