Home » Developer & Programmer » Forms » Framing a search query at run time, considering columns from diff tables.
Framing a search query at run time, considering columns from diff tables. [message #387416] Thu, 19 February 2009 03:14 Go to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Hi Friends

I have a doubt, and i have no idea how to go ahead and solve this. Please help me out in this.

I have written a function, which frames the query at the run time, so that when i press the search button, i would get the results in my display block.

Now the problem is in my Search Block, i have around 20 columns, and these 20 colums come from 4 diff tables, i should frame a query for this, how can i do that.

I have pasted my function here, which searches based on only table presently.

/* Formatted on 2009/02/18 15:41 (Formatter Plus v4.8.8) */
FUNCTION get_query
   RETURN VARCHAR2
IS
   v_qry     VARCHAR2 (6000) DEFAULT NULL;
   v_where   CHAR (1)        DEFAULT 'N';
   v_err     VARCHAR2 (2000);
   l_seq     VARCHAR2 (3);
BEGIN
   IF NVL (LTRIM (RTRIM (:search_blk.system_name)), 'N') <> 'N'
   THEN
      v_qry :=
            v_qry
         || ' UPPER(SYSTEM_NAME) LIKE UPPER('''
         || :search_blk.system_name
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF NVL (LTRIM (RTRIM (:search_blk.product_id)), 'N') <> 'N'
   THEN
      v_qry :=
            v_qry
         || ' UPPER(PRODUCT_ID) LIKE UPPER('''
         || :search_blk.product_id
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF NVL (LTRIM (RTRIM (:search_blk.product_type)), 'N') <> 'N'
   THEN
      v_qry :=
            v_qry
         || ' UPPER(PRODUCT_TYPE) LIKE UPPER('''
         || :search_blk.product_type
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF LTRIM (RTRIM (:search_blk.review_action_type)) IS NOT NULL
   THEN
      v_qry :=
            v_qry
         || ' REVIEW_ACTION_TYPE='
         || :search_blk.review_action_type
         || ' AND ';
      v_where := 'Y';
   END IF;

   IF NVL (LTRIM (RTRIM (:search_blk.review_comment)), 'XXxx123') <> 'XXxx123'
   THEN
      v_qry :=
            v_qry
         || ' UPPER(REVIEW_COMMENT) LIKE UPPER('''
         || :search_blk.review_comment
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF LTRIM (RTRIM (:search_blk.review_date)) IS NOT NULL
   THEN
      v_qry :=
            v_qry
         || 'TRUNC(REVIEW_DATE)'
         || NVL (:search_blk.lst_review_date, '=')
         || ''''
         || :search_blk.review_date
         || ''' AND ';
      v_where := 'Y';
   END IF;

   IF NVL (LTRIM (RTRIM (:search_blk.reviewed_by)), 'N') <> 'N'
   THEN
      v_qry :=
            v_qry
         || ' UPPER(REVIEWED_BY) LIKE UPPER('''
         || :search_blk.reviewed_by
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF NVL (LTRIM (RTRIM (:search_blk.assigned_to)), 'N') <> 'N'
   THEN
      v_qry :=
            v_qry
         || ' UPPER(ASSIGNED_TO) LIKE UPPER('''
         || :search_blk.assigned_to
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF NVL (LTRIM (RTRIM (:search_blk.account_id)), 'N') <> 'N'
   THEN
      v_qry :=
            v_qry
         || ' UPPER(ACCOUNT_ID) LIKE UPPER('''
         || :search_blk.account_id
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF NVL (LTRIM (RTRIM (:search_blk.agreement_line_id)), 'N') <> 'N'
   THEN
      v_qry :=
            v_qry
         || ' UPPER(AGREEMENT_LINE_ID) LIKE UPPER('''
         || :search_blk.agreement_line_id
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF LTRIM (RTRIM (:search_blk.issue_type)) IS NOT NULL
   THEN
      v_qry :=
            v_qry
         || ' UPPER(ISSUE_TYPE) LIKE UPPER('''
         || :search_blk.issue_type
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF LTRIM (RTRIM (:search_blk.last_found_date)) IS NOT NULL
   THEN
      v_qry :=
            v_qry
         || 'TRUNC(LAST_FOUND_DATE)'
         || NVL (:search_blk.lst_last_found_date, '=')
         || ''''
         || :search_blk.last_found_date
         || ''' AND ';
      v_where := 'Y';
   END IF;

   IF LTRIM (RTRIM (:search_blk.review_status)) IS NOT NULL
   THEN
      v_qry :=
            v_qry
         || ' UPPER(REVIEW_STATUS) LIKE UPPER('''
         || :search_blk.review_status
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF LTRIM (RTRIM (:search_blk.created_date)) IS NOT NULL
   THEN
      v_qry :=
            v_qry
         || 'TRUNC(CREATED_DATE)'
         || NVL (:search_blk.lst_created_date, '=')
         || ''''
         || :search_blk.created_date
         || ''' AND ';
      v_where := 'Y';
   END IF;

   IF LTRIM (RTRIM (:search_blk.last_update_date)) IS NOT NULL
   THEN
      v_qry :=
            v_qry
         || 'TRUNC(LAST_UPDATE_DATE)'
         || NVL (:search_blk.lst_last_updated_date, '=')
         || ''''
         || :search_blk.last_update_date
         || ''' AND ';
      v_where := 'Y';
   END IF;

   RETURN SUBSTR (v_qry, 1, LENGTH (v_qry) - 4);
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN SQLERRM || ': AT-LINE: ' || l_seq;
END get_query;


In the ablove function i have considered only columns from a table called entitlement. for these columns the data base datablock property is set to "yes".

The other columns which i have to consider is

product_name from product table, Product_ID is a commn column in the product and entitlement tables.
account_name from account table, Account id is a common column in the account and entitlement tables.

And for these columns the data base data block is set to "NO" as i have displayed it as just a text item.


Plese help me how to do this.

Re: Framing a search query at run time, considering columns from diff tables. [message #387440 is a reply to message #387416] Thu, 19 February 2009 04:24 Go to previous messageGo to next message
cookiemonster
Messages: 12406
Registered: September 2008
Location: Rainy Manchester
Senior Member
CAn you make a view that joins all the tables together then base the block on that?
Re: Framing a search query at run time, considering columns from diff tables. [message #387441 is a reply to message #387416] Thu, 19 February 2009 04:25 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SUHAS22845 wrote on Thu, 19 February 2009 10:14
i have around 20 columns, and these 20 colums come from 4 diff tables, i should frame a query for this, how can i do that.

Could you, perhaps, create a view which would join these 4 tables and write a query against the view?
Previous Topic: can't used the order by clause in Cursor?
Next Topic: FRM-40508
Goto Forum:
  


Current Time: Tue Dec 06 06:16:17 CST 2016

Total time taken to generate the page: 0.27898 seconds